SQL JOIN
How do I get data from multiple tables?
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.

Four different types of JOINs
- (INNER) JOIN: Select records that have matching values in both tables.
- FULL (OUTER) JOIN: Selects all records that match either left or right table records.
- 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.

Note: All INNER and OUTER keywords are optional.
Details about the different JOINs are available in subsequent tutorial pages.
The SQL JOIN syntax
The general syntax is
SELECT column-names FROM table-name1 JOIN table-name2 ON column-name1 = column-name2 WHERE condition
The general syntax with INNER is:
SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition
Note: The INNER keyword is optional: it is the default as well as the most commmonly used JOIN operation.
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
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.
Result: 830 records.
OrderNumber | TotalAmount | FirstName | LastName | City | Country |
---|---|---|---|---|---|
542378 | 440.00 | Paul | Henriot | Reims | France |
542379 | 1863.40 | Karin | Josephs | Münster | Germany |
542380 | 1813.00 | Mario | Pontes | Rio de Janeiro | Brazil |
542381 | 670.80 | Mary | Saveley | Lyon | France |
542382 | 3730.00 | Pascale | Cartrain | Charleroi | Belgium |
542383 | 1444.80 | Mario | Pontes | Rio de Janeiro | Brazil |
542384 | 625.20 | Yang | Wang | Bern | Switzerland |
![]() |
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
ORDERITEM |
---|
Id |
OrderId |
ProductId |
UnitPrice |
Quantity |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
Problem: List all orders
with product names,
quantities, and prices
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.
The O, I, and P are table Aliases. Date is a column Alias.
Result: 2155 records
OrderNumber | Date | ProductName | Quantity | UnitPrice |
---|---|---|---|---|
542378 | 7/4/2012 12:00:00 AM | Queso Cabrales | 12 | 14.00 |
542378 | 7/4/2012 12:00:00 AM | Singaporean Hokkien Fried Mee | 10 | 9.80 |
542378 | 7/4/2012 12:00:00 AM | Mozzarella di Giovanni | 5 | 34.80 |
542379 | 7/5/2012 12:00:00 AM | Tofu | 9 | 18.60 |
542379 | 7/5/2012 12:00:00 AM | Manjimup Dried Apples | 40 | 42.40 |
542380 | 7/8/2012 12:00:00 AM | Jack's New England Clam Chowder | 10 | 7.70 |
542380 | 7/8/2012 12:00:00 AM | Manjimup Dried Apples | 35 | 42.40 |
542380 | 7/8/2012 12:00:00 AM | Louisiana Fiery Hot Pepper Sauce | 15 | 16.80 |
542381 | 7/8/2012 12:00:00 AM | Gustaf's Knäckebröd | 6 | 16.80 |
542381 | 7/8/2012 12:00:00 AM | Ravioli Angelo | 15 | 15.60 |
542381 | 7/8/2012 12:00:00 AM | Louisiana Fiery Hot Pepper Sauce | 20 | 16.80 |
542382 | 7/9/2012 12:00:00 AM | Sir Rodney's Marmalade | 40 | 64.80 |
542382 | 7/9/2012 12:00:00 AM | Geitost | 25 | 2.00 |
![]() |