What is inner join in SQL?

We can use the INNER JOIN keyword to join two tables in our database. The query returns records that have matching values in both tables.

The INNER JOIN (can also use JOIN) instruction is often used to group relevant data that is stored in separate tables.

The `ON` clause

We use the ON clause in conjunction with JOIN to specify the joining condition.

The ON clause makes the code easier to read and understand. It specifies the attribute in one table that has corresponding values in another table.

Code

We begin by creating a demo database, populating it with values, and displaying the current database.

Our Customer_Orders database consists of two tables. The first table, Customers, stores the CustomerID, Name, and Address of the customers. The second table, Orders, stores the OrderID, CustomerID, Order_Date, and the Cost.

CREATE TABLE Customers(
  CustomerID int PRIMARY KEY,
  Customer_Name varchar(20),
  Address varchar(50)
);
CREATE TABLE Orders(
  OrderID int PRIMARY KEY,
  CustomerID int,
  Order_Date date,
  Cost int
);
INSERT INTO Customers
VALUES (1, 'Dave', '67 Hemmingway Street');
INSERT INTO Customers
VALUES (2, 'Alberto', '467 Pensylvannia Avenue');
INSERT INTO Customers
VALUES (3, 'Joe', 'The White House');
INSERT INTO Orders
VALUES (11, 1, '2021/08/10', 100);
INSERT INTO Orders
VALUES (12, 2, '2020/12/11', 200);
INSERT INTO Orders
VALUES (13, 3, '2020/10/13', 250);
SELECT * FROM Customers;
SELECT * FROM Orders;

CREATE TABLE Customers(
  CustomerID int PRIMARY KEY,
  Customer_Name varchar(20),
  Address varchar(50)
);
CREATE TABLE Orders(
  OrderID int PRIMARY KEY,
  CustomerID int,
  Order_Date date,
  Cost int
);
INSERT INTO Customers
VALUES (1, 'Dave', '67 Hemmingway Street');
INSERT INTO Customers
VALUES (2, 'Alberto', '467 Pensylvannia Avenue');
INSERT INTO Customers
VALUES (3, 'Joe', 'The White House');
INSERT INTO Orders
VALUES (11, 1, '2021/08/10', 100);
INSERT INTO Orders
VALUES (12, 2, '2020/12/11', 200);
INSERT INTO Orders
VALUES (13, 3, '2020/10/13', 250);
/*We will now join the order and customer tables*/
SELECT *
FROM Orders INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Unlock your potential: SQL joins series, all in one place!

To continue your exploration of SQL joins, check out our series of Answers below:

Different types of SQL joins
Understand the different types of SQL joins used for combining data from multiple tables.
What is inner join in SQL?
Learn how inner joins return matched rows from both tables in SQL.
What is outer join in SQL?
Explore outer joins and how they return unmatched rows as well.
What is a self join in SQL?
Discover how self joins allow a table to join with itself.
What is a natural join in SQL?
Learn how natural joins match columns with the same name in SQL tables.
What is a hash join in SQL?
Understand hash joins, which are used for large-scale data matching and combining.
What is the SQL CROSS JOIN?
Explore the SQL CROSS JOIN to combine every row from one table with every row from another.
What is an equi join in SQL?
Learn how equi joins match rows based on the equality of values between tables.
How to join 3 or more tables in SQL
Master the technique of joining multiple tables in a SQL query for complex data retrieval.

What is inner join in SQL?

The ON clause

Code

The `ON` clause