SQL Exists Operator

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
(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
(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
(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.

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.