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.

You can also search these topics using sql exists example, sql exists and not exists, sql exists oracle, sql exists vs any, sql exists multiple columns, sql exists in select clause, sql exists with multiple conditions, sql exists in where clause, sql exists multiple rows, sql exists meaning, sql exists multiple tables, sql exists multiple conditions, sql exists multiple subquery, sql exists more than one, sql exists multiple, sql exists with example, sql exists where clause, sql exists with count.

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);

Note:-

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

Note:-

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.

You can also search these topics using sql exists with join, sql exists with or, sql exists with multiple columns, sql exists with case, sql exists without subquery, sql exists vs in performance, sql exists vs join performance, sql exists vs join, sql exists vs count, sql exists vs inner join, sql exists vs not exists, sql exists vs having, sql exists vs in example, sql exists in sql server, sql exists in select, sql exists in select statement, sql exists in case, sql exists in another table, sql exists instead of in.