SQL INNER JOIN is used on tables that have Primary Key and Foreign Key relationship.
This shows there is a relationship between the tables that are joined.
When INNER JOIN is used we get matching and meaningful records between the tables joined.
SQL INNER JOIN Syntax
SELECT column_name1, column_name2 ...
FROM table1
INNER JOIN table2
ON table1.column_name1 = table2.column_name1
Below is shown the "Customers" table:
C001 |
Fredrick Johnson |
FJohnson@yahoo.com |
Berkeley Gardens 12 Brewery |
London |
WX1 6LT |
UK |
C002 |
Mary Davidson |
Mary@gmail.com |
35 King George |
Madrid |
28023 |
Spain |
C003 |
George Clooney |
George@hotmail.com |
Obere Str. 57 |
Berlin |
12209 |
Germany |
C004 |
David Louis |
David@mail.com |
2743 Bering St. |
NewJersy |
07019 |
USA |
C005 |
Johnny Anderson |
Johnny@gmail.com |
55 Grizzly Peak Rd. |
NewJersy |
07019 |
USA |
C006 |
Paul Henriot |
PaulHenriot@yahoo.com |
59 rue de l'Abbaye |
Paris |
51100 |
France |
C007 |
Henry King |
Henry@mail.com |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
C008 |
Robert John |
Robert@gmail.com |
87 Polk St. Suite 5 |
San Francisco |
94117 |
USA |
In the Customers table "CustomerID" is Primary Key with the unique values. You can see that the "CustomerID" column's values do not repeat.
Below is shown the "Orders" table:
D001 |
C001 |
4/3/2007 12:00:00 AM |
150.23 |
D002 |
C003 |
1/22/2005 12:00:00 AM |
123.57 |
D003 |
C005 |
3/19/2003 12:00:00 AM |
220.43 |
D004 |
C007 |
7/21/2008 12:00:00 AM |
157.72 |
D005 |
C008 |
2/28/2009 12:00:00 AM |
223.78 |
D006 |
C011 |
3/21/2005 12:00:00 AM |
245.34 |
In the Orders table, the "OrderID" is the Primary Key. Here you can see that "CustomerID" column is the Foreign Key.
The Customers and the Orders table are related to each other using the "CustomerID" column.
SQL INNER JOIN Example
To retrieve all the data from the Customers and Orders tables, that are matching, use the SQL given below.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select Customers.CustomerID, Customers.CustomerName, Orders.OrderID,
Orders.Date, Orders.Amount
From Customers
INNER JOIN Orders on Customers.CustomerID = Orders.CustomerID;
Note: You can see 5 records that are having matching CustomerID column.
SQL in Text Editor: