Open navigation menu

Subqueries in WHERE and SELECT

Demonstrates correlated and non-correlated subqueries in WHERE clauses and SELECT columns to filter and enrich data based on related query results.

SqlData Fetching

Code

snippet
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.salary,
    e.department_id,
    (SELECT AVG(salary)
     FROM employees
     WHERE department_id = e.department_id) AS dept_avg_salary,
    (SELECT COUNT(*)
     FROM orders o
     WHERE o.sales_rep_id = e.employee_id) AS orders_closed
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees
)
  AND e.department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
)
ORDER BY e.salary DESC;

Related Examples