SQL Subqueries — Scalar, Correlated, Row, Table & Nested

-- 1. SCALAR SUBQUERY: returns exactly ONE value (one row, one column)
-- Used anywhere a single value is expected
SELECT
    Name,
    Salary,
    (SELECT AVG(Salary) FROM Employee) AS CompanyAvg,  -- In SELECT list
    Salary - (SELECT AVG(Salary) FROM Employee) AS DiffFromAvg
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);      -- In WHERE

-- 2. COLUMN SUBQUERY: returns ONE column (multiple rows)
-- Used with IN, ANY, ALL operators
SELECT Name FROM Employee
WHERE DeptID IN (
    SELECT DeptID FROM Department WHERE Budget > 1000000
);

-- ANY: true if condition is true for AT LEAST ONE value
SELECT Name FROM Employee
WHERE Salary > ANY (SELECT Salary FROM Employee WHERE DeptID = 2);
-- Returns employees earning more than the MINIMUM salary in Dept 2

-- ALL: true if condition is true for ALL values
SELECT Name FROM Employee
WHERE Salary > ALL (SELECT Salary FROM Employee WHERE DeptID = 2);
-- Returns employees earning more than the MAXIMUM salary in Dept 2

-- 3. ROW SUBQUERY: returns exactly ONE row
SELECT * FROM Employee
WHERE (DeptID, JobTitle) = (SELECT DeptID, JobTitle FROM Employee WHERE EmpID = 101);
-- Find all employees with same dept AND job title as EmpID 101

-- 4. TABLE SUBQUERY (Derived Table / Inline View): returns a full result set
-- Used in FROM clause — must be given an alias
SELECT dept_stats.DeptName, dept_stats.AvgSalary, dept_stats.HeadCount
FROM (
    SELECT d.DeptName, AVG(e.Salary) AS AvgSalary, COUNT(*) AS HeadCount
    FROM Employee e JOIN Department d ON e.DeptID = d.DeptID
    GROUP BY d.DeptName
) AS dept_stats                        -- Alias required for derived table
WHERE dept_stats.HeadCount > 5
ORDER BY dept_stats.AvgSalary DESC;

-- 5. CORRELATED SUBQUERY: references outer query — runs once per outer row
-- Find employees earning above their OWN department average
SELECT e.Name, e.Salary, e.DeptID
FROM Employee e
WHERE e.Salary > (
    SELECT AVG(Salary) FROM Employee
    WHERE DeptID = e.DeptID    -- References outer query: e.DeptID
);

-- Correlated with EXISTS: find departments that have at least one manager
SELECT d.DeptName FROM Department d
WHERE EXISTS (
    SELECT 1 FROM Employee e
    WHERE e.DeptID = d.DeptID AND e.JobTitle = 'Manager'
);

-- Deeply nested subquery (hard to read)
SELECT Name FROM Employee
WHERE DeptID IN (
    SELECT DeptID FROM Department
    WHERE ManagerID IN (
        SELECT EmpID FROM Employee
        WHERE Salary > (SELECT AVG(Salary) * 1.5 FROM Employee)
    )
);
-- Complex, hard to debug — good candidate for CTE refactoring

-- Same query refactored with CTEs (much more readable)
WITH
TopEarners AS (
    SELECT EmpID FROM Employee
    WHERE Salary > (SELECT AVG(Salary) * 1.5 FROM Employee)
),
WellPaidMgrDepts AS (
    SELECT DeptID FROM Department
    WHERE ManagerID IN (SELECT EmpID FROM TopEarners)
)
SELECT Name FROM Employee
WHERE DeptID IN (SELECT DeptID FROM WellPaidMgrDepts);

-- Subquery in UPDATE
UPDATE Employee
SET Salary = Salary * 1.10
WHERE DeptID = (
    SELECT DeptID FROM Department WHERE DeptName = 'Engineering'
);

-- Subquery in DELETE
DELETE FROM Orders
WHERE CustomerID IN (
    SELECT CustomerID FROM Customers WHERE Status = 'Blacklisted'
);

-- Subquery vs JOIN performance comparison:
-- These often produce the same result:
-- Using subquery:
SELECT Name FROM Employee
WHERE DeptID IN (SELECT DeptID FROM Department WHERE Location = 'Mumbai');
-- Using JOIN (often faster for large datasets):
SELECT DISTINCT e.Name FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
WHERE d.Location = 'Mumbai';