For an instructor lead, in-depth look at learning SQL click below.
The SQL EXISTS operator is a boolean operator that is used in a WHERE or HAVING clause to test whether a subquery returns any record. In simpler words, it helps us to check if our subquery has any output or not. The EXISTS operator will return true (or matches) if the subquery returns at least one record/result.
Basic Syntax of EXISTS
The basic syntax of the EXISTS operator in SQL is:
|
1 2 3 4 5 |
SELECT column1, column2, columnN FROM table_name WHERE EXISTS (subquery) |
In the above SQL command, “subquery” can be a select statement or any command that returns data from the table. If the subquery returns at least one record from the table, the EXISTS condition will be TRUE and the SELECT Statement will return the record.
SQL EXISTS Operator Examples
Let’s take an example where we have a CUSTOMERS table having records as follows:
|
1 2 3 |
SELECT * FROM CUSTOMERS; |
And we get output as:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ |
Now, let’s use SQL EXISTS Operator. We want to find the customers who have salary higher than 5000:
|
1 2 3 4 |
SELECT NAME FROM CUSTOMERS WHERE EXISTS (SELECT 1 FROM CUSTOMERS WHERE SALARY > 5000); |
This will give us our desired result as follows:
|
1 2 3 4 5 6 7 8 |
+----------+ | NAME | +----------+ | Chaitali | | Hardik | +----------+ |
In conclusion, the EXISTS operator is powerful yet often overlooked tool in SQL. It can greatly simplify your queries if used correctly. So, next time when you want to check if a SQL query returns any result, remember to use the EXISTS operator.