Subqueries in SQL

 

Subqueries in SQL

A subquery is a query nested inside another query, used to return data that will be used in the main query. Subqueries can be classified as Simple Subqueries and Correlated Subqueries. Additionally, keywords like IN and EXISTS are often used with subqueries to filter results.

---

1. Simple Subqueries

A Simple Subquery is a subquery that runs independently of the main query and returns results to be used in the outer query. These are executed first, and their results are passed to the outer query.

Syntax:

SELECT column_name                                                          
FROM table_name                                                             
WHERE column_name = (SELECT column_name FROM another_table WHERE condition);

Example:

• Suppose you have two tables: employees and departments.

employees:

id	name	department_id
1	Alice	101
2	Bob	102
3	Carol	103

departments:

department_id	department_name
101	HR
102	IT

• To find the names of employees who work in the IT department:

  SELECT name                                                 
  FROM employees                                              
  WHERE department_id = (SELECT department_id FROM departments
  WHERE department_name = 'IT');                              
  

  Result:

  name
  ----
  Bob
  

The subquery retrieves the department_id of "IT" (102), and the outer query uses this value to get the employee name.

---

2. Correlated Subqueries

A Correlated Subquery is a subquery that depends on the outer query for its values. The subquery is executed once for each row in the outer query.

Syntax:

SELECT column_name
FROM table_name
WHERE EXISTS (SELECT 1 FROM another_table WHERE table_name.column = another_table.column);

Example:

• To find employees whose salary is higher than the average salary in their department:

employees:

id	name	department_id	salary
1	Alice	101	5000
2	Bob	102	6000
3	Carol	101	7000
4	David	102	5500

• A correlated subquery can be used as follows:

  SELECT name                                         
  FROM employees e1                                   
  WHERE salary > (SELECT AVG(salary) FROM employees e2
  WHERE e1.department_id = e2.department_id);         
  

  Result:

  name
  -----
  Carol
  Bob
  

In this case, for each employee, the subquery calculates the average salary of their department, and the main query selects employees whose salary exceeds that average.

---

3. Using IN with Subqueries

The IN operator checks if a value exists within a list of values returned by a subquery. It’s useful when you want to filter results based on a set of values from another query.

Syntax:

SELECT column_name                                                           
FROM table_name                                                              
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

Example:

• To find employees who work in departments located in "Building A":

departments:

department_id	department_name	building
101	HR	A
102	IT	B
103	Sales	A

• You can use IN with a subquery like this:

  SELECT name                                                                         
  FROM employees                                                                      
  WHERE department_id IN (SELECT department_id FROM departments WHERE building = 'A');
  

  Result:

  name
  ----
  Alice
  Carol
  

The subquery fetches department IDs for "Building A," and the outer query retrieves the names of employees in those departments.

---

4. Using EXISTS with Subqueries

The EXISTS operator checks if the subquery returns any rows. It’s used to test for the existence of records in a subquery.

Syntax:

SELECT column_name                                         
FROM table_name                                            
WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);

Example:

• To find employees who belong to a department that has more than one employee:

employees:

id	name	department_id
1	Alice	101
2	Bob	102
3	Carol	101
4	David	102

• Use EXISTS to check for departments with multiple employees:

  SELECT name                                                                        
  FROM employees e1                                                                  
  WHERE EXISTS (SELECT 1 FROM employees e2 WHERE e1.department_id = e2.department_id 
  GROUP BY department_id HAVING COUNT(*) > 1);                                       
  

  Result:

  name
  ----
  Alice
  Bob
  Carol
  David
  

This query checks if each employee belongs to a department that has more than one employee and returns their names.