SQL Exists Operator
The SQL EXISTS operator is used to test for the existence of any rows or records in a subquery. It can be used with any valid sql statement.
It is used to check whether the result of a sub query or inner query is empty (contains no tuples or records) or not. It returns true if the subquery returns at least one row.
The subquery will be any valid sql SELECT statement.
Note: It is not recommended to use on large result set due to inefficient since the sub-query is return for every row or record in the outer query table.
Related Links
Sql Exists Syntax
The below syntax is used to create exists operator with sub query or inner query.
SELECT column_name1, column_name2, ..., column_nameN
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
- The subquery must be enclosed in (brackets).
- You must create alias name for table to access column names (in the where condition) of inner table or subquery table. Bydefault the column will take as outer table column.
Sample Database Table - Books
| BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
|---|---|---|---|---|---|
| 1 | SQL Visual Quickstart | 85 | 2011 | Optimization | Harish Karthik |
| 2 | Jump Start MySQL | 200 | 2011 | Optimization | Ranjani Mai |
| 3 | Practical SQL | 135 | 2014 | Database | Harish Karthik |
Sample Database Table - BookOrder
| BookID | BookName | OrderDate | Qty | DeliveryDate |
|---|---|---|---|---|
| 1 | SQL Visual Quickstart | 22-03-2009 | 19 | 26-03-2009 |
| 2 | Practical SQL | 26-05-2011 | 8 | 28-05-2011 |
| 3 | Jump Start MySQL | 25-06-2010 | 19 | 28-07-2010 |
SQL Exists Operator Example
The following SQL statement will display records from "Books" table if there are atleast one or more rows in the "BookOrder" table.
It will display only matching "BookId" from both tables.
SELECT BookID, BookName from Books b1
WHERE EXISTS
(SELECT BookID FROM BookOrder b2 WHERE b1.BookID = b2.BookID)
The result of above query is:
| BookID | BookName |
|---|---|
| 1 | SQL Visual Quickstart |
| 2 | Jump Start MySQL |
| 3 | Practical SQL |
If you want to apply more conditon to that query from both tables, see below query.
SELECT BookID, BookName, RelYear, DomainName from Books b1
WHERE EXISTS
(SELECT BookID FROM BookOrder b2 WHERE b1.BookID = b2.BookID AND b2.Qty > 10 AND RelYear > 2012)
The result of above query is:
| BookID | BookName | RelYear | DomainName |
|---|---|---|---|
| 3 | Practical SQL | 2014 | Database |
- You cannot include columns from subquery table to final output result set.
- You should refer table alias name for subquery table columns while accessing them in the where condion.
Related Links