SQL EXISTS Operator

Last update on April 07 2022 12:39:02 (UTC/GMT +8 hours)

EXISTS Operator

The EXISTS checks the existence of a result of a Subquery. The EXISTS subquery tests whether a subquery fetches at least one row. When no data is returned then this operator returns 'FALSE'.
A valid EXISTS subquery must contain an outer reference and it must be a correlated Subquery.
The select list in the EXISTS subquery is not actually used in evaluating the EXISTS so it can contain any valid select list.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE [NOT] EXISTS (subquery)

Parameters:

Name	Description
column_name	Name of the column of the table.
expression1	Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name	Name of the table.
WHERE [NOT] EXISTS	Tests the subquery for the existence of one or more rows. If a single row satisfies the subquery clause, it returns Boolean TRUE. When the subquery returns no matching rows the optional NOT keyword returns a Boolean TRUE.

Contents:

Pictorial Presentation: SQL EXISTS Operator

DBMS Support: EXISTS Operator

DBMS	Command
MySQL	Supported
PostgreSQL	Supported
SQL Server	Supported
Oracle	Supported

Example: SQL EXISTS Operator

To get 'agent_code','agent_name','working_area' and 'commission' from the 'agents', with following conditions-

1. 'grade' in 'customer' table must be 3,

2. 'agent_code' in 'customer' and 'agents' table must match,

3. 'commission' of 'agents' should arrange in ascending order,

4. the above condition (1) and (2) should match at least one row,

the following SQL statement can be used :

SELECT agent_code,agent_name,working_area,commission 
FROM agents
WHERE exists
(SELECT * FROM  customer
WHERE grade=3
AND agents.agent_code=customer.agent_code)
ORDER BY commission;

Sample table: customer

Sample table: agents

Output :

AGENT_CODE AGENT_NAME           WORKING_AREA         COMMISSION
---------- -------------------- -------------------- ----------
A009       Benjamin             Hampshair                   .11
A002       Mukesh               Mumbai                      .11
A008       Alford               New York                    .12
A010       Santakumar           Chennai                     .14

SELECT using EXISTS

Sample table: employees

Sample table: departments

Sample table: locations

To display the employee details who are working in the country UK, we can use the following statement:

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES E
WHERE EXISTS (SELECT 1
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID
AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.COUNTRY_ID = 'UK');

Output:

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
        203 Susan                      6500            40
        179 Charles                    6200            80
        177 Jack                       8400            80
        176 Jonathon                   8600            80
        175 Alyssa                     8800            80
        174 Ellen                     11000            80
        173 Sundita                    6100            80
        172 Elizabeth                  7300            80
        171 William                    7400            80
        170 Tayler                     9600            80
        169 Harrison                  10000            80
        168 Lisa                      11500            80
        167 Amit                       6200            80
        166 Sundar                     6400            80
        . . .

SQL Exists with GROUP BY

Here we have discussed how SQL EXIST can work with GROUP BY in a select statement.

Example:

Sample table: customer

To get 'cust_code', 'cust_name', 'cust_city' and 'grade' from the 'customer' table, with following conditions -

1. 'grade' in 'customer' table must be 2,

2. more than 2 agents are present in grade 2,

3. 'grade' in customer table should make a group,

the following SQL statement can be used :

SELECT cust_code,cust_name,cust_city,grade
FROM customer
WHERE grade=2 AND
EXISTS(
SELECT COUNT(*) FROM customer
WHERE grade=2
GROUP BY grade
HAVING COUNT(*)>2);

Output:

CUST_CODE  CUST_NAME                                CUST_CITY            GRADE
---------- ---------------------------------------- --------------- ----------
C00013     Holmes                                   London                   2
C00001     Micheal                                  New York                 2
C00025     Ravindran                                Bangalore                2
C00024     Cook                                     London                   2
C00018     Fleming                                  Brisban                  2
C00022     Avinash                                  Mumbai                   2
C00017     Srinivas                                 Bangalore                2
C00003     Martin                                   Torento                  2
C00014     Rangarappa                               Bangalore                2
C00016     Venkatpati                               Bangalore                2

SQL Exists with IN

Here is an example of SQL EXISTS operator using IN operator.

In this page we are discussing the usage of SQL EXISTS with IN operator in a SELECT statement.

Example:

Sample table: customer

Sample table: orders

To get distinct 'agent_code' from the 'orders' table, with following conditions -

1. 'agent_code' must be within the resultant 'agent_code' from 'customer' table which satisfies the condition bellow :

2. 'payment_amt' of 'customer' table must be more than 3000,

3. number of rows having said 'payment_amount' is more than 10,

the following SQL statement can be used :

SELECT DISTINCT(agent_code)
FROM orders
WHERE agent_code IN(
SELECT agent_code
FROM customer WHERE payment_amt>3000
AND EXISTS(SELECT COUNT(*)
FROM customer GROUP BY payment_amt
HAVING COUNT(*)>10));

