SQL JOIN

Problem: List all suppliers with their products.

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

Try it live

---

• (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.

All INNER and OUTER keywords are optional.

---

Details about these JOIN operations are available below 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.
INNER JOIN is the most commonly used type of JOIN operation.

---

SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
  FROM [Order] 
  JOIN Customer ON [Order].CustomerId = Customer.Id

Try it live

Using table Aliases for [Order] and Customer might have been useful in this example.

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.

---