SQL INNER JOIN

-- 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;

SQL INNER JOIN Syntax

The syntax of the SQL INNER JOIN statement is:

SELECT columns_from_both_tables
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2

Here,

• table1 and table2 are the two tables that are to be joined
• column1 is the column in table1 that is related to column2 in table2

INNER JOIN excludes all the rows that are not common between two tables.

Note: We can also use JOIN instead of INNER JOIN. Basically, these two clauses are the same.

Example 1: SQL INNER JOIN

-- join the Customers and Orders tables with matching fields customer_id and customer
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 rows from both tables if the values of customer_id (of the Customers table) and customer (of the Orders table) are a match.

Example 2: SQL INNER JOIN

Let's look at another example,

-- 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.

INNER JOIN With WHERE Clause

Here's an example of INNER JOIN with the WHERE clause:

-- join Customers and Orders table with matching fields customer_id and customer
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.

SQL INNER JOIN With AS Alias

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.

SQL INNER JOIN With Three Tables

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 Shippingss tables based on customer_id (from the Customers table) and customer (from the Shippings table)

The command returns those 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.

Recommended Readings

1. SQL JOIN
2. SQL LEFT JOIN
3. SQL RIGHT JOIN
4. SQL FULL OUTER JOIN