Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

SQL INNER JOIN

INNER JOIN combines records from two tables.

An INNER JOIN matches column values in two tables.

A query can contain zero, one, or multiple JOIN operations.

INNER JOIN is the same as JOIN; the keyword INNER is optional.

SQL INNER JOIN

Example

#

List all suppliers with their products, sorted by company.

SELECT CompanyName, ProductName
  FROM Supplier S
 INNER JOIN Product P ON S.Id = P.SupplierId
 ORDER BY CompanyName
Result:  78 records
CompanyName ProductName
Aux joyeux ecclésiastiques Côte de Blaye
Aux joyeux ecclésiastiques Chartreuse verte
Bigfoot Breweries Sasquatch Ale
Bigfoot Breweries Steeleye Stout
Bigfoot Breweries Laughing Lumberjack Lager
Cooperativa de Quesos 'Las Cabras' Queso Cabrales
Cooperativa de Quesos 'Las Cabras' Queso Manchego La Pastora
Escargots Nouveaux Escargots de Bourgogne

Syntax

INNER JOIN syntax.

SELECT column-names
  FROM table-name1 
 INNER JOIN table-name2 ON column-name1 = column-name2
 WHERE condition

The INNER keyword is optional and is the same as a JOIN.
INNER JOINs are the most commonly used form of JOIN operations.


More Examples

INNER 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
 INNER JOIN Customer C ON C.Id = O.CustomerId
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

INNER JOIN with 3 tables

Problem: List all orders with product names, quantities, and prices.
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 
 INNER JOIN OrderItem I ON O.Id = I.OrderId 
 INNER JOIN Product P ON P.Id = I.ProductId
 ORDER BY O.OrderNumber

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

Result:  2,155 records
OrderNumber Date ProductName Quantity UnitPrice
542378 7/4/2012 Queso Cabrales 12 14.00
542378 7/4/2012 Singaporean Hokkien Fried Mee 10 9.80
542378 7/4/2012 Mozzarella di Giovanni 5 34.80
542379 7/5/2012 Tofu 9 18.60
542379 7/5/2012 Manjimup Dried Apples 40 42.40
542380 7/8/2012 Jack's New England Clam Chowder 10 7.70
542380 7/8/2012 Manjimup Dried Apples 35 42.40
542380 7/8/2012 Louisiana Fiery Hot Pepper Sauce 15 16.80
542381 7/8/2012 Gustaf's Knäckebröd 6 16.80
542381 7/8/2012 Ravioli Angelo 15 15.60
542381 7/8/2012 Louisiana Fiery Hot Pepper Sauce 20 16.80
542382 7/9/2012 Sir Rodney's Marmalade 40 64.80
542382 7/9/2012 Geitost 25 2.00

You may also like



Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

Guides


vsn 3.1