SQL Joins: All Types and Join Optimization

You will learn:

INNER / LEFT / RIGHT / FULL OUTER / CROSS joins
SELF join, multi-table joins, NON-EQUI join
Semi join (EXISTS) and Anti join (NOT EXISTS / LEFT JOIN ... IS NULL)
Filter in ON vs WHERE (very common interview question)
Join optimization: indexes on join keys, SARGable predicates, STATISTICS IO, execution plans, join algorithms (Nested Loop / Merge / Hash)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables: SalaryGrades (for non-equi join) and Shifts (for cross join)

DROP TABLE IF EXISTS dbo.SalaryGrades;
DROP TABLE IF EXISTS dbo.Shifts;

CREATE TABLE dbo.SalaryGrades
(
    Grade     CHAR(1)       PRIMARY KEY,
    MinSalary DECIMAL(10,2) NOT NULL,
    MaxSalary DECIMAL(10,2) NOT NULL
);

INSERT INTO dbo.SalaryGrades (Grade, MinSalary, MaxSalary) VALUES
('E',      0,  49999.99),
('D',  50000,  74999.99),
('C',  75000,  99999.99),
('B', 100000, 149999.99),
('A', 150000, 999999.99);

CREATE TABLE dbo.Shifts
(
    ShiftName VARCHAR(20) PRIMARY KEY,
    StartTime TIME NOT NULL,
    EndTime   TIME NOT NULL
);

INSERT INTO dbo.Shifts (ShiftName, StartTime, EndTime) VALUES
('Morning', '06:00', '14:00'),
('Evening', '14:00', '22:00'),
('Night',   '22:00', '06:00');
GO

Messages

(5 rows affected)
(3 rows affected)

Part A: JOIN Types

INNER JOIN: only matching rows (Intern with NULL department disappears)

SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM dbo.Employees AS e
INNER JOIN dbo.Departments AS d ON d.DepartmentID = e.DepartmentID;

Output

EmployeeID	FirstName	DepartmentName
1	Rajesh	Executive
2	Anita	Sales
3	Vikram	IT
4	Meena	HR
5	Suresh	Finance
6	Kavya	Marketing
7	Arjun	Sales
8	Pooja	Sales
9	Rahul	Sales
10	Sneha	Sales
11	Kiran	IT
12	Divya	IT
13	Amit	IT
14	Neha	IT
15	Sanjay	IT

... showing 15 of 24 rows

LEFT JOIN: all employees, department may be NULL

SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM dbo.Employees AS e
LEFT JOIN dbo.Departments AS d ON d.DepartmentID = e.DepartmentID;

Output

EmployeeID	FirstName	DepartmentName
1	Rajesh	Executive
2	Anita	Sales
3	Vikram	IT
4	Meena	HR
5	Suresh	Finance
6	Kavya	Marketing
7	Arjun	Sales
8	Pooja	Sales
9	Rahul	Sales
10	Sneha	Sales
11	Kiran	IT
12	Divya	IT
13	Amit	IT
14	Neha	IT
15	Sanjay	IT

... showing 15 of 25 rows

RIGHT JOIN: all departments, even Research with no employees

SELECT d.DepartmentName, e.FirstName
FROM dbo.Employees AS e
RIGHT JOIN dbo.Departments AS d ON d.DepartmentID = e.DepartmentID;

Output

DepartmentName	FirstName
Executive	Rajesh
Sales	Anita
Sales	Arjun
Sales	Pooja
Sales	Rahul
Sales	Sneha
Sales	Ritu
Marketing	Kavya
Marketing	Rohit
Marketing	Swati
IT	Vikram
IT	Kiran
IT	Divya
IT	Amit
IT	Neha

... showing 15 of 25 rows

FULL OUTER JOIN: everything from both sides

(shows the Intern with no dept AND Research with no employees)

SELECT e.FirstName, d.DepartmentName
FROM dbo.Employees AS e
FULL OUTER JOIN dbo.Departments AS d ON d.DepartmentID = e.DepartmentID
WHERE e.EmployeeID IS NULL OR d.DepartmentID IS NULL;

Output

FirstName	DepartmentName
Naveen	NULL
NULL	Research

CROSS JOIN: every combination (8 depts x 3 shifts = 24 rows)

SELECT d.DepartmentName, s.ShiftName
FROM dbo.Departments AS d
CROSS JOIN dbo.Shifts AS s
ORDER BY d.DepartmentName, s.StartTime;

Output

DepartmentName	ShiftName
Executive	Morning
Executive	Evening
Executive	Night
Finance	Morning
Finance	Evening
Finance	Night
HR	Morning
HR	Evening
HR	Night
IT	Morning
IT	Evening
IT	Night
Marketing	Morning
Marketing	Evening
Marketing	Night

... showing 15 of 24 rows

SELF JOIN: employee with his/her manager name

SELECT e.EmployeeID,
       e.FirstName + ' ' + e.LastName AS Employee,
       m.FirstName + ' ' + m.LastName AS Manager
FROM dbo.Employees AS e
LEFT JOIN dbo.Employees AS m ON m.EmployeeID = e.ManagerID;

Output

EmployeeID	Employee	Manager
1	Rajesh Kumar	NULL
2	Anita Sharma	Rajesh Kumar
3	Vikram Reddy	Rajesh Kumar
4	Meena Iyer	Rajesh Kumar
5	Suresh Patel	Rajesh Kumar
6	Kavya Nair	Anita Sharma
7	Arjun Mehta	Anita Sharma
8	Pooja Singh	Arjun Mehta
9	Rahul Verma	Arjun Mehta
10	Sneha Gupta	Arjun Mehta
11	Kiran Rao	Vikram Reddy
12	Divya Menon	Kiran Rao
13	Amit Joshi	Kiran Rao
14	Neha Kapoor	Vikram Reddy
15	Sanjay Das	Vikram Reddy

... showing 15 of 25 rows

SELF JOIN: employees who earn More than their manager (should be none)

and employees who joined BEFORE their manager

SELECT e.FirstName AS Employee, e.HireDate, m.FirstName AS Manager, m.HireDate AS ManagerHireDate
FROM dbo.Employees AS e
JOIN dbo.Employees AS m ON m.EmployeeID = e.ManagerID
WHERE e.HireDate < m.HireDate;

Output

Employee	HireDate	Manager	ManagerHireDate

(0 rows)

Non-equi JOIN: join using BETWEEN instead of =

SELECT e.FirstName, e.Salary, g.Grade
FROM dbo.Employees AS e
JOIN dbo.SalaryGrades AS g ON e.Salary BETWEEN g.MinSalary AND g.MaxSalary
ORDER BY e.Salary DESC;

Output

FirstName	Salary	Grade
Rajesh	250000.00	A
Vikram	190000.00	A
Anita	180000.00	A
Suresh	130000.00	B
Meena	120000.00	B
Kavya	110000.00	B
Deepak	105000.00	B
Arjun	100000.00	B
Kiran	95000.00	C
Sanjay	90000.00	C
Neha	75000.00	C
Divya	70000.00	D
Geeta	70000.00	D
Amit	68000.00	D
Lakshmi	62000.00	D

... showing 15 of 25 rows

Multi-table JOIN: full order details (5 tables)

SELECT o.OrderID, o.OrderDate, c.CustomerName,
       e.FirstName AS SalesPerson, p.ProductName, cat.CategoryName,
       oi.Quantity, oi.UnitPrice, oi.LineTotal
FROM dbo.Orders      AS o
JOIN dbo.Customers   AS c   ON c.CustomerID  = o.CustomerID
JOIN dbo.Employees   AS e   ON e.EmployeeID  = o.EmployeeID
JOIN dbo.OrderItems  AS oi  ON oi.OrderID    = o.OrderID
JOIN dbo.Products    AS p   ON p.ProductID   = oi.ProductID
JOIN dbo.Categories  AS cat ON cat.CategoryID = p.CategoryID
ORDER BY o.OrderID;

Output

