TRENDING TOPICS

×

SQL EXISTS Operator

SQL EXISTS operator checks the existence of result of a subquery or more record.

EXISTS Operator Conditions

  • SQL EXISTS query result is a BOOLEAN value 'TRUE or FALSE'.
  • Use EXISTS operator with SELECT, UPDATE, INSERT, and DELETE.
  • SQL NOT EXISTS operator return the result just opposite to EXISTS Operator.

EXISTS Operator Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS (
  SELECT column_name
  FROM table_name
  WHERE [condition]);

NOT EXISTS Operator Syntax

SELECT column_name(s)
FROM table_name
WHERE NOT EXISTS (
  SELECT column_name
  FROM table_name
  WHERE [condition]);

EXAMPLE: EXISTS Operator

Customers Table

Simple Customers record table & the name of the table is CUSTOMERS. The Column(s) name of the table like [ID, NAME, EMAIL, ADDRESS, and SALARY]:

ID NAME EMAIL ADDRESS SALARY
1 Mark JK mark@gmail.com Mumbai 5000
2 Nitika nitika@gmail.com Delhi 6700
3 Suneel Kumar kumars@gmail.com Pune 9400
4 Jenifer jenifer@gmail.com Indore 3400
5 McKinley mckinley@gmail.com UK 7200
6 Deepika deepika@gmail.com Nashik 3600

Orders Table

This is a sample ORDER table; The columns of the table are [OID, DATE, CUSTOMER_ID, and AMOUNT]:

OID DATE CUSTOMER_ID AMOUNT
210 12/01/2019 2 4000
209 11/22/2019 4 2000
211 12/10/2019 1 1000
212 12/17/2019 6 3000

Example of EXISTS condition with SELECT

SELECT NAME, EMAIL
FROM CUSTOMERS
WHERE EXISTS (
  SELECT *
  FROM ORDERS
  WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID);

Result Lokk Like

NAME EMAIL
Mark JK mark@gmail.com
Nitika nitika@gmail.com
Jenifer jenifer@gmail.com
Deepika deepika@gmail.com

Example of NOT EXISTS condition with SELECT

SELECT NAME, EMAIL
FROM CUSTOMERS
WHERE NOT EXISTS (
  SELECT *
  FROM ORDERS
  WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID);

Result Lokk Like

NAME EMAIL
Suneel Kumar kumars@gmail.com
McKinley mckinley@gmail.com

SQL SUBQUERY

A SQL SUBQUERY is also called NESTED Query; The SUBQUERY is frequently used within other queries.

  • A SQL SUBQUERY can be used with multiple statements like SELECT, INSERT, UPDATE, DELETE Statements.
  • We can use WHERE condition, HAVING Clause, GROUB BY Clause BUT we can't use ORDER BY Clause within a SUBQUERY.

© 2021 GDATAMART.COM (All Rights Reserved)