What are subqueries in SQL? What is the difference between correlated and non-correlated subqueries?

Q: What are subqueries in SQL? What is the difference between correlated and non-correlated subqueries?

Subqueries in SQL A subquery (inner query / nested query) is a SELECT statement inside another SQL statement. Non-Correlated Subquery — Runs Once The inner query executes once independently. The result is used by the outer query. sql -- Find employees earning above the average salary SELECT name, salary FROM employees WHERE salary (SELECT AVG(salary) FROM employees); -- Inner query: AVG(salary) = 97500 (runs once) -- Outer query: finds everyone 97500 -- Find employees in the QA department SELECT name FROM employees WHERE deptid = (SELECT id FROM departments WHERE deptname = 'QA'); -- Find the

Answer

Subqueries in SQL

A subquery (inner query / nested query) is a SELECT statement inside another SQL statement.

Non-Correlated Subquery — Runs Once

The inner query executes once independently. The result is used by the outer query.

SQL

-- Find employees earning above the average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Inner query: AVG(salary) = 97500 (runs once)
-- Outer query: finds everyone > 97500

-- Find employees in the QA department
SELECT name FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'QA');

-- Find the highest paid employee
SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Subquery in FROM clause (derived table)
SELECT dept_summary.department, dept_summary.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summary
WHERE dept_summary.avg_salary > 100000;

Correlated Subquery — Runs Per Outer Row

The inner query references columns from the outer query — it runs once for each row in the outer query.

SQL

-- Find employees who earn more than the average of their own department
SELECT e1.name, e1.salary, e1.department
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department   -- ← references outer query's e1
);
-- For each employee (e1), the inner query computes avg for THEIR department

-- Find the highest earner in each department (correlated)
SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

EXISTS / NOT EXISTS (Correlated)

SQL

-- Find customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Find customers with NO orders (common QA check)
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- Equivalent but often faster than:
-- WHERE c.id NOT IN (SELECT customer_id FROM orders)

Subquery vs JOIN Performance

SQL

-- Subquery (readable but sometimes slower)
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'Bangalore');

-- JOIN equivalent (often faster for large datasets)
SELECT e.name FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.location = 'Bangalore';

IN vs EXISTS

SQL

-- IN: works well when subquery returns small result set
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE active = 1);

-- EXISTS: better when subquery could match many rows
-- Stops at first match — more efficient
SELECT name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id AND d.active = 1);