OrderID	OrderDate	CustomerName	SalesPerson	ProductName	CategoryName	Quantity	UnitPrice	LineTotal
1	2024-01-09	Ravi Traders	Rahul	Lenovo ThinkCentre	Desktops	3	55000.00	165000.00
1	2024-01-09	Ravi Traders	Rahul	MacBook Air M3	Laptops	1	120000.00	120000.00
2	2024-01-17	Deccan Systems	Ritu	Desk Organizer	Stationery	2	450.00	810.00
3	2024-01-26	Deccan Systems	Sneha	Dell Inspiron 15	Laptops	2	65000.00	130000.00
3	2024-01-26	Deccan Systems	Sneha	Samsung Galaxy A55	Mobiles	1	25000.00	25000.00
3	2024-01-26	Deccan Systems	Sneha	Basic Office Chair	Office Chairs	2	8500.00	17000.00
4	2024-01-29	Silicon Valley Pvt Ltd	Arjun	HP Pavilion 14	Laptops	3	85000.00	255000.00
4	2024-01-29	Silicon Valley Pvt Ltd	Arjun	Standing Desk	Desks	2	22000.00	39600.00
4	2024-01-29	Silicon Valley Pvt Ltd	Arjun	Lenovo ThinkCentre	Desktops	2	55000.00	99000.00
5	2024-02-06	Lakshmi Stores	Arjun	Redmi Note 13	Mobiles	1	15000.00	14250.00
6	2024-02-14	Marina Digital	Rahul	iPhone 15	Mobiles	1	70000.00	63000.00
6	2024-02-14	Marina Digital	Rahul	Wireless Mouse	Accessories	2	1200.00	2400.00
6	2024-02-14	Marina Digital	Rahul	MacBook Air M3	Laptops	3	120000.00	342000.00
7	2024-02-20	Mumbai Mart	Ritu	HP Pavilion 14	Laptops	2	85000.00	161500.00
7	2024-02-20	Mumbai Mart	Ritu	Redmi Note 13	Mobiles	2	15000.00	30000.00

... showing 15 of 202 rows

Part B: SEMI JOIN and ANTI JOIN

SEMI JOIN: customers who placed at least one order (no duplicates)

SELECT c.CustomerID, c.CustomerName
FROM dbo.Customers AS c
WHERE EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.CustomerID = c.CustomerID);

Output

CustomerID	CustomerName
1	Ravi Traders
2	Infosoft Solutions
3	Lakshmi Stores
4	Mumbai Mart
5	Pune Tech Park
6	Capital Electronics
7	Bengal Book House
8	Deccan Systems
9	Gujarat Office Supply
10	Pink City Furnishers
11	Kerala Kart
12	Awadh Enterprises
13	Silicon Valley Pvt Ltd
14	Gateway Retail
15	Marina Digital

... showing 15 of 17 rows

ANTI JOIN using NOT EXISTS: customers who never ordered

SELECT c.CustomerID, c.CustomerName
FROM dbo.Customers AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.CustomerID = c.CustomerID);

Output

CustomerID	CustomerName
18	Nizam Bakers
19	Coastal Exports
20	Himalaya Ventures

ANTI JOIN using LEFT JOIN ... IS NULL: products never ordered

SELECT p.ProductID, p.ProductName
FROM dbo.Products AS p
LEFT JOIN dbo.OrderItems AS oi ON oi.ProductID = p.ProductID
WHERE oi.OrderItemID IS NULL;

Output

ProductID	ProductName
19	Old Model Tablet
20	Laser Printer

Watch out! Joining 1-to-many creates duplicates.

Wrong : this counts the customer once per order

SELECT c.CustomerName, COUNT(*) AS RowsAfterJoin
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerName;

Output

CustomerName	RowsAfterJoin
Awadh Enterprises	5
Bengal Book House	2
Capital Electronics	2
Deccan Systems	3
Gateway Retail	1
Gujarat Office Supply	3
Infosoft Solutions	6
Kerala Kart	3
Lakshmi Stores	13
Marina Digital	6
Mumbai Mart	4
Northern Distributors	2
Pink City Furnishers	4
Pune Tech Park	8
Ravi Traders	11

... showing 15 of 17 rows

Part C: Filter in "ON" vs "WHERE" with LEFT JOIN

