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 CustomersVALUES (1, 'Dave', '67 Hemmingway Street');INSERT INTO CustomersVALUES (2, 'Alberto', '467 Pensylvannia Avenue');INSERT INTO CustomersVALUES (3, 'Joe', 'The White House');INSERT INTO OrdersVALUES (11, 1, '2021/08/10', 100);INSERT INTO OrdersVALUES (12, 2, '2020/12/11', 200);INSERT INTO OrdersVALUES (13, 3, '2020/10/13', 250);SELECT * FROM Customers;SELECT * FROM Orders;
We now join the Customers and Orders tables to display the customer details for each order.
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 CustomersVALUES (1, 'Dave', '67 Hemmingway Street');INSERT INTO CustomersVALUES (2, 'Alberto', '467 Pensylvannia Avenue');INSERT INTO CustomersVALUES (3, 'Joe', 'The White House');INSERT INTO OrdersVALUES (11, 1, '2021/08/10', 100);INSERT INTO OrdersVALUES (12, 2, '2020/12/11', 200);INSERT INTO OrdersVALUES (13, 3, '2020/10/13', 250);/*We will now join the order and customer tables*/SELECT *FROM Orders INNER JOIN CustomersON Orders.CustomerID=Customers.CustomerID;
After using INNER JOIN, we obtain a table that contains all the details relating to the order and the customer.
The joining condition is specified as the CustomerID, which exists in both tables.
Free Resources
