SQL JOIN

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.

SQL JOIN

Example

#

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.

Result:  78 records.
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

SQL JOIN Types


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

SQL JOIN Diagram

SQL JOIN Types

Details about these JOIN operations are available in this and in subsequent pages.

Syntax

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.


More Examples

JOIN with 2 TABLES

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List all orders with customer information.
SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId

O and C are table aliases.

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

JOIN with 3 TABLES

Problem: List all orders with product name, quantity, and price, sorted by order number.
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.

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

You may also like



Guides


vsn 3.1