Filter in ON: keeps All customers, only joins 2025 orders

SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
       ON o.CustomerID = c.CustomerID
      AND o.OrderDate >= '2025-01-01'
ORDER BY c.CustomerID;

Output

CustomerName	OrderID	OrderDate
Ravi Traders	64	2025-03-08
Ravi Traders	71	2025-04-25
Ravi Traders	73	2025-05-09
Ravi Traders	75	2025-05-21
Infosoft Solutions	57	2025-01-18
Infosoft Solutions	58	2025-01-26
Lakshmi Stores	67	2025-03-28
Lakshmi Stores	70	2025-04-17
Lakshmi Stores	76	2025-05-27
Lakshmi Stores	79	2025-06-15
Mumbai Mart	62	2025-02-22
Mumbai Mart	68	2025-04-02
Mumbai Mart	69	2025-04-12
Pune Tech Park	66	2025-03-22
Pune Tech Park	74	2025-05-14

... showing 15 of 35 rows

Filter in WHERE: turns the LEFT JOIN into an INNER JOIN

(customers without 2025 orders vanish)

SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2025-01-01'
ORDER BY c.CustomerID;

Output

CustomerName	OrderID	OrderDate
Ravi Traders	64	2025-03-08
Ravi Traders	71	2025-04-25
Ravi Traders	75	2025-05-21
Ravi Traders	73	2025-05-09
Infosoft Solutions	57	2025-01-18
Infosoft Solutions	58	2025-01-26
Lakshmi Stores	70	2025-04-17
Lakshmi Stores	67	2025-03-28
Lakshmi Stores	79	2025-06-15
Lakshmi Stores	76	2025-05-27
Mumbai Mart	68	2025-04-02
Mumbai Mart	69	2025-04-12
Mumbai Mart	62	2025-02-22
Pune Tech Park	66	2025-03-22
Pune Tech Park	77	2025-06-03

... showing 15 of 26 rows

Report: every department with employee count and total salary (including 0)

SELECT d.DepartmentName,
       COUNT(e.EmployeeID)      AS EmployeeCount,   -- COUNT(column) ignores NULL
       ISNULL(SUM(e.Salary), 0) AS TotalSalary
FROM dbo.Departments AS d
LEFT JOIN dbo.Employees AS e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName
ORDER BY TotalSalary DESC;

Output

DepartmentName	EmployeeCount	TotalSalary
IT	7	658000.00
Sales	6	500000.00
Finance	3	252000.00
Executive	1	250000.00
Marketing	3	202000.00
HR	2	165000.00
Operations	2	145000.00
Research	0	0.00

Part D: JOIN Optimization

Tip: In SSMS press Ctrl+M (Include Actual Execution Plan) before running.

-- Start clean (so this file can be run many times)
DROP INDEX IF EXISTS IX_Orders_CustomerID    ON dbo.Orders;
DROP INDEX IF EXISTS IX_Orders_EmployeeID    ON dbo.Orders;
DROP INDEX IF EXISTS IX_OrderItems_OrderID   ON dbo.OrderItems;
DROP INDEX IF EXISTS IX_OrderItems_ProductID ON dbo.OrderItems;
DROP INDEX IF EXISTS IX_Employees_DeptID     ON dbo.Employees;
GO

SET STATISTICS IO ON;    -- shows logical reads in the "Messages" tab
SET STATISTICS TIME ON;  -- shows CPU and elapsed time
GO

Foreign keys are NOT indexed automatically in SQL Server.

Without an index on Orders.CustomerID the join must scan Orders.

SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE c.City = 'Hyderabad';
GO

Messages

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 4 ms.

Output

CustomerName	OrderID	OrderDate
Ravi Traders	1	2024-01-09
Deccan Systems	2	2024-01-17
Deccan Systems	3	2024-01-26
Ravi Traders	10	2024-03-12
Ravi Traders	13	2024-04-02
Ravi Traders	26	2024-06-28
Deccan Systems	28	2024-07-09
Ravi Traders	39	2024-09-23
Ravi Traders	46	2024-11-06
Ravi Traders	52	2024-12-18
Ravi Traders	64	2025-03-08
Ravi Traders	71	2025-04-25
Ravi Traders	73	2025-05-09
Ravi Traders	75	2025-05-21

