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

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.

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