A JOIN combines records from two tables.
JOIN matches related column values in two tables.
A query can contain zero, one, or multiple JOIN operations.
List all suppliers with their products.
SELECT CompanyName, ProductName
FROM Supplier S
JOIN Product P ON S.Id = P.SupplierId
S and P in the above query are table aliases.
| CompanyName | ProductName |
|---|---|
| Exotic Liquids | Chai |
| Exotic Liquids | Chang |
| Exotic Liquids | Aniseed Syrup |
| New Orleans Cajun Delights | Chef Anton's Cajun Seasoning |
| New Orleans Cajun Delights | Chef Anton's Gumbo Mix |
| Grandma Kelly's Homestead | Grandma's Boysenberry Spread |
| Grandma Kelly's Homestead | Uncle Bob's Organic Dried Pears |
| Grandma Kelly's Homestead | Northwoods Cranberry Sauce |
| Tokyo Traders | Mishi Kobe Niku |
| Tokyo Traders | Ikura |
 |
|
| Type | Description |
|---|---|
JOIN |
Match records in both tables |
| LEFT JOIN | Match left (first) table records with right table records |
| RIGHT JOIN | Match right (last) table records with left table records |
| FULL JOIN | Include all left and right records whether they match or not |
Details about these JOIN operations are available in this and in subsequent pages.
JOIN syntax.
SELECT column-names FROM table-name1 JOIN table-name2 ON column-name1 = column-name2 WHERE condition
INNER JOIN syntax.
SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition
JOIN is the same as INNER JOIN; the INNER keyword is optional.
JOIN (or INNER JOIN) is the most commonly used type of JOIN operation.
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
FROM [Order] O
JOIN Customer C ON C.Id = O.CustomerId
O and C are table aliases.
| 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 |
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.
| 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 |
|
||||
