SQL INNER JOIN

-- join Customers and Orders tables
-- select customer_id and first_name columns from the Customers table
-- select amount from the Orders table

SELECT Customers.customer_id, Customers.first_name, Orders.amount
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 a column in table1 and column2 in a column in table2

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

Example 1: SQL INNER JOIN

-- join the Customers and Orders tables when
-- the customer_id from Customers matches the customer column in Orders

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer; 

Here is how the above SQL query works:

Example 2: SQL INNER JOIN

Let's look at another example.

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 table based on the cat_id column, which is present in both tables.

INNER JOIN With WHERE Clause

We can use the WHERE clause in conjunction with an INNER JOIN to further filter the rows in the output.

-- join tables the Customers and Orders tables
-- return rows if amount is 500 or higher

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;
 

SQL INNER JOIN With AS Alias

Since join queries can be long, we can use the AS alias within INNER JOIN to make our queries more concise and easier to understand.

-- 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 across both join conditions.

Recommended Readings

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