A SQL JOIN combines records from two tables.
A JOIN locates related column values in the two tables.
A query can contain zero, one, or multiple JOIN operations.
INNER JOIN is the same as JOIN; the keyword INNER is optional.
Different types of JOINs
(INNER) JOIN: Select records that have matching values in both tables.
LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
FULL (OUTER) JOIN: Selects all records that match either left or right table records.
All INNER and OUTER keywords are optional.
Details about the differences between these JOINs are available in subsequent tutorial pages.
SQL JOIN Examples
Problem: List all orders with customer information
SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
FROM [Order] JOIN Customer
ON [Order].CustomerId = Customer.Id
In this example using table aliases for [Order] and Customer might have been useful.
Problem: List all orders with product names, quantities, and prices
SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date,
P.ProductName, I.Quantity, I.UnitPrice
FROM [Order] O
JOIN OrderItem I ON O.Id = I.OrderId
JOIN Product P ON P.Id = I.ProductId
ORDER BY O.OrderNumber
This query performs two JOIN operations with 3 tables.
The O, I, and P are table aliases. Date is a column alias.