Joins & APPLY

Joins combine rows from two or more table sources. T-SQL supports the full ANSI join family plus two extras: CROSS APPLY and OUTER APPLY, which behave like a correlated join and are roughly equivalent to ANSI LATERAL.

ANSI Join Types

Join	Returns
INNER JOIN	Rows that match in both tables
LEFT JOIN	All left rows + matched right rows (NULLs where unmatched)
RIGHT JOIN	All right rows + matched left rows
FULL JOIN	All rows from both, NULL where no match
CROSS JOIN	Cartesian product (no ON)

-- Employees with their department names (only those who have a department)
SELECT e.employee_id, e.first_name + ' ' + e.last_name AS name, d.department_name
FROM   employees e
JOIN   departments d ON d.department_id = e.department_id;

-- Include employees who are NOT assigned to any department
SELECT e.employee_id, e.first_name, d.department_name
FROM   employees   e
LEFT JOIN departments d ON d.department_id = e.department_id;

-- Find departments with no employees and employees with no department
SELECT e.employee_id, d.department_id, d.department_name
FROM   employees   e
FULL JOIN departments d ON d.department_id = e.department_id
WHERE  e.employee_id IS NULL OR d.department_id IS NULL;

Joining More Than Two Tables

SELECT  e.first_name + ' ' + e.last_name AS employee,
        d.department_name,
        l.city,
        l.country_id
FROM    employees   e
JOIN    departments d ON d.department_id = e.department_id
JOIN    locations   l ON l.location_id  = d.location_id
JOIN    jobs        j ON j.job_id       = e.job_id
WHERE   j.job_title LIKE '%Manager%'
ORDER BY l.country_id, d.department_name;

Self-Joins

A table joined to itself — useful for hierarchical data:

-- Each employee with their manager's name
SELECT  e.employee_id,
        e.first_name + ' ' + e.last_name AS employee,
        m.first_name + ' ' + m.last_name AS manager
FROM    employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

CROSS APPLY and OUTER APPLY

APPLY invokes a table-valued expression once per row of the left input. The expression on the right can reference columns from the left — something a regular JOIN cannot do. Think of it as a correlated subquery that returns a rowset.

Operator	Behaviour
CROSS APPLY	Like INNER JOIN — drops rows where the right side returns no rows
OUTER APPLY	Like LEFT JOIN — keeps rows where the right side returns no rows (NULLs)

Example 1 — Top-N-Per-Group with APPLY

A classic problem: the 3 most recent orders for each customer:

SELECT  c.customer_id,
        c.customer_name,
        o.order_id,
        o.order_date,
        o.total
FROM    customers c
CROSS APPLY (
    SELECT TOP 3 order_id, order_date, total
    FROM   orders
    WHERE  customer_id = c.customer_id      -- correlation
    ORDER BY order_date DESC
) o
ORDER BY c.customer_id, o.order_date DESC;

A plain JOIN cannot reference c.customer_id inside a TOP-3 subquery in the join's source — APPLY is the natural fit.

Example 2 — Calling a Table-Valued Function

CREATE FUNCTION dbo.GetSalaryHistory (@emp_id INT)
RETURNS TABLE
AS RETURN (
    SELECT effective_date, salary
    FROM   salary_history
    WHERE  employee_id = @emp_id
);
GO

-- Apply the TVF for every employee
SELECT  e.employee_id, e.first_name, h.effective_date, h.salary
FROM    employees e
CROSS APPLY dbo.GetSalaryHistory(e.employee_id) h
ORDER BY e.employee_id, h.effective_date;

Example 3 — OUTER APPLY Keeping Unmatched Rows

-- Every employee, plus their most recent order (NULL if they have none)
SELECT  e.employee_id, e.first_name, o.order_id, o.order_date
FROM    employees e
OUTER APPLY (
    SELECT TOP 1 order_id, order_date
    FROM   orders
    WHERE  sales_rep_id = e.employee_id
    ORDER BY order_date DESC
) o;

APPLY vs LATERAL

Database	Equivalent
SQL Server / Sybase	CROSS APPLY / OUTER APPLY
PostgreSQL, Oracle 12c+, ANSI SQL	CROSS JOIN LATERAL / LEFT JOIN LATERAL

Functionally identical: both give a row-by-row, parameterised join that can reference outer columns.

ON vs WHERE in Outer Joins

In a LEFT JOIN, predicates in ON qualify the match, while predicates in WHERE filter after the join — and can accidentally turn an outer join into an inner join:

-- Wrong: this filters out unmatched rows that have d.department_name IS NULL
SELECT e.employee_id, d.department_name
FROM   employees   e
LEFT JOIN departments d ON d.department_id = e.department_id
WHERE  d.department_name = 'IT';

-- Right: predicate stays in ON
SELECT e.employee_id, d.department_name
FROM   employees   e
LEFT JOIN departments d ON d.department_id = e.department_id
                       AND d.department_name = 'IT';

Best Practices

• Always use ANSI join syntax (INNER JOIN ... ON); avoid the deprecated *= and =* operators (removed in SQL Server 2012).
• Reach for APPLY when you need TOP-N-per-group, calling a TVF per row, or splitting a string per row with STRING_SPLIT.
• In LEFT JOINs, put filters on the right table inside the ON clause; put filters on the left table in WHERE.

Summary

• T-SQL supports INNER, LEFT, RIGHT, FULL, and CROSS joins, plus CROSS APPLY and OUTER APPLY.
• APPLY is a correlated, row-by-row join — equivalent to ANSI LATERAL.
• Use CROSS APPLY for top-N-per-group, calling TVFs, or string splitting.
• Move filters between ON and WHERE carefully — the choice changes the result of outer joins.