The SQL INNER JOIN statement joins two tables based on a common column and selects rows that have matching values in these columns.
Example
-- join Customers and Orders tables with their matching fields customer_id
SELECT Customers.customer_id, Orders.item
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer_id;Here, the SQL command joins the Customers and Orders tables.
The result includes customer_id (from Customers) and item (from Orders) of rows where customer IDs match (Customer.customer_id = Orders.customer_id).
SQL INNER JOIN Syntax
SELECT columns_from_both_tables
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2
Here,
- table1 and table2 - two tables that are to be joined
- column1 and column2 - columns common to in table1 and table2
Example 1: SQL INNER JOIN
-- join the Customers and Orders tables
-- with customer_id and customer fields
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer;Here, the SQL command selects the specified rows from both tables if the values of customer_id (of the Customers table) and customer (of the Orders table) are a match.
As you can see, INNER JOIN excludes all the rows that are not common between two tables.
Note: We can also use SQL JOIN instead of INNER JOIN. Basically, these two clauses are the same.
Example 2: Join Two Tables With a Matching Field
-- join Categories and Products tables
-- with their matching fields cat_id
SELECT Categories.cat_name, Products.prod_title
FROM Categories
INNER JOIN Products
ON Categories.cat_id = Products.cat_id;Here, the SQL command selects common rows between Categories and Products tables with the matching field cat_id.
The result set has the cat_name column from Categories and the prod_title column from Products.
More on SQL INNER JOIN
We can use AS aliases inside INNER JOIN to make our query short and clean. For example,
-- use alias C for Categories table
-- use alias P for Products table
SELECT C.cat_name, P.prod_title
FROM Categories AS C
INNER JOIN Products AS P
ON C.cat_id= P.cat_id;
Here, the SQL command performs an inner join on the Categories and Products tables while assigning the aliases C and P to them, respectively.
We can also join more than two tables using INNER JOIN. For example,
-- join three tables: Customers, Orders, and Shippings
SELECT C.customer_id, C.first_name, O.amount, S.status
FROM Customers AS C
INNER JOIN Orders AS O
ON C.customer_id = O.customer
INNER JOIN Shippings AS S
ON C.customer_id = S.customer;
Here, the SQL command
- joins Customers and Orders tables based on customer_id (from the Customers table) and customer (from the Orders table)
- and joins Customers and Shippings tables based on customer_id (from the Customers table) and customer (from the Shippings table)
The command returns the rows where there is a match between column values in both join conditions.
Note: For this command to run, there must be a customer_id column in each individual table. The column names can be different as long as they have common data.
Here's an example of INNER JOIN with the WHERE clause:
-- join Customers and Orders table
-- with customer_id and customer fields
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;
Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.
Also Read