Skip to content

Subqueries and Correlated subqueries QNA

Jump to bottom
Praveen Kumar Anwla edited this page Feb 13, 2025 · 33 revisions

Q1: Can you explain when to use subqueries in SELECT, FROM, WHERE, and HAVING clauses, and when to use which one?

Ans: Subqueries are queries embedded within other queries, and they can be used in various clauses of a SQL statement. The choice of where to use a subquery depends on the specific requirements of your query. Here's a breakdown of when to use subqueries in different clauses:

  1. Subqueries in SELECT Clause:
    • Calculating Aggregates or Derived Values: Subqueries in the SELECT clause are powerful when you need to calculate aggregated values or perform complex calculations for each row.
    • Use when you need to retrieve a single value to be displayed alongside each row in the result set.

Example 1: Suppose you want to retrieve the list of employees along with the average salary for all employees in the company.

     SELECT 
     employee_id,
     employee_name,
     salary,(SELECT AVG(salary) FROM employees) AS average_salary
     FROM employees;

Note: Finding a practical example for a non-correlated subquery in the SELECT clause might be a bit challenging, as they are less common in that context.

  1. Subqueries in FROM / JOIN Clause:
    • Creating Virtual Tables: Using subqueries in the FROM clause allows you to create virtual tables that can be joined or used in other ways within the main query.
    • Join virtual table with main/outer query: Use when you need to treat the result of a subquery as a temporary table and join it with another table.
    • Dynamic Join Conditions: Subqueries in the JOIN clause are powerful when the join condition depends on the result of another query.

  • Example 1: Retrieve the list of employees along with the average sales per employee, using a subquery in the FROM clause.

    SELECT employees.employee_id, avg_sales.avg_amount
FROM employees
JOIN (SELECT employee_id, AVG(sales_amount) AS avg_amount FROM sales GROUP BY employee_id) AS avg_sales
ON employees.employee_id = avg_sales.employee_id;

Alternative query, assuming Sales data is inside the same employees table, which isn't the case in real time though.

SELECT DISTINCT
    employee_id,
    AVG(sales) OVER (PARTITION BY employee_id) AS Avg_sales
FROM employees;
  • Example 2: Retrieve a list of employees along with the count of employees who have a salary greater than the average salary in their department 
     SELECT e.employee_id, e.employee_name, e.salary, COUNT(*) AS emp_count
 FROM employees e
JOIN (
    SELECT  department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id ) AS dept_avg 
ON e.department_id = dept_avg.department_id
GROUP BY e.employee_id, e.employee_name, e.salary,
WHERE e.salary > dept_avg.avg_salary;

Note: avg_salary_count is the count of employees in each department who have a salary greater than the average salary in that department.

  • Example 3: Provide a report that lists all orders handled by employees who earn a salary greater than $50,000, including details about the department of each employee
     SELECT *
     FROM orders
     JOIN (SELECT employee_id, department_id FROM employees WHERE salary > 50000) AS high_salary_employees
     ON orders.employee_id = high_salary_employees.employee_id;
  1. Subqueries in WHERE Clause:
    • Dynamic Filtering: Subqueries in the WHERE clause are powerful for dynamic filtering based on conditions from other tables.
    • Use when you want to filter the result set based on a condition that involves data from another table.

Example 1: Retrieve list of employees whose salary is greater than the average salary across the entire company.

     SELECT employee_id,employee_name,salary
     FROM employees
     WHERE salary > (SELECT AVG(salary) FROM employees);
  1. Subqueries in HAVING Clause:

    • Filtering Aggregates Dynamically: Subqueries in the HAVING clause are powerful when you need to filter aggregated results dynamically.

    • Use when you want to filter the result set based on aggregated values (e.g., using GROUP BY) and apply a condition to the aggregated data.

    • Example: Retrieve departments with an average salary greater than a certain value.

     SELECT department_id, AVG(salary) as avg_salary
     FROM employees
     GROUP BY department_id
     HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

