SQL Joins and Essential Queries: A Practical SQL Server Guide

Even if you use an ORM every day, SQL is the language your database actually speaks. Knowing it well lets you read the queries EF Core generates, write reports efficiently and debug performance problems. This guide covers the queries you'll write again and again, using SQL Server syntax and a small e-commerce schema.

The sample schema

CREATE TABLE Customers (
    Id INT PRIMARY KEY, Name NVARCHAR(100), City NVARCHAR(50)
);

CREATE TABLE Orders (
    Id INT PRIMARY KEY, CustomerId INT NULL REFERENCES Customers(Id),
    OrderDate DATE, Status NVARCHAR(20), Total DECIMAL(18,2)
);

CREATE TABLE OrderItems (
    Id INT PRIMARY KEY, OrderId INT REFERENCES Orders(Id),
    ProductName NVARCHAR(100), Quantity INT, UnitPrice DECIMAL(18,2)
);

The logical order of a SELECT

You write SELECT first, but SQL Server evaluates clauses in this order:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. OFFSET / FETCH

This explains common errors: you can't use a SELECT alias in WHERE (it doesn't exist yet), but you can in ORDER BY.

Joins

Joins combine rows from two tables based on a condition.

INNER JOIN — only matching rows

SELECT c.Name, o.Id AS OrderId, o.Total
FROM Customers c
INNER JOIN Orders o ON o.CustomerId = c.Id;

Customers without orders and orders without customers are excluded.

LEFT JOIN — everything from the left, matches from the right

SELECT c.Name, COUNT(o.Id) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
GROUP BY c.Name;

Customers with no orders appear with OrderCount = 0 (note COUNT(o.Id), not COUNT(*), which would count 1).

A classic use — finding rows with no match:

-- Customers who have never ordered
SELECT c.*
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE o.Id IS NULL;

Warning: Putting a condition on the right table in WHERE turns a LEFT JOIN into an INNER JOIN. Filter the right table in the ON clause instead: LEFT JOIN Orders o ON o.CustomerId = c.Id AND o.Status = 'Paid'

RIGHT and FULL OUTER JOIN

• RIGHT JOIN is a mirrored LEFT JOIN — usually rewritten as a LEFT JOIN for readability.
• FULL OUTER JOIN keeps unmatched rows from both sides — useful for reconciliation reports.

Multiple joins

SELECT c.Name, o.OrderDate, i.ProductName, i.Quantity * i.UnitPrice AS LineTotal
FROM Orders o
JOIN Customers  c ON c.Id = o.CustomerId
JOIN OrderItems i ON i.OrderId = o.Id
WHERE o.OrderDate >= '2026-01-01';

Aggregation: GROUP BY and HAVING

SELECT c.City,
       COUNT(DISTINCT c.Id) AS Customers,
       COUNT(o.Id)          AS Orders,
       SUM(o.Total)         AS Revenue,
       AVG(o.Total)         AS AvgOrder
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id AND o.Status = 'Paid'
GROUP BY c.City
HAVING SUM(o.Total) > 10000000
ORDER BY Revenue DESC;

• WHERE filters rows before grouping.
• HAVING filters groups after aggregation.
• Every non-aggregated column in SELECT must appear in GROUP BY.

Subqueries and EXISTS

-- Orders above the average order value
SELECT Id, Total
FROM Orders
WHERE Total > (SELECT AVG(Total) FROM Orders);

-- Customers who bought a specific product
SELECT c.Name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    JOIN OrderItems i ON i.OrderId = o.Id
    WHERE o.CustomerId = c.Id AND i.ProductName = N'Laptop'
);

EXISTS stops at the first match and handles NULLs safely. Prefer NOT EXISTS over NOT IN — NOT IN returns nothing if the subquery contains a single NULL.

Common Table Expressions (CTEs)

CTEs name an intermediate result, making complex queries readable:

WITH CustomerTotals AS (
    SELECT CustomerId, SUM(Total) AS Spent
    FROM Orders
    WHERE Status = 'Paid'
    GROUP BY CustomerId
)
SELECT c.Name, ct.Spent
FROM CustomerTotals ct
JOIN Customers c ON c.Id = ct.CustomerId
WHERE ct.Spent > 5000000
ORDER BY ct.Spent DESC;

Recursive CTEs walk hierarchies such as categories or org charts:

WITH CategoryTree AS (
    SELECT Id, Name, ParentId, 0 AS Depth FROM Categories WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.Name, c.ParentId, t.Depth + 1
    FROM Categories c JOIN CategoryTree t ON c.ParentId = t.Id
)
SELECT * FROM CategoryTree ORDER BY Depth, Name;

Window functions

Window functions calculate across related rows without collapsing them like GROUP BY does.

SELECT
    o.CustomerId,
    o.Id,
    o.Total,
    ROW_NUMBER() OVER (PARTITION BY o.CustomerId ORDER BY o.OrderDate DESC) AS RecentRank,
    SUM(o.Total) OVER (PARTITION BY o.CustomerId)                          AS CustomerTotal,
    SUM(o.Total) OVER (ORDER BY o.OrderDate ROWS UNBOUNDED PRECEDING)      AS RunningTotal,
    LAG(o.Total) OVER (PARTITION BY o.CustomerId ORDER BY o.OrderDate)     AS PreviousOrder
FROM Orders o;

The famous "latest order per customer" query:

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT * FROM Ranked WHERE rn = 1;

Paging

SELECT Id, OrderDate, Total
FROM Orders
ORDER BY OrderDate DESC, Id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;   -- page 3, 20 per page

OFFSET requires ORDER BY, and the order should be deterministic (add a unique column like Id), otherwise rows can appear on two pages. For deep pages on huge tables, keyset paging (WHERE Id < @lastId) is much faster.

Handling NULL correctly

• NULL = NULL is not true — use IS NULL / IS NOT NULL.
• Aggregates ignore NULLs: AVG(Discount) averages only non-null values.
• COALESCE(Discount, 0) replaces NULL with a default.

Data modification safely

BEGIN TRANSACTION;

UPDATE Orders
SET Status = 'Cancelled'
OUTPUT deleted.Status AS OldStatus, inserted.Id
WHERE Status = 'Pending' AND OrderDate < DATEADD(DAY, -30, GETDATE());

-- check the affected rows, then:
COMMIT;   -- or ROLLBACK;

Always run the WHERE as a SELECT first before an UPDATE or DELETE.

Summary

Know the logical order of query evaluation, choose the right join (and keep LEFT JOIN filters in ON), aggregate with GROUP BY/HAVING, prefer EXISTS for existence checks, use CTEs for readability and window functions for rankings and running totals, and page with a deterministic ORDER BY. These patterns cover most real-world reporting and data access needs.

FAQ

Is JOIN faster than a subquery?

Often the optimizer produces the same plan. Write the clearest query first, then compare execution plans if performance matters.

What's the difference between UNION and UNION ALL?

UNION removes duplicates (extra work); UNION ALL keeps everything and is faster. Use UNION ALL unless you need de-duplication.

How do I see the SQL EF Core generates?

Call .ToQueryString() on an IQueryable, or enable logging with options.LogTo(Console.WriteLine).