Sql Subqueries In Select From Where Clauses Complete Guide

SQL Subqueries in SELECT, FROM, and WHERE Clauses

Subqueries in the SELECT Clause:

Using a subquery in the SELECT clause allows you to evaluate and return scalar values (single value) from one or more subqueries for each row of the main query.

Example:

SELECT employee_id, first_name, last_name, salary, 
       (SELECT MAX(salary) FROM employees) AS max_salary,
       salary / (SELECT AVG(salary) FROM employees) AS salary_ratio
FROM employees;

Explanation:

• max_salary: This column is calculated using a subquery that finds the maximum salary from the employees table.
• salary_ratio: This column shows each employee's salary as a ratio against the average salary. The subquery computes the average salary.

Subqueries in the FROM Clause:

Subqueries can also be used in the FROM clause, where they function essentially like derived tables. You can assign an alias to the subquery result, which can then be referenced in the outer query.

Example:

SELECT e.department_id, d.department_name, e.avg_salary
FROM
    (SELECT department_id, AVG(salary) AS avg_salary
     FROM employees
     GROUP BY department_id) e
JOIN departments d ON e.department_id = d.department_id;

Explanation:

• The inner subquery calculates the average salary for each department and creates a derived table e.
• This derived table is then joined with the departments table using department_id for further analysis involving department names and their average salaries.

Subqueries in the WHERE Clause:

Subqueries in the WHERE clause offer flexibility over conditions that involve comparisons against a set of values returned by a subquery.

Single-row Subqueries: These are used when the subquery returns only one value.

Example:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation: This query selects the employee ID, first name, last name, and salary of all employees whose salary is greater than the average salary of all employees. The comparison uses the single returned value from the subquery.

Multiple-row Subqueries: These are used when the subquery returns multiple rows. Common operators include IN, ANY, ALL, EXISTS.

• IN: Checks if a value is in a list of values.
• ANY/ALL: Used for comparison purposes against multiple values.
• EXISTS: Checks the existence of records based on the subquery's result.

Example using IN:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id IN (SELECT department_id FROM employees WHERE location_id = 100);

Explanation: This query selects employee details from those who belong to departments located at location_id = 100. Using the IN operator in combination with the subquery enables this multi-row evaluation.

Example using ANY:

SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);

Explanation: This query returns details of all employees whose salary is higher than the maximum salary of any department. The ANY keyword is used here to compare against multiple maximum values.

Example using EXISTS:

SELECT department_id, department_name
FROM departments
WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id);

Explanation: The EXISTS subquery checks if there are any employees belonging to each department listed in the departments table. If the subquery finds at least one record, the department detail is included in the result.

Important Information:

• Performance Considerations: Nested subqueries can impact performance negatively. It's always best practice to consider other query structures like JOINs if there is a large dataset.

• Correlated Subqueries: These subqueries refer to an outer query for their execution. Each time the outer query processes a row, the correlated subquery runs once per row.

  Example of Correlated Subquery:

  SELECT employee_id, first_name, last_name, salary, department_id
  FROM employees e1
  WHERE salary = (SELECT MAX(salary)
                  FROM employees e2
                  WHERE e1.department_id = e2.department_id);
  

  Explanation: The correlated subquery fetches the highest salary for the department corresponding to the current row being processed by the outer query. It compares this value with the employee salary from the outer query for filtering.

• Scalar Subqueries: Must return exactly one row and one column.

• Multiple-row Subqueries: Return multiple rows and can be used with operators like IN, ANY, and ALL.

• Nested Subqueries: These can be nested inside others, forming complex queries which might need careful handling due to their complexity and potential performance issues.

Use Cases:

• Conditional Comparisons: Evaluating conditional criteria dynamically.
• Deriving Data: Calculating aggregates, averages, counts, etc., for complex conditions.
• Data Filtering: Filtering data based on results from other tables or subsets within the same table.