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
DBMS Support: EXISTS Operator
| DBMS | Command |
| MySQL | Supported |
| PostgreSQL | Supported |
| SQL Server | Supported |
| Oracle | Supported |
Example: 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
-- 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
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
-- 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 ........ ........
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SOME
Next: Wildcards & Like
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics