SQL INNER JOIN

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.

How to use INNER JOIN in SQL
Example: SQL INNER JOIN

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

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

INNER JOIN With WHERE Clause

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

Did you find this article helpful?