Advanced SQL Techniques - Sub queries

A subquery, also known as an inner query or nested query, is a SQL query nested inside a larger query. It's a powerful technique for breaking down complex problems into smaller, more manageable parts, allowing you to perform more advanced data retrieval and manipulation.

---

What is a Subquery?

A subquery is a SELECT statement that is embedded within another SQL statement, such as SELECT, INSERT, UPDATE, or DELETE, or even inside another subquery. The outer statement is called the main query or outer query, and the inner query is the subquery.

The subquery is always executed first, and its result is then used by the outer query to complete its operation. Think of it as a helper query that provides a value or a set of values for the main query to use.

A key rule is that a subquery must always be enclosed in parentheses ().

---

Types of Subqueries

Subqueries can be categorized based on the number of rows and columns they return.

1. Scalar Subquery

A scalar subquery returns a single value (one row with one column). This is the simplest type of subquery. Because it returns a single value, you can use it almost anywhere you would use a literal value or an expression, such as in the SELECT list or the WHERE clause with standard comparison operators (=, <, >).

Example: Find all employees who earn more than the average salary. First, we need the average salary. A subquery can find this for us.

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

Here, (SELECT AVG(salary) FROM employees) is a scalar subquery. It calculates the single average salary value, and the outer query then uses this value to filter the employees.

2. Multi-row Subquery

A multi-row subquery returns one column with multiple rows. Since it returns a list of values, you can't use standard comparison operators like =. Instead, you must use operators that handle sets of values:

• IN: True if a value matches any value in the list returned by the subquery.

• NOT IN: True if a value does not match any value in the list.

• ANY: Compares a value to each value in the list. It returns true if the comparison is true for at least one value. Often used with =, <, >. For example, > ANY means greater than the minimum value in the list.

• ALL: Compares a value to each value in the list. It returns true if the comparison is true for all values in the list. For example, > ALL means greater than the maximum value.

Example: Find all employees who work in a department located in 'Seattle'.

SELECT
  first_name,
  last_name
FROM
  employees
WHERE
  department_id IN (SELECT department_id FROM departments WHERE location = 'Seattle');

The subquery (SELECT department_id FROM departments WHERE location = 'Seattle') returns a list of department IDs for all departments in Seattle. The outer query then finds all employees whose department_id is in that list.

3. Correlated Subquery

A correlated subquery is an inner query that depends on the outer query for its values. Unlike a regular subquery that runs once, a correlated subquery is executed once for each row processed by the outer query. This can make them less efficient but they are very powerful for certain tasks.

The inner query uses a value from the current row of the outer query to perform its calculation.

Example: Find all employees who earn more than the average salary of their own department.

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

In this example, the outer query is aliased as e1. For each employee row processed by e1, the subquery runs. The subquery calculates the average salary for only those employees (e2) who are in the same department as the current employee from the outer query (e1.department_id). This correlation links the inner and outer queries.

---

Where Subqueries Can Be Used

You can place subqueries in several clauses of a SQL statement.

• In the WHERE Clause: This is the most common use, for filtering rows based on a condition determined by the subquery. (All examples above use this).

• In the SELECT Clause: A scalar subquery can be used in the SELECT list to return a calculated value as a column. Example: Show each employee's name and the average salary of their department in a separate column.

    SELECT
      first_name,
      last_name,
      salary,
      (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) AS dept_average_salary
    FROM
      employees e1;
  

• In the FROM Clause: A subquery in the FROM clause creates a temporary table, often called a derived table or inline view. The outer query then selects from this temporary table. The subquery must be given an alias. Example: Find the average of the maximum salaries for each department.

    SELECT
      AVG(max_salaries)
    FROM
      (SELECT MAX(salary) AS max_salaries FROM employees GROUP BY department_id) AS dept_max_salaries;
  

  Here, the subquery first finds the maximum salary for each department. The outer query then calculates the average of those maximums.

---

Advantages and Disadvantages

Advantages 👍

• Simplifies Complex Queries: Breaks down a complex problem into logical, isolated steps.

• Improves Readability: Well-structured subqueries can make the logic of a statement easier to follow than complex joins.

• High Flexibility: Allows for queries that would be difficult or impossible to write with simple joins, such as performing aggregations on a subset of data before comparing it to the main dataset.

Disadvantages 👎

• Performance: Subqueries, especially correlated subqueries, can be slow. A correlated subquery that runs for every row of a large outer table can be a significant performance bottleneck.

• Readability (if overused): Deeply nested subqueries can become very difficult to read and debug. In many cases, using a Common Table Expression (CTE) or a JOIN can be a cleaner and more efficient alternative.