Exists in SQL is one of the main operators in SQL that helps you in specifying a subquery to test whether a certain exists in the database. It uses the below given syntax to execute the query.

Syntax:

SELECT [column_name... | expression1 ]

FROM [table_name]

WHERE EXISTS (subquery)

The operator returns the value as TRUE if the subquery contains any rows, otherwise FALSE. The exists operator can be used with either of these statements- SELECT, UPDATE, INSERT or DELETE.

The parameters used in the syntax are explained below.

PARAMETER

DESCRIPTION

column_name

Name of the column

expression1

This could be any expression made up of  a single variable, constant or even a column name 

table_name

Name of the column that we are working on

WHERE EXISTS

It tests the subquery for the existence of one or more rows. If there is any row present, it returns the boolean value as TRUE, and otherwise FALSE

So far we learnt about the syntax and the parameters of the exists operator in SQL. Now, let us look at some examples of how it is used with different statements to execute the subqueries.

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

How to Use EXISTS Condition With the SELECT Statement

The SELECT statement in SQL is used to retrieve data from the database. We can either retrieve all the columns of the database or only the columns that we require according to our need. The data returned from the SELECT statement is stored in a table also called as result-set.

Now, before moving onto the code from EXISTS condition, let’s focus on the tables that we would be working on in this tutorial.

The first table is the STUDENT table containing STUDENT_NAME, STUDENT_ID and STUDENT_LOCATION as its columns.

The second table is the DEPARTMENT table that consists of fields- DEPT_NAME, DEPT_ID, LOCATION_ID and STUDENT_ID.

TABLE: STUDENT

ExistsinSQL_1

TABLE : DEPARTMENT

ExistsinSQL_2.

Below is the code for using EXISTS condition using SELECT statement.

In this example, we are extracting all the rows from the STUDENT table, by comparing the STUDENT_ID in the DEPARTMENT and STUDENT table.

The query will give us those rows from the STUDENT table for which the STDUENT_ID matches with the STUDENT_ID in the DEPARTMENT table.

ExistsinSQL_3

Output:

ExistsinSQL_4

How to Use EXISTS Condition With the UPDATE Statement

An UPDATE statement is used to modify or change the existing data in the database.

In this example, we’ll update the STUDENT_LOCATION where STUDENT_ID = 100. And, then to see the altered table, we will select all the rows to get the output.

ExistsinSQL_5

Output

ExistsinSQL_6

Now, as you can see that for STUDENT_ID =100, the STUDENT_LOCATION has been changed from Panipat to Delhi.

How to Use EXISTS Condition With the DELETE Statement

DELETE statement is used to delete any existing record from the database. Next up, we will see how to delete a row using the EXISTS condition.

As you can see in the code below, we have written a query to delete the row which has its STUDENT_NAME set as ‘Kashish’.

ExistsinSQL_7

Output:

As it is clearly seen that the row containing the field ‘Kashish’ has now been deleted from the STUDENT table.

ExistsinSQL_8

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

How to Use NOT With the EXISTS Condition

The NOT command is used to retrieve those records WHERE the condition given by the user is NOT TRUE or is FALSE.

In this given example, we retrieve the STUDENT_NAME from the STUDENT table, for which the given condition i.e department.student_id=student.student_id is FALSE.

ExistsinSQL_9

Output:

If you see both the tables clearly, you will get to know that for both the names given below, STUDENT_ID is not mentioned in the DEPARTMENT table, and hence it is the output. 

If we had not used the NOT command here, it would give us the output as Neena and Johny.

Note: although the STUDENT_ID for STUDENT_NAME Kashish is present in the DEPARTMENT table, it would not be shown in the output here, as we already deleted that row in the last example.

ExistsinSQL_10

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Conclusion

Now that we have reached the end of this tutorial, you must be thorough with the EXISTS condition in SQL and now is the time to learn more concepts and become proficient in SQL. Wondering how? Checkout Simplilearn’s SQL certification training to master SQL, and take the next leap in your programming career.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.