EXPERIMENT 07:
SQL Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS,
UNION, INTERSECT.
1. employees table (contains employee information):
employee_ employee_na salar department
id me y _id
101 Alice 5000 10
102 Bob 6000 10
103 Charlie 7000 20
104 David 8000 20
105 Eva 7500 30
106 Frank 9000 30
2. departments table (contains department information):
department department_n location_
_id ame id
10 HR 100
20 IT 200
30 Marketing 300
1. ANY (Compares a value to any value returned by a subquery)
The ANY operator is used when we want to compare a value against any value returned
by a subquery.
Example:
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);
Explanation:
This query selects employees whose salary is greater than any salary in department 10.
Subquery:
The subquery (SELECT salary FROM employees WHERE department_id = 10) returns the
salaries of employees in department 10. For this example, it returns 5000 and 6000.
Result:
employee_ employee_na salar
id me y
103 Charlie 7000
104 David 8000
105 Eva 7500
106 Frank 9000
2. ALL (Compares a value to all values returned by a subquery)
The ALL operator compares a value to all the values returned by a subquery.
Example:
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);
Explanation:
This query selects employees whose salary is greater than all salaries in department 10.
Subquery:
The subquery (SELECT salary FROM employees WHERE department_id = 10) returns the
salaries of employees in department 10, which are 5000 and 6000.
Result:
employee_ employee_na salar
id me y
103 Charlie 7000
104 David 8000
105 Eva 7500
106 Frank 9000
3. IN (Checks if a value is in a set of values returned by a subquery)
The IN operator checks if a value matches any value in a set of values returned by a
subquery.
Example:
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id
= 200);
Explanation:
This query selects employees who work in departments located at location_id = 200.
Subquery:
The subquery (SELECT department_id FROM departments WHERE location_id = 200)
returns 20 (the department with location_id = 200 is IT).
Result:
employee_ employee_na
id me
103 Charlie
104 David
4. EXISTS (Checks if a subquery returns any rows)
The EXISTS operator checks if a subquery returns any rows. It returns TRUE if the
subquery returns one or more rows.
Example:
SELECT employee_id, employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id =
d.department_id AND d.location_id = 200);
Explanation:
This query selects employees who belong to departments that are located at location_id
= 200.
Subquery:
The subquery (SELECT 1 FROM departments d WHERE e.department_id =
d.department_id AND d.location_id = 200) checks if the employee's department is
located in location_id = 200.
Result:
employee_ employee_na
id me
103 Charlie
104 David
5. NOT EXISTS (Checks if a subquery returns no rows)
The NOT EXISTS operator checks if the subquery returns no rows. It returns TRUE if the
subquery returns no rows.
Example:
SELECT employee_id, employee_name
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE e.department_id =
d.department_id AND d.location_id = 300);
Explanation:
This query selects employees who do not belong to departments located at location_id =
300.
Subquery:
The subquery (SELECT 1 FROM departments d WHERE e.department_id =
d.department_id AND d.location_id = 300) checks if the employee's department is
located in location_id = 300.
Result:
employee_ employee_na
id me
101 Alice
102 Bob
103 Charlie
104 David
6. UNION (Combines the results of two queries and removes duplicates)
The UNION operator combines the result sets of two queries and removes any
duplicates.
Example:
SELECT employee_id, employee_name FROM employees WHERE department_id = 10
UNION
SELECT employee_id, employee_name FROM employees WHERE department_id = 20;
Explanation:
This query combines employees from department 10 and department 20. It removes any
duplicates.
Result:
employee_ employee_na
id me
101 Alice
102 Bob
103 Charlie
104 David
employee_ employee_na
id me
7. INTERSECT (Returns rows that are common to both queries)
The INTERSECT operator returns only the rows that appear in both queries.
Example:
SELECT employee_id, employee_name FROM employees WHERE department_id = 10
INTERSECT
SELECT employee_id, employee_name FROM employees WHERE department_id = 20;
Explanation:
This query returns employees who are present in both department 10 and department
20. Since no employee belongs to both departments, the result will be empty.
Result:
employee_na
employee_id
me
(No common
employees)
