SQL JOIN

List all suppliers with their products.

SELECT CompanyName, ProductName
  FROM Supplier S
  JOIN Product P ON S.Id = P.SupplierId

Try it live

S and P in the above query are table aliases.

---

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

Try it live

O and C are table aliases.

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

Try it live

This query performs two JOIN operations with 3 tables.
The O, I, and P are table Aliases. Date is a column Alias.

---