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.


Search Keys

  • sql exists example
  • sql exists vs in
  • sql exists and not exists
  • sql exists oracle
  • sql exists vs any
  • sql exists multiple columns
  • sql exists join
  • 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 mysql
  • sql exists multiple conditions
  • sql exists multiple subquery
  • sql exists more than one
  • sql exists multiple
  • sql exists mssql
  • 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 Programming With MySQL 178.69 2013 Programming Chandra
3 Understanding the New SQL 180 2006 Security Siva Kumar
4 Jump Start MySQL 200 2011 Optimization Ranjani Mai
5 Practical SQL 135 2014 Database Harish Karthik
6 MySql Concurrency 60 2007 Database Rishi Keshan

Sample Database Table - BookOrder

BookID BookName OrderDate Qty DeliveryDate
1 SQL Visual Quickstart 22-03-2009 19 26-03-2009
5 Programming With MySQL 14-09-2009 6 17-09-2009
5 Understanding the New SQL 16-05-2004 9 21-06-2004
5 Jump Start MySQL 25-06-2010 19 28-07-2010
4 Practical SQL 26-05-2011 8 28-05-2011
4 MySql Concurrency 11-04-2011 8 18-05-2011

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
4 Jump Start MySQL
5 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
5 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.

Search Keys

  • sql exists with join
  • sql exists with or
  • sql exists where
  • 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
  • sql exists in select
  • sql exists in select statement
  • sql exists in case
  • sql exists in another table
  • sql exists if
  • sql exists instead of in