w3resource

SQL EXISTS Operator

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

SQL EXISTS Operator

DBMS Support: EXISTS Operator

DBMS Command
MySQL Supported
PostgreSQL Supported
SQL Server Supported
Oracle Supported

Example: SQL EXISTS Operator

Sample table : customer
  
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
Sample table : agents
 
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

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
-- Selecting specific columns: agent_code, agent_name, working_area, and commission
FROM agents
-- From the table named "agents"
WHERE EXISTS (
    SELECT * FROM customer
    -- Selecting all columns from the "customer" table
    WHERE grade = 3
    -- Where the value in the column "grade" is 3
    AND agents.agent_code = customer.agent_code
    -- And the value in the column "agent_code" in the "agents" table matches the value in the column "agent_code" in the "customer" table
)
-- Where there exists at least one row returned by the subquery
ORDER BY commission;
-- Ordering the result set by the column "commission"

Explanation:

  • SELECT agent_code, agent_name, working_area, commission: This specifies that we want to retrieve specific columns (agent_code, agent_name, working_area, and commission) from the table.

  • FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".

  • WHERE EXISTS (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where there exists at least one row returned by the subquery should be included in the result set.

  • The subquery inside EXISTS (...) selects all columns from the "customer" table where the value in the column "grade" is 3 and the value in the column "agent_code" in the "agents" table matches the value in the column "agent_code" in the "customer" table.

  • ORDER BY commission: This orders the result set by the column "commission".

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
  
employee_id  first_name  last_name   email       phone_number  hire_date   job_id      salary      commission_pct  manager_id  department_id
-----------  ----------  ----------  ----------  ------------  ----------  ----------  ----------  --------------  ----------  -------------
100          Steven      King        SKING       515.123.4567  6/17/1987   AD_PRES     24000                                   90
101          Neena       Kochhar     NKOCHHAR    515.123.4568  6/18/1987   AD_VP       17000                       100         90
102          Lex         De Haan     LDEHAAN     515.123.4569  6/19/1987   AD_VP       17000                       100         90
103          Alexander   Hunold      AHUNOLD     590.423.4567  6/20/1987   IT_PROG     9000                        102         60
104          Bruce       Ernst       BERNST      590.423.4568  6/21/1987   IT_PROG     6000                        103         60
105          David       Austin      DAUSTIN     590.423.4569  6/22/1987   IT_PROG     4800                        103         60
106          Valli       Pataballa   VPATABAL    590.423.4560  6/23/1987   IT_PROG     4800                        103         60
107          Diana       Lorentz     DLORENTZ    590.423.5567  6/24/1987   IT_PROG     4200                        103         60
108          Nancy       Greenberg   NGREENBE    515.124.4569  6/25/1987   FI_MGR      12000                       101         100
109          Daniel      Faviet      DFAVIET     515.124.4169  6/26/1987   FI_ACCOUNT  9000                        108         100
110          John        Chen        JCHEN       515.124.4269  6/27/1987   FI_ACCOUNT  8200                        108         100
111          Ismael      Sciarra     ISCIARRA    515.124.4369  6/28/1987   FI_ACCOUNT  7700                        108         100
112          Jose Manue  Urman       JMURMAN     515.124.4469  6/29/1987   FI_ACCOUNT  7800                        108         100
113          Luis        Popp        LPOPP       515.124.4567  6/30/1987   FI_ACCOUNT  6900                        108         100
114          Den         Raphaely    DRAPHEAL    515.127.4561  7/1/1987    PU_MAN      11000                       100         30
115          Alexander   Khoo        AKHOO       515.127.4562  7/2/1987    PU_CLERK    3100                        114         30
116          Shelli      Baida       SBAIDA      515.127.4563  7/3/1987    PU_CLERK    2900                        114         30
117          Sigal       Tobias      STOBIAS     515.127.4564  7/4/1987    PU_CLERK    2800                        114         30
118          Guy         Himuro      GHIMURO     515.127.4565  7/5/1987    PU_CLERK    2600                        114         30
119          Karen       Colmenares  KCOLMENA    515.127.4566  7/6/1987    PU_CLERK    2500                        114         30
120          Matthew     Weiss       MWEISS      650.123.1234  7/7/1987    ST_MAN      8000                        100         50
121          Adam        Fripp       AFRIPP      650.123.2234  7/8/1987    ST_MAN      8200                        100         50
122          Payam       Kaufling    PKAUFLIN    650.123.3234  7/9/1987    ST_MAN      7900                        100         50
123          Shanta      Vollman     SVOLLMAN    650.123.4234  7/10/1987   ST_MAN      6500                        100         50
124          Kevin       Mourgos     KMOURGOS    650.123.5234  7/11/1987   ST_MAN      5800                        100         50
125          Julia       Nayer       JNAYER      650.124.1214  7/12/1987   ST_CLERK    3200                        120         50
126          Irene       Mikkilinen  IMIKKILI    650.124.1224  7/13/1987   ST_CLERK    2700                        120         50
127          James       Landry      JLANDRY     650.124.1334  7/14/1987   ST_CLERK    2400                        120         50
128          Steven      Markle      SMARKLE     650.124.1434  7/15/1987   ST_CLERK    2200                        120         50
129          Laura       Bissot      LBISSOT     650.124.5234  7/16/1987   ST_CLERK    3300                        121         50
130          Mozhe       Atkinson    MATKINSO    650.124.6234  7/17/1987   ST_CLERK    2800                        121         50
131          James       Marlow      JAMRLOW     650.124.7234  7/18/1987   ST_CLERK    2500                        121         50
132          TJ          Olson       TJOLSON     650.124.8234  7/19/1987   ST_CLERK    2100                        121         50
133          Jason       Mallin      JMALLIN     650.127.1934  7/20/1987   ST_CLERK    3300                        122         50
134          Michael     Rogers      MROGERS     650.127.1834  7/21/1987   ST_CLERK    2900                        122         50
135          Ki          Gee         KGEE        650.127.1734  7/22/1987   ST_CLERK    2400                        122         50
136          Hazel       Philtanker  HPHILTAN    650.127.1634  7/23/1987   ST_CLERK    2200                        122         50
137          Renske      Ladwig      RLADWIG     650.121.1234  7/24/1987   ST_CLERK    3600                        123         50
138          Stephen     Stiles      SSTILES     650.121.2034  7/25/1987   ST_CLERK    3200                        123         50
139          John        Seo         JSEO        650.121.2019  7/26/1987   ST_CLERK    2700                        123         50
140          Joshua      Patel       JPATEL      650.121.1834  7/27/1987   ST_CLERK    2500                        123         50
141          Trenna      Rajs        TRAJS       650.121.8009  7/28/1987   ST_CLERK    3500                        124         50
142          Curtis      Davies      CDAVIES     650.121.2994  7/29/1987   ST_CLERK    3100                        124         50
143          Randall     Matos       RMATOS      650.121.2874  7/30/1987   ST_CLERK    2600                        124         50
144          Peter       Vargas      PVARGAS     650.121.2004  7/31/1987   ST_CLERK    2500                        124         50
145          John        Russell     JRUSSEL     011.44.1344.  8/1/1987    SA_MAN      14000       0.4             100         80
146          Karen       Partners    KPARTNER    011.44.1344.  8/2/1987    SA_MAN      13500       0.3             100         80
147          Alberto     Errazuriz   AERRAZUR    011.44.1344.  8/3/1987    SA_MAN      12000       0.3             100         80
148          Gerald      Cambrault   GCAMBRAU    011.44.1344.  8/4/1987    SA_MAN      11000       0.3             100         80
149          Eleni       Zlotkey     EZLOTKEY    011.44.1344.  8/5/1987    SA_MAN      10500       0.2             100         80
150          Peter       Tucker      PTUCKER     011.44.1344.  8/6/1987    SA_REP      10000       0.3             145         80
151          David       Bernstein   DBERNSTE    011.44.1344.  8/7/1987    SA_REP      9500        0.25            145         80
152          Peter       Hall        PHALL       011.44.1344.  8/8/1987    SA_REP      9000        0.25            145         80
153          Christophe  Olsen       COLSEN      011.44.1344.  8/9/1987    SA_REP      8000        0.2             145         80
154          Nanette     Cambrault   NCAMBRAU    011.44.1344.  8/10/1987   SA_REP      7500        0.2             145         80
155          Oliver      Tuvault     OTUVAULT    011.44.1344.  8/11/1987   SA_REP      7000        0.15            145         80
156          Janette     King        JKING       011.44.1345.  8/12/1987   SA_REP      10000       0.35            146         80
157          Patrick     Sully       PSULLY      011.44.1345.  8/13/1987   SA_REP      9500        0.35            146         80
158          Allan       McEwen      AMCEWEN     011.44.1345.  8/14/1987   SA_REP      9000        0.35            146         80
159          Lindsey     Smith       LSMITH      011.44.1345.  8/15/1987   SA_REP      8000        0.3             146         80
160          Louise      Doran       LDORAN      011.44.1345.  8/16/1987   SA_REP      7500        0.3             146         80
161          Sarath      Sewall      SSEWALL     011.44.1345.  8/17/1987   SA_REP      7000        0.25            146         80
162          Clara       Vishney     CVISHNEY    011.44.1346.  8/18/1987   SA_REP      10500       0.25            147         80
163          Danielle    Greene      DGREENE     011.44.1346.  8/19/1987   SA_REP      9500        0.15            147         80
164          Mattea      Marvins     MMARVINS    011.44.1346.  8/20/1987   SA_REP      7200        0.1             147         80
165          David       Lee         DLEE        011.44.1346.  8/21/1987   SA_REP      6800        0.1             147         80
166          Sundar      Ande        SANDE       011.44.1346.  8/22/1987   SA_REP      6400        0.1             147         80
167          Amit        Banda       ABANDA      011.44.1346.  8/23/1987   SA_REP      6200        0.1             147         80
168          Lisa        Ozer        LOZER       011.44.1343.  8/24/1987   SA_REP      11500       0.25            148         80
169          Harrison    Bloom       HBLOOM      011.44.1343.  8/25/1987   SA_REP      10000       0.2             148         80
170          Tayler      Fox         TFOX        011.44.1343.  8/26/1987   SA_REP      9600        0.2             148         80
171          William     Smith       WSMITH      011.44.1343.  8/27/1987   SA_REP      7400        0.15            148         80
172          Elizabeth   Bates       EBATES      011.44.1343.  8/28/1987   SA_REP      7300        0.15            148         80
173          Sundita     Kumar       SKUMAR      011.44.1343.  8/29/1987   SA_REP      6100        0.1             148         80
174          Ellen       Abel        EABEL       011.44.1644.  8/30/1987   SA_REP      11000       0.3             149         80
175          Alyssa      Hutton      AHUTTON     011.44.1644.  8/31/1987   SA_REP      8800        0.25            149         80
176          Jonathon    Taylor      JTAYLOR     011.44.1644.  9/1/1987    SA_REP      8600        0.2             149         80
177          Jack        Livingston  JLIVINGS    011.44.1644.  9/2/1987    SA_REP      8400        0.2             149         80
178          Kimberely   Grant       KGRANT      011.44.1644.  9/3/1987    SA_REP      7000        0.15            149
179          Charles     Johnson     CJOHNSON    011.44.1644.  9/4/1987    SA_REP      6200        0.1             149         80
180          Winston     Taylor      WTAYLOR     650.507.9876  9/5/1987    SH_CLERK    3200                        120         50
181          Jean        Fleaur      JFLEAUR     650.507.9877  9/6/1987    SH_CLERK    3100                        120         50
182          Martha      Sullivan    MSULLIVA    650.507.9878  9/7/1987    SH_CLERK    2500                        120         50
183          Girard      Geoni       GGEONI      650.507.9879  9/8/1987    SH_CLERK    2800                        120         50
184          Nandita     Sarchand    NSARCHAN    650.509.1876  9/9/1987    SH_CLERK    4200                        121         50
185          Alexis      Bull        ABULL       650.509.2876  9/10/1987   SH_CLERK    4100                        121         50
186          Julia       Dellinger   JDELLING    650.509.3876  9/11/1987   SH_CLERK    3400                        121         50
187          Anthony     Cabrio      ACABRIO     650.509.4876  9/12/1987   SH_CLERK    3000                        121         50
188          Kelly       Chung       KCHUNG      650.505.1876  9/13/1987   SH_CLERK    3800                        122         50
189          Jennifer    Dilly       JDILLY      650.505.2876  9/14/1987   SH_CLERK    3600                        122         50
190          Timothy     Gates       TGATES      650.505.3876  9/15/1987   SH_CLERK    2900                        122         50
191          Randall     Perkins     RPERKINS    650.505.4876  9/16/1987   SH_CLERK    2500                        122         50
192          Sarah       Bell        SBELL       650.501.1876  9/17/1987   SH_CLERK    4000                        123         50
193          Britney     Everett     BEVERETT    650.501.2876  9/18/1987   SH_CLERK    3900                        123         50
194          Samuel      McCain      SMCCAIN     650.501.3876  9/19/1987   SH_CLERK    3200                        123         50
195          Vance       Jones       VJONES      650.501.4876  9/20/1987   SH_CLERK    2800                        123         50
196          Alana       Walsh       AWALSH      650.507.9811  9/21/1987   SH_CLERK    3100                        124         50
197          Kevin       Feeney      KFEENEY     650.507.9822  9/22/1987   SH_CLERK    3000                        124         50
198          Donald      OConnell    DOCONNEL    650.507.9833  9/23/1987   SH_CLERK    2600                        124         50
199          Douglas     Grant       DGRANT      650.507.9844  9/24/1987   SH_CLERK    2600                        124         50
200          Jennifer    Whalen      JWHALEN     515.123.4444  9/25/1987   AD_ASST     4400                        101         10
201          Michael     Hartstein   MHARTSTE    515.123.5555  9/26/1987   MK_MAN      13000                       100         20
202          Pat         Fay         PFAY        603.123.6666  9/27/1987   MK_REP      6000                        201         20
203          Susan       Mavris      SMAVRIS     515.123.7777  9/28/1987   HR_REP      6500                        101         40
204          Hermann     Baer        HBAER       515.123.8888  9/29/1987   PR_REP      10000                       101         70
205          Shelley     Higgins     SHIGGINS    515.123.8080  9/30/1987   AC_MGR      12000                       101         110
206          William     Gietz       WGIETZ      515.123.8181  10/1/1987   AC_ACCOUNT  8300                        205         110
Sample table: departments
 
+---------------+----------------------+------------+-------------+
| DEPARTMENT_ID | DEPARTMENT_NAME      | MANAGER_ID | LOCATION_ID |
+---------------+----------------------+------------+-------------+
|            10 | Administration       |        200 |        1700 |
|            20 | Marketing            |        201 |        1800 |
|            30 | Purchasing           |        114 |        1700 |
|            40 | Human Resources      |        203 |        2400 |
|            50 | Shipping             |        121 |        1500 |
|            60 | IT                   |        103 |        1400 |
|            70 | Public Relations     |        204 |        2700 |
|            80 | Sales                |        145 |        2500 |
|            90 | Executive            |        100 |        1700 |
|           100 | Finance              |        108 |        1700 |
|           110 | Accounting           |        205 |        1700 |
|           120 | Treasury             |          0 |        1700 |
|           130 | Corporate Tax        |          0 |        1700 |
|           140 | Control And Credit   |          0 |        1700 |
|           150 | Shareholder Services |          0 |        1700 |
|           160 | Benefits             |          0 |        1700 |
|           170 | Manufacturing        |          0 |        1700 |
|           180 | Construction         |          0 |        1700 |
|           190 | Contracting          |          0 |        1700 |
|           200 | Operations           |          0 |        1700 |
|           210 | IT Support           |          0 |        1700 |
|           220 | NOC                  |          0 |        1700 |
|           230 | IT Helpdesk          |          0 |        1700 |
|           240 | Government Sales     |          0 |        1700 |
|           250 | Retail Sales         |          0 |        1700 |
|           260 | Recruiting           |          0 |        1700 |
|           270 | Payroll              |          0 |        1700 |
+---------------+----------------------+------------+-------------+
Sample table: locations
 
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
| LOCATION_ID | STREET_ADDRESS                           | POSTAL_CODE | CITY                | STATE_PROVINCE    | COUNTRY_ID |
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+
|        1000 | 1297 Via Cola di Rie                     | 989         | Roma                |                   | IT         |
|        1100 | 93091 Calle della Testa                  | 10934       | Venice              |                   | IT         |
|        1200 | 2017 Shinjuku-ku                         | 1689        | Tokyo               | Tokyo Prefecture  | JP         |
|        1300 | 9450 Kamiya-cho                          | 6823        | Hiroshima           |                   | JP         |
|        1400 | 2014 Jabberwocky Rd                      | 26192       | Southlake           | Texas             | US         |
|        1500 | 2011 Interiors Blvd                      | 99236       | South San Francisco | California        | US         |
|        1600 | 2007 Zagora St                           | 50090       | South Brunswick     | New Jersey        | US         |
|        1700 | 2004 Charade Rd                          | 98199       | Seattle             | Washington        | US         |
|        1800 | 147 Spadina Ave                          | M5V 2L7     | Toronto             | Ontario           | CA         |
|        1900 | 6092 Boxwood St                          | YSW 9T2     | Whitehorse          | Yukon             | CA         |
|        2000 | 40-5-12 Laogianggen                      | 190518      | Beijing             |                   | CN         |
|        2100 | 1298 Vileparle (E)                       | 490231      | Bombay              | Maharashtra       | IN         |
|        2200 | 12-98 Victoria Street                    | 2901        | Sydney              | New South Wales   | AU         |
|        2300 | 198 Clementi North                       | 540198      | Singapore           |                   | SG         |
|        2400 | 8204 Arthur St                           |             | London              |                   | UK         |
|        2500 | Magdalen Centre, The Oxford Science Park | OX9 9ZB     | Oxford              | Oxford            | UK         |
|        2600 | 9702 Chester Road                        | 9629850293  | Stretford           | Manchester        | UK         |
|        2700 | Schwanthalerstr. 7031                    | 80925       | Munich              | Bavaria           | DE         |
|        2800 | Rua Frei Caneca 1360                     | 01307-002   | Sao Paulo           | Sao Paulo         | BR         |
|        2900 | 20 Rue des Corps-Saints                  | 1730        | Geneva              | Geneve            | CH         |
|        3000 | Murtenstrasse 921                        | 3095        | Bern                | BE                | CH         |
|        3100 | Pieter Breughelstraat 837                | 3029SK      | Utrecht             | Utrecht           | NL         |
|        3200 | Mariano Escobedo 9991                    | 11932       | Mexico City         | Distrito Federal, | MX         |
+-------------+------------------------------------------+-------------+---------------------+-------------------+------------+

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
-- Selecting specific columns: EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES E
-- From the table named "EMPLOYEES" with an alias "E"
WHERE EXISTS (
    SELECT 1
    -- Selecting a constant value 1
    FROM DEPARTMENTS D, LOCATIONS L
    -- From tables DEPARTMENTS and LOCATIONS
    WHERE D.LOCATION_ID = L.LOCATION_ID
    -- Where the LOCATION_ID in DEPARTMENTS matches the LOCATION_ID in LOCATIONS
    AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
    -- And the DEPARTMENT_ID in DEPARTMENTS matches the DEPARTMENT_ID in EMPLOYEES
    AND L.COUNTRY_ID = 'UK'
    -- And the COUNTRY_ID in LOCATIONS is 'UK'
);
-- Where there exists at least one row returned by the subquery

Explanation:

  • SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID: This specifies that we want to retrieve specific columns (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID) from the table.

  • FROM EMPLOYEES E: This indicates the table from which we want to retrieve the data. In this case, the table is named "EMPLOYEES", and we've given it an alias "E".

  • WHERE EXISTS (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where there exists at least one row returned by the subquery should be included in the result set.

  • The subquery inside EXISTS (...) selects a constant value of 1 from the tables DEPARTMENTS and LOCATIONS, where the LOCATION_ID in DEPARTMENTS matches the LOCATION_ID in LOCATIONS, the DEPARTMENT_ID in DEPARTMENTS matches the DEPARTMENT_ID in EMPLOYEES, and the COUNTRY_ID in LOCATIONS is '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
-- Selecting specific columns: cust_code, cust_name, cust_city, grade
FROM customer
-- From the table named "customer"
WHERE grade = 2
-- Where the value in the column "grade" is equal to 2
AND EXISTS (
    SELECT COUNT(*)
    -- Selecting the count of rows
    FROM customer
    -- From the same table "customer"
    WHERE grade = 2
    -- Where the value in the column "grade" is equal to 2
    GROUP BY grade
    -- Grouping the results by grade
    HAVING COUNT(*) > 2
    -- Having more than 2 rows in the group
);
-- Where there exists at least one row returned by the subquery

Explanation:

  • SELECT cust_code, cust_name, cust_city, grade: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, grade) from the table.

  • FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".

  • WHERE grade = 2: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column "grade" is equal to 2 should be included in the result set.

  • AND EXISTS (...): This is a conditional clause that further filters the rows returned by the query. It specifies that only rows where there exists at least one row returned by the subquery should be included in the result set.

  • The subquery inside EXISTS (...) selects the count of rows from the "customer" table where the value in the column "grade" is equal to 2, groups the results by grade, and then filters the groups to only include those with more than 2 rows.

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)
-- Selecting distinct values of agent_code
FROM orders
-- From the table named "orders"
WHERE agent_code IN (
    SELECT agent_code
    -- Selecting agent_code
    FROM customer
    -- From the table named "customer"
    WHERE payment_amt > 3000
    -- Where the value in the column "payment_amt" is greater than 3000
    AND EXISTS (
        SELECT COUNT(*)
        -- Selecting the count of rows
        FROM customer
        -- From the same table "customer"
        GROUP BY payment_amt
        -- Grouping the results by payment_amt
        HAVING COUNT(*) > 10
        -- Having more than 10 rows in the group
    )
    -- Where there exists at least one row returned by the subquery
);
-- Where the value in the column "agent_code" matches any value returned by the subquery

