SQL JOIN

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

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.

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

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

• Our Sql LEFT JOIN Tutorial
• Our Sql RIGHT JOIN Tutorial
• Our Sql JOIN 3 Tables Tutorial
• Our Sql JOIN Multiple Tables Tutorial