Output:

AGENT_CODE
----------
A004
A002
A007
A009
A011
A012
A010
A001
A008
A006
A005
A003

INSERT using EXISTS

To add employee details to EMP_TEMP table who are working in the country UK, the following SQL statement can be used :

INSERT INTO EMP_TEMP SELECT *
FROM EMPNEW E
WHERE EXISTS (SELECT 1
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID
AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND L.COUNTRY_ID = 'UK');

Sample table: employees

Sample table: departments

Sample table: locations

Output:

Here are the rows inserted into the table EMP_TEMP are:

SQL> SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY  FROM emp_temp;

EMPLOYEE_ID FIRST_NAME           HIRE_DATE     SALARY
----------- -------------------- --------- ----------
        145 John                 01-OCT-04      14000
        146 Karen                05-JAN-05      13500
        147 Alberto              10-MAR-05      12000
        148 Gerald               15-OCT-07      11000
        149 Eleni                29-JAN-08      10500
        150 Peter                30-JAN-05      10000
        151 David                24-MAR-05       9500
        152 Peter                20-AUG-05       9000
        153 Christopher          30-MAR-06       8000
        . . .  
        175 Alyssa               19-MAR-05       8800
        176 Jonathon             24-MAR-06       8600
        177 Jack                 23-APR-06       8400
        179 Charles              04-JAN-08       6200
        203 Susan                07-JUN-02       6500

35 rows selected.

UPDATE using EXISTS

Sample table: employees

To update the commission of the employees to zero who earn the salary 14000 and above, the following SQL statement can be used :

UPDATE EMPNEW E
SET COMMISSION_PCT = 0
WHERE EXISTS (SELECT 1
FROM EMPNEW
WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
AND SALARY>=14000);

Output:

Here is the rows before update the commission :

EMPLOYEE_ID FIRST_NAME               SALARY COMMISSION_PCT
----------- -------------------- ---------- --------------
        100 Steven                    24000
        101 Neena                     17000
        102 Lex                       17000
        145 John                      14000             .4

Here are the effected rows after update the commission :

EMPLOYEE_ID FIRST_NAME               SALARY COMMISSION_PCT
----------- -------------------- ---------- --------------
        100 Steven                    24000              0
        101 Neena                     17000              0
        102 Lex                       17000              0
        145 John                      14000              0

DELETE using EXISTS

Sample table: employees

To delete the employee details from who worked 14 years and above, the following SQL statement can be used :

DELETE FROM EMPNEW E
WHERE EXISTS (SELECT 1
FROM EMPNEW
WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
AND (TO_CHAR(SYSDATE,'YYYY')- TO_CHAR(HIRE_DATE,'YYYY'))>=14);

Output:

Here is the rows before delete who worked for 14 years and more :

EMPLOYEE_ID FIRST_NAME           HIRE_DATE
----------- -------------------- ---------
        102 Lex                  13-JAN-01
        108 Nancy                17-AUG-02
        109 Daniel               16-AUG-02
        114 Den                  07-DEC-02
        203 Susan                07-JUN-02
        204 Hermann              07-JUN-02
        205 Shelley              07-JUN-02
        206 William              07-JUN-02

Here is the output after executing the command :

SQL> DELETE FROM EMPNEW E
  2  WHERE EXISTS (SELECT 1
  3  FROM EMPNEW
  4  WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
  5  AND (TO_CHAR(SYSDATE,'YYYY')- TO_CHAR(HIRE_DATE,'YYYY'))>=14);

8 rows deleted.

SQL NOT Exists

Here we have discussed how SQL NOT EXISTS works with a select statement.

Example:

Sample table: customer

Sample table: orders

To get 'agent_code', 'ord_num', 'ord_amount' and 'cust_code' from the 'orders' table, with following condition -

1. any agent of 'customer' table not having a 'payment_amt' is 1400,

the following SQL statement can be used :

SELECT agent_code,ord_num,ord_amount,cust_code
FROM orders a
WHERE NOT EXISTS(
SELECT agent_code
FROM  customer
WHERE payment_amt=1400 AND a.cust_code=cust_code);

Output :

AGENT_CODE    ORD_NUM ORD_AMOUNT CUST_C
---------- ---------- ---------- ------
A005           200134       4200 C00004
A007           200112       2000 C00016
A004           200122       2500 C00003
A008           200111       1000 C00020
A008           200114       3500 C00002
A002           200133       1200 C00009
A002           200128       3500 C00009
A002           200120        500 C00009
A010           200109       3500 C00011
A004           200108       4000 C00008
A004           200121       1500 C00008
A002           200113       4000 C00022
A002           200126        500 C00022
A002           200123        500 C00022
A006           200129       2500 C00024
........
........

See our Model Database

Practice SQL Exercises