SQL INNER JOIN

In this tutorial, we'll learn about SQL INNER JOIN with the help of examples.

The SQL INNER JOIN joins two tables based on a common column, and selects records that have matching values in these columns.

Example

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

Here's how this code works:

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

Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table).


Syntax of INNER JOIN

The syntax of INNER JOIN is:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

INNER JOIN With WHERE Clause

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

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 snippet short and clean. For example,

SELECT C.category_name, P.product_title
FROM Category AS C
INNER JOIN Products AS P
ON C.category_id = P.cat_id;

Here, the SQL command selects common rows between Category and Products table.


SQL INNER JOIN With Three Tables

We can also join more than two tables using the INNER JOIN. For example,

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_id
INNER JOIN Shipping AS S
ON C.customer_id = S.customer_id;

Here, the SQL command

  • joins Customers and Orders table based on customer_id
  • and joins Customers and Status table based on customer_id

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 the customer_id column in each individual table.


Inner Join Vs Other Joins

INNER JOIN Vs JOIN

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

That means,

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

is similar to

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

The INNER JOIN selects the common rows between two tables. Whereas the LEFT JOIN selects the common rows as well as all the remaining rows from the left table.

Let's take a look at example,

INNER JOIN

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

Output

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

LEFT JOIN

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

Output

How to use LEFT JOIN in SQL
Example: SQL LEFT JOIN Output
INNER JOIN Vs RIGHT JOIN

The INNER JOIN selects the common rows between two tables. Whereas the RIGHT JOIN selects the common rows as well as all the remaining rows from the right table.

Let's take a look at example,

INNER JOIN

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

Output

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

RIGHT JOIN

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

Output

How to use RIGHT JOIN in SQL
Example: SQL RIGHT JOIN Output
INNER JOIN Vs FULL OUTER JOIN

The INNER JOIN selects the common rows between two tables. Whereas the FULL OUTER JOIN selects all the rows from both the tables.

Let's take a look at example,

INNER JOIN

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

Output

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

FULL OUTER JOIN

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

Output

How to use FULL OUTER JOIN in SQL
Example: SQL FULL OUTER JOIN Output

Recommended Readings

Did you find this article helpful?