Subqueries & Correlated Subqueries

Scalar subqueries, subqueries in FROM/WHERE, IN vs EXISTS

« SQL Joins Explained« Previous Window Functions: ROW_NUMBER, RANK & OVER »Next »

A subquery is a nested SQL query embedded inside another SELECT, INSERT, UPDATE, or DELETE statement, providing dynamic data to the outer query.

Standard vs Correlated Subqueries

Scalar Subquery: Returns exactly 1 row and 1 column, used in comparisons (WHERE salary > (SELECT AVG(salary) FROM employees)).

Subquery in FROM: Functions as a temporary inline derived table.

IN vs EXISTS: IN checks if a value is contained in a list; EXISTS tests whether the subquery returns any rows at all (terminating immediately upon first match for high speed).

Correlated Subquery: References columns from the outer query, executing once per outer row.

Subqueries with EXISTS and Aggregations

Try It Yourself »

-- Find employees earning more than their department's average
SELECT e.EmployeeName, e.DepartmentID, e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(d.Salary) 
    FROM Employees d 
    WHERE d.DepartmentID = e.DepartmentID
);

-- Find customers who have placed at least 1 high-value order
SELECT c.CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1 
    FROM Orders o 
    WHERE o.CustomerID = c.CustomerID 
      AND o.TotalAmount > 1000
);

Google AdSense Slot (inContentBanner)

Set your Publisher ID in src/config/ads.ts to display live ads.

Test Yourself With Exercise

+15 XP

What is a "correlated subquery" in SQL?

« Previous: SQL Joins Explained Next: Window Functions: ROW_NUMBER, RANK & OVER »