Explanation:

  • SELECT DISTINCT(agent_code): This specifies that we want to retrieve distinct values of agent_code.

  • FROM orders: This indicates the table from which we want to retrieve the data. In this case, the table is named "orders".

  • WHERE agent_code IN (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the column "agent_code" matches any value returned by the subquery should be included in the result set.

  • The subquery inside IN (...) selects agent_code from the "customer" table where the value in the column "payment_amt" is greater than 3000 and there exists at least one group with more than 10 rows in the "customer" table grouped by "payment_amt".

Output:

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

INSERT using EXISTS

Sample table: employees


Sample table: departments


Sample table: locations


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 *
-- Inserting all columns into the table EMP_TEMP
FROM EMPNEW E
-- From the table EMPNEW with an alias E
WHERE EXISTS (
    SELECT 1
    -- Selecting a constant value 1
    FROM DEPARTMENTS D, LOCATIONS L
    -- From tables DEPARTMENTS and LOCATIONS
    WHERE D.LOCATION_ID = L.LOCATION_ID
    -- Where the LOCATION_ID in DEPARTMENTS matches the LOCATION_ID in LOCATIONS
    AND D.DEPARTMENT_ID = E.DEPARTMENT_ID
    -- And the DEPARTMENT_ID in DEPARTMENTS matches the DEPARTMENT_ID in EMPNEW
    AND L.COUNTRY_ID = 'UK'
    -- And the COUNTRY_ID in LOCATIONS is 'UK'
);
-- Where there exists at least one row returned by the subquery, indicating a match between EMPNEW and DEPARTMENTS/LOCATIONS

Explanation:

  • INSERT INTO EMP_TEMP SELECT *: This specifies that we want to insert all columns from the query result into the table EMP_TEMP.

  • FROM EMPNEW E: This indicates the table from which we want to retrieve the data. In this case, the table is named "EMPNEW", and we've given it an alias "E".

  • WHERE EXISTS (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where there exists at least one row returned by the subquery should be included in the result set.

  • The subquery inside EXISTS (...) selects a constant value of 1 from the tables DEPARTMENTS and LOCATIONS, where the LOCATION_ID in DEPARTMENTS matches the LOCATION_ID in LOCATIONS, the DEPARTMENT_ID in DEPARTMENTS matches the DEPARTMENT_ID in EMPNEW, and the COUNTRY_ID in LOCATIONS is 'UK'.

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
-- Updating the table EMPNEW and giving it an alias E
SET COMMISSION_PCT = 0
-- Setting the value of the column COMMISSION_PCT to 0
WHERE EXISTS (
    SELECT 1
    -- Selecting a constant value 1
    FROM EMPNEW
    -- From the same table EMPNEW
    WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
    -- Where the EMPLOYEE_ID in EMPNEW matches the EMPLOYEE_ID in the outer query
    AND SALARY >= 14000
    -- And the SALARY in EMPNEW is greater than or equal to 14000
);
-- Where there exists at least one row returned by the subquery

Explanation:

  • UPDATE EMPNEW E: This specifies that we want to update the table EMPNEW and gives it an alias E to reference it in the query.

  • SET COMMISSION_PCT = 0: This sets the value of the column COMMISSION_PCT to 0 for the rows that meet the specified conditions.

  • WHERE EXISTS (...): This is a conditional clause that filters the rows to be updated. It specifies that only rows where there exists at least one row returned by the subquery should be updated.

  • The subquery inside EXISTS (...) selects a constant value of 1 from the EMPNEW table where the EMPLOYEE_ID matches the EMPLOYEE_ID in the outer query and the SALARY is greater than or equal to 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
-- Deleting rows from the table EMPNEW and giving it an alias E
WHERE EXISTS (
    SELECT 1
    -- Selecting a constant value 1
    FROM EMPNEW
    -- From the same table EMPNEW
    WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
    -- Where the EMPLOYEE_ID in EMPNEW matches the EMPLOYEE_ID in the outer query
    AND (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(HIRE_DATE, 'YYYY')) >= 14
    -- And the difference in years between the current date (SYSDATE) and HIRE_DATE is greater than or equal to 14
);
-- Where there exists at least one row returned by the subquery
;

Explanation:

  • DELETE FROM EMPNEW E: This specifies that we want to delete rows from the table EMPNEW and gives it an alias E to reference it in the query.

  • WHERE EXISTS (...): This is a conditional clause that filters the rows to be deleted. It specifies that only rows where there exists at least one row returned by the subquery should be deleted.

  • The subquery inside EXISTS (...) selects a constant value of 1 from the EMPNEW table where the EMPLOYEE_ID matches the EMPLOYEE_ID in the outer query and the difference in years between the current date (SYSDATE) and HIRE_DATE is greater than or equal to 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 :


DELETE FROM EMPNEW E
-- Deleting rows from the table EMPNEW and giving it an alias E
WHERE EXISTS (
    SELECT 1
    -- Selecting a constant value 1
    FROM EMPNEW
    -- From the same table EMPNEW
    WHERE EMPLOYEE_ID = E.EMPLOYEE_ID
    -- Where the EMPLOYEE_ID in EMPNEW matches the EMPLOYEE_ID in the outer query
    AND (TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(HIRE_DATE, 'YYYY')) >= 14
    -- And the difference in years between the current date (SYSDATE) and HIRE_DATE is greater than or equal to 14
);
-- Where there exists at least one row returned by the subquery


8 rows deleted.

Explanation:

  • DELETE FROM EMPNEW E: This specifies that we want to delete rows from the table EMPNEW and gives it an alias E to reference it in the query.

  • WHERE EXISTS (...): This is a conditional clause that filters the rows to be deleted. It specifies that only rows where there exists at least one row returned by the subquery should be deleted.

  • The subquery inside EXISTS (...) selects a constant value of 1 from the EMPNEW table where the EMPLOYEE_ID matches the EMPLOYEE_ID in the outer query and the difference in years between the current date (SYSDATE) and HIRE_DATE is greater than or equal to 14.

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
-- Selecting specific columns: agent_code, ord_num, ord_amount, cust_code
FROM orders a
-- From the table named "orders" and aliasing it as "a"
WHERE NOT EXISTS (
    SELECT agent_code
    -- Selecting the column agent_code
    FROM customer
    -- From the table named "customer"
    WHERE payment_amt = 1400
    -- Where the value in the column payment_amt is 1400
    AND a.cust_code = cust_code
    -- And the value in the column cust_code from the outer query matches the value in the column cust_code in the subquery
);
-- Where there does not exist any rows returned by the subquery

Explanation:

  • SELECT agent_code, ord_num, ord_amount, cust_code: This specifies that we want to retrieve specific columns (agent_code, ord_num, ord_amount, cust_code).

  • FROM orders a: This indicates the table from which we want to retrieve the data. In this case, the table is named "orders", and we've given it an alias "a".

  • WHERE NOT EXISTS (...): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where there does not exist any row returned by the subquery should be included in the result set.

  • The subquery inside NOT EXISTS (...) selects the column agent_code from the "customer" table where the payment_amt is 1400 and the cust_code matches with the cust_code from the outer query.

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

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SOME
Next: Wildcards & Like



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql/special-operators/sql_exists.php