In summary, subqueries provide a way to perform complex operations and make queries more flexible by allowing the use of results from one query within another. The choice of where to use a subquery depends on the specific requirements of your SQL query and the logical structure you want to achieve.

Q2: Can you explain when to use correlated queries in SELECT, FROM, WHERE, and HAVING clauses, and when to use which one?

Ans: Correlated subqueries are a type of subquery where the inner query refers to columns from the outer query. These subqueries are evaluated once for each row processed by the outer query. Correlated subqueries can be used in different clauses of a SQL statement, and the decision on where to use them depends on the specific requirements of your query. Here's a breakdown:

  1. Correlated Subqueries in SELECT clause:
    • Use when you need to calculate a value for each row in the result set based on a condition involving columns from the outer query.
    • Commonly used for computing aggregated values or performing calculations that depend on the current row being processed.

Syntax:

     SELECT column1,
            (SELECT AVG(column2) FROM table2 WHERE table2.foreign_key = table1.primary_key) AS avg_value
     FROM table1;

Example 1: Generate a report that provides the salary details of each employee, the average salary across the company, and the total number of orders each employee has managed.

    SELECT employee_id, salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    (SELECT COUNT(*) FROM orders WHERE orders.employee_id = employees.employee_id) AS order_count
    
    FROM employees;

Alternative query using JOINS:

-- Compute the average salary for all employees
WITH AvgSalary AS (
    SELECT AVG(salary) AS avg_salary 
    FROM employees
),
-- Compute the order counts per employee
OrderCounts AS (
    SELECT employee_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY employee_id
)
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    a.avg_salary,
    o.order_count
FROM employees e
-- Cross join to ensure avg_salary is available to all rows
CROSS JOIN AvgSalary a
-- Left join so employees with no orders still appear, but with NULL order_count
LEFT JOIN OrderCounts o ON e.employee_id = o.employee_id;
  • Example2: Retrieve a list of employees along with the count of projects each employee is assigned to.
     SELECT employee_name, (SELECT COUNT(*) FROM projects WHERE projects.employee_id = employees.employee_id) AS project_count
     FROM employees;
  • Example3: Suppose you have tables for orders, products, and order details. You want to retrieve a list of orders along with the name and price of the most expensive product in each order. Here's a shorter version:
SELECT 
    o.order_id,
    o.order_date,
    (SELECT product_name 
     FROM order_details od 
     JOIN products p ON od.product_id = p.product_id
     WHERE od.order_id = o.order_id
     ORDER BY p.product_price DESC 
     LIMIT 1) AS most_expensive_product
FROM orders o;
  1. Correlated Subqueries in FROM/JOIN clause:
    • Correlated subqueries in FROM/JOIN clause aren't very common. Instead, you'll find subqueries in FROM/JOIN clause to form the dervied tables. Example as follows-

** Syntax:**

     SELECT t1.column1, t2.avg_column2
     FROM table1 t1
     JOIN (SELECT foreign_key, AVG(column2) AS avg_column2 FROM table2 GROUP BY foreign_key) t2
     ON t1.primary_key = t2.foreign_key;

Example 1: Retrieve the list of employees along with the average sales per employee, using a subquery in the FROM clause.

SELECT employees.employee_id, avg_sales.avg_amount
FROM employees
JOIN (SELECT employee_id, AVG(sales_amount) AS avg_amount FROM sales GROUP BY employee_id) AS avg_sales
ON employees.employee_id = avg_sales.employee_id;
  1. Correlated Subqueries in WHERE clause:
    • Use when you need to filter rows in the outer query based on conditions that involve columns from both the outer and inner queries.
    • Commonly used for filtering rows based on aggregate functions or comparisons with values from the correlated subquery.

Example 1:

     SELECT column1, column2
     FROM table1 t1
     WHERE column2 > (SELECT AVG(column2) FROM table1 t2 WHERE t1.column1 = t2.column1);

Example 2: Find Employees Who Earn More Than the Department Average.

SELECT e.employee_id, e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id
);
  1. Correlated Subqueries in HAVING clause:
    • Use when you want to filter groups based on conditions involving columns from both the outer and inner queries in a grouped query.
    • Similar to correlated subqueries in the WHERE clause but applied to grouped data.

Syntax:

     SELECT column1, COUNT(column2) AS count_column2
     FROM table1 t1
     GROUP BY column1
     HAVING COUNT(column2) > (SELECT AVG(column3) FROM table1 t2 WHERE t1.column1 = t2.column1);
  • Example: List departments with more than two employees, who're earning above the average salary in their department.
     SELECT department_id
     FROM employees
     GROUP BY department_id
     HAVING COUNT(employee_id) > 2
     AND SUM(CASE WHEN salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = employees.department_id) THEN 1 ELSE 0 END) > 2;

When using correlated subqueries, it's important to consider performance implications, as they can potentially be less efficient than non-correlated subqueries. Be mindful of indexes and optimize your queries accordingly.

Q3: In which part(SELECT, FROM, JOIN, WHERE, HAVING) of SQL query, correlated subqueries are used more frequently?

Ans: Correlated subqueries are more frequently used in the following parts of a SQL query:

  1. WHERE Clause:
    • Dynamic Filtering Based on Outer Query Values: Correlated subqueries are most commonly used in the WHERE clause when you need to filter the result set based on conditions that involve data from another table and depend on values from the outer query.
     SELECT customer_id, customer_name
     FROM customers c
     WHERE total_purchase > (SELECT AVG(purchase_amount) FROM purchases p WHERE c.customer_id = p.customer_id);
  1. HAVING Clause:
    • Filtering Aggregated Results Based on Outer Query Values: Correlated subqueries are often used in the HAVING clause when you need to filter aggregated results dynamically based on conditions from other tables and depend on values from the outer query.
     SELECT department_id, AVG(salary) AS avg_salary
     FROM employees e1
     GROUP BY department_id
     HAVING AVG(salary) > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

Other simpler ways to write above query:

Using a Common Table Expression (CTE)

WITH DepartmentAvg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT department_id, avg_salary
FROM DepartmentAvg
WHERE avg_salary > (
    SELECT AVG(salary)
    FROM employees
);

Using an Inline Subquery

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

While correlated subqueries can be used in other parts of SQL queries, they are particularly powerful and necessary in the WHERE and HAVING clauses where the conditions are dependent on values from the outer query. The ability to reference values from the outer query dynamically within the subquery is the key feature of correlated subqueries.

Q4: In which part(SELECT, FROM, JOIN, WHERE, HAVING) of SQL query, subqueries are used more frequently ?

Ans: The frequency of subquery usage in a SQL query depends on the specific requirements of the task, the complexity of the data manipulation, and the nature of the query. However, in general, subqueries are more frequently used in the following parts of a SQL query:

  1. WHERE Clause:

    • Subqueries are commonly used in the WHERE clause to filter the result set based on conditions that involve data from other tables or the result of another query. This is often necessary when you need to apply dynamic filtering criteria.

    Example:

   SELECT customer_id, customer_name
   FROM customers
   WHERE total_purchase > (SELECT AVG(purchase_amount) FROM purchases WHERE customers.customer_id = purchases.customer_id);

  1. SELECT Clause:

    • Subqueries are frequently used in the SELECT clause to calculate derived values, perform aggregations, or retrieve additional information that is not directly available in the main query.

    Example:

   SELECT
       employee_id,
       salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
   FROM employees;

  1. FROM Clause:

    • While less frequent than in the WHERE and SELECT clauses, subqueries in the FROM clause are used to create virtual tables or derived datasets that can be joined with other tables or used in other parts of the query.

    Example:

   SELECT employee_id, total_sales
   FROM employees
   JOIN (SELECT employee_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY employee_id) AS sales_summary
   ON employees.employee_id = sales_summary.employee_id;

In summary, while subqueries can be used in various parts of a SQL query, the WHERE, SELECT, and FROM clauses are the areas where they are more commonly employed. The specific use cases depend on the nature of the data manipulation and the logic required for a given task.

Clone this wiki locally