Messages

Table 'Customers'. Scan count 0, logical reads 160, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Create indexes on the join (foreign key) columns

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID     ON dbo.Orders(CustomerID) INCLUDE (OrderDate, Status);
CREATE NONCLUSTERED INDEX IX_Orders_EmployeeID     ON dbo.Orders(EmployeeID);
CREATE NONCLUSTERED INDEX IX_OrderItems_OrderID    ON dbo.OrderItems(OrderID)   INCLUDE (ProductID, Quantity, UnitPrice);
CREATE NONCLUSTERED INDEX IX_OrderItems_ProductID  ON dbo.OrderItems(ProductID);
CREATE NONCLUSTERED INDEX IX_Employees_DeptID      ON dbo.Employees(DepartmentID);
GO

Messages

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 2 ms.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'OrderItems'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'OrderItems'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Employees'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

Same query again: compare logical reads / plan (Index Seek instead of Scan)

SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE c.City = 'Hyderabad';
GO

Messages

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

Output

CustomerName	OrderID	OrderDate
Ravi Traders	1	2024-01-09
Ravi Traders	10	2024-03-12
Ravi Traders	13	2024-04-02
Ravi Traders	26	2024-06-28
Ravi Traders	39	2024-09-23
Ravi Traders	46	2024-11-06
Ravi Traders	52	2024-12-18
Ravi Traders	64	2025-03-08
Ravi Traders	71	2025-04-25
Ravi Traders	73	2025-05-09
Ravi Traders	75	2025-05-21
Deccan Systems	2	2024-01-17
Deccan Systems	3	2024-01-26
Deccan Systems	28	2024-07-09

Messages

Table 'Orders'. Scan count 3, logical reads 6, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SARGable vs Non-SARGable predicates

Non-SARGable : function on the column -> index cannot be seeked

SELECT OrderID, OrderDate FROM dbo.Orders WHERE YEAR(OrderDate) = 2025;
--     SARGable : compare the raw column with a range -> index seek possible
SELECT OrderID, OrderDate FROM dbo.Orders WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';
GO

Messages

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

Output

OrderID	OrderDate
64	2025-03-08
71	2025-04-25
73	2025-05-09
75	2025-05-21
57	2025-01-18
58	2025-01-26
67	2025-03-28
70	2025-04-17
76	2025-05-27
79	2025-06-15
62	2025-02-22
68	2025-04-02
69	2025-04-12
66	2025-03-22
74	2025-05-14

... showing 15 of 26 rows

Messages

Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Output

OrderID	OrderDate
64	2025-03-08
71	2025-04-25
73	2025-05-09
75	2025-05-21
57	2025-01-18
58	2025-01-26
67	2025-03-28
70	2025-04-17
76	2025-05-27
79	2025-06-15
62	2025-02-22
68	2025-04-02
69	2025-04-12
66	2025-03-22
74	2025-05-14

... showing 15 of 26 rows

Messages

Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Pre-aggregate Before joining (less rows to join = faster)

Slower pattern : join everything, then group

SELECT c.CustomerName, SUM(oi.LineTotal) AS Revenue
FROM dbo.Customers AS c
JOIN dbo.Orders     AS o  ON o.CustomerID = c.CustomerID
JOIN dbo.OrderItems AS oi ON oi.OrderID   = o.OrderID
GROUP BY c.CustomerName;

--     Better pattern on big tables : aggregate first, then join small result
WITH OrderTotals AS
(
    SELECT o.CustomerID, SUM(oi.LineTotal) AS Revenue
    FROM dbo.Orders AS o
    JOIN dbo.OrderItems AS oi ON oi.OrderID = o.OrderID
    GROUP BY o.CustomerID
)
SELECT c.CustomerName, t.Revenue
FROM OrderTotals AS t
JOIN dbo.Customers AS c ON c.CustomerID = t.CustomerID;
GO

Messages

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 7 ms.

Output

CustomerName	Revenue
Awadh Enterprises	673885.00
Bengal Book House	259075.00
Capital Electronics	115100.00
Deccan Systems	220310.00
Gateway Retail	476850.00
Gujarat Office Supply	148800.00
Infosoft Solutions	1163225.00
Kerala Kart	644925.00
Lakshmi Stores	1925592.50
Marina Digital	1326609.00
Mumbai Mart	365735.00
Northern Distributors	275200.00
Pink City Furnishers	536100.00
Pune Tech Park	1649975.00
Ravi Traders	1527910.00

... showing 15 of 17 rows

Messages

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrderItems'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Output

CustomerName	Revenue
Ravi Traders	1527910.00
Infosoft Solutions	1163225.00
Lakshmi Stores	1925592.50
Mumbai Mart	365735.00
Pune Tech Park	1649975.00
Capital Electronics	115100.00
Bengal Book House	259075.00
Deccan Systems	220310.00
Gujarat Office Supply	148800.00
Pink City Furnishers	536100.00
Kerala Kart	644925.00
Awadh Enterprises	673885.00
Silicon Valley Pvt Ltd	574857.00
Gateway Retail	476850.00
Marina Digital	1326609.00

... showing 15 of 17 rows

Messages

Table 'Customers'. Scan count 0, logical reads 34, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Orders'. Scan count 0, logical reads 404, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrderItems'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Join algorithms: SQL Server chooses one automatically.

You can FORCE one for learning (do NOT do this in production without reason).

SELECT c.CustomerName, o.OrderID FROM dbo.Customers c INNER LOOP  JOIN dbo.Orders o ON o.CustomerID = c.CustomerID;  -- Nested Loops : small outer input + index on inner
SELECT c.CustomerName, o.OrderID FROM dbo.Customers c INNER MERGE JOIN dbo.Orders o ON o.CustomerID = c.CustomerID;  -- Merge Join   : both inputs sorted on join key
SELECT c.CustomerName, o.OrderID FROM dbo.Customers c INNER HASH  JOIN dbo.Orders o ON o.CustomerID = c.CustomerID;  -- Hash Match   : large unsorted inputs
GO

Messages

Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
Warning: The join order has been enforced because a local join hint is used.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.

Output

CustomerName	OrderID
Ravi Traders	1
Ravi Traders	10
Ravi Traders	13
Ravi Traders	26
Ravi Traders	39
Ravi Traders	46
Ravi Traders	52
Ravi Traders	64
Ravi Traders	71
Ravi Traders	73
Ravi Traders	75
Infosoft Solutions	14
Infosoft Solutions	20
Infosoft Solutions	21
Infosoft Solutions	35

... showing 15 of 80 rows

Messages

Table 'Orders'. Scan count 20, logical reads 40, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Output

CustomerName	OrderID
Ravi Traders	1
Ravi Traders	10
Ravi Traders	13
Ravi Traders	26
Ravi Traders	39
Ravi Traders	46
Ravi Traders	52
Ravi Traders	64
Ravi Traders	71
Ravi Traders	73
Ravi Traders	75
Infosoft Solutions	14
Infosoft Solutions	20
Infosoft Solutions	21
Infosoft Solutions	35

... showing 15 of 80 rows

Messages

Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Output

CustomerName	OrderID
Ravi Traders	1
Ravi Traders	10
Ravi Traders	13
Ravi Traders	26
Ravi Traders	39
Ravi Traders	46
Ravi Traders	52
Ravi Traders	64
Ravi Traders	71
Ravi Traders	73
Ravi Traders	75
Infosoft Solutions	14
Infosoft Solutions	20
Infosoft Solutions	21
Infosoft Solutions	35

... showing 15 of 80 rows

Messages

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Data types must match on join columns.

Joining INT to VARCHAR causes CONVERT_IMPLICIT -> scans instead of seeks.
Always keep FK column type = PK column type.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

Messages

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Practice Questions

List each manager with the number of direct reports.
Show customers and the total number of orders, including customers with 0 orders.
Find categories that have no products (hint: anti join).
Find pairs of employees who work in the same city but different departments.
For each order show the customer city and ship city, and flag if they differ.