SQLite Exists operator

Introduction

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.

SQLite Version: 3.8

Example

Here is an example of SQL EXISTS operator

Sample table: customer

Sample table: agents

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;

Here is the result.

Sample Output:

AGENT_CODE  AGENT_NAME                                WORKING_AREA  COMMISSION
----------  ----------------------------------------  ------------  ----------
A002        Mukesh                                    Mumbai        0.11
A009        Benjamin                                  Hampshair     0.11
A008        Alford                                    New York      0.12
A010        Santakumar                                Chennai       0.14

Example: SQL Exists with group by

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

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

Here is the result.

Sample 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

Example: SQL NOT Exists

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

Sample table: customer

Sample table: orders

To get 'agent_code', 'ord_num', 'ord_amount' and 'cust_code' from the 'orders' table, with the 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 
WHERE NOT EXISTS
(SELECT agent_code FROM customer WHERE payment_amt=1400);

Here is the result.

Sample Output:

AGENT_CODE  ORD_NUM     ORD_AMOUNT  CUST_CODE
----------  ----------  ----------  ----------
A003        200100      1000        C00015
A010        200110      3000        C00019
A010        200107      4500        C00007
A007        200112      2000        C00016
A002        200113      4000        C00022
A012        200102      2000        C00012
A008        200114      3500        C00002
A004        200122      2500        C00003
A006        200118      500         C00023
A010        200119      4000        C00007
A004        200121      1500        C00008
A011        200130      2500        C00025
A005        200134      4200        C00004
A013        200115      2000        C00013
A004        200108      4000        C00008
A005        200103      1500        C00021
A011        200105      2500        C00025
A010        200109      3500        C00011
A008        200101      3000        C00001
A008        200111      1000        C00020
A004        200104      1500        C00006
A002        200106      2500        C00005
A005        200125      2000        C00018
A001        200117      800         C00014
A002        200123      500         C00022
A002        200120      500         C00009
A009        200116      500         C00010
A007        200124      500         C00017
A002        200126      500         C00022
A006        200129      2500        C00024
A003        200127      2500        C00015
A002        200128      3500        C00009
A010        200135      2000        C00007
A012        200131      900         C00012
A002        200133      1200        C00009
A013        200132      4000        C00013

Previous: IN and NOT IN Operator
Next: Insert