Overview: In this tutorial you will learn and understand how to use the INNER Join in SQL query, to join two or more tables with examples.
Introduction
In SQL JOINS, INNER JOIN use to combine rows from two tables based on a related column between them. The result is a new table that includes only the rows where there is a match in both tables. The INNER JOIN operation returns only the rows that have matching values in both tables.
Lets consider two tables “Customers” and “Orders”. The “Customers” table contains information about customers such as customer ID, name, and address, while the “Orders” table contains information about the orders placed by customers, such as order ID, customer ID, and order date.
If we want to get information about the customers and their orders, we can use an INNER JOIN on the “Customers” and “Orders” tables based on the “CustomerID” column, which is a related column between the two tables.
Syntax:
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The result of this INNER JOIN operation would be a new table that includes only the rows where there is a match in both the “Customers” and “Orders” tables based on the “CustomerID” column. The new table would contain columns from both tables, including the “CustomerID”, “Name”, “Address”, “OrderID”, and “OrderDate”.
Example #1: INNER Join for multiple tables
SQL INNER JOIN can also be use between multiple tables, to combine rows based on related columns between all the tables.
Lets consider three tables “Customers”, “OrderDetails” and “Orders”.
The “Customers” table contains information about customers such as customer ID, name, and address.
The “Orders” table contains information about the orders placed by customers, such as order ID, customer ID, and order date.
The “OrderDetails” table contains information about the details of the orders, such as order ID, product ID, and quantity.
If you want to get information about the customers, their orders, and the details of their orders, we can use an INNER JOIN on the three tables based on the related columns between them.
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;
The result of this INNER JOIN would be a new table that includes only the rows where there is a match in all three tables based on the related columns “CustomerID”, “OrderID”. The new table would contain columns from all three tables, including the “CustomerID”, “Name”, “Address”, “OrderID”, “OrderDate”, “ProductID”, and “Quantity”.
Example #2: INNER Join using condition with WHERE clause
WHERE clause can be combine with INNER Join to specify conditions for the joined rows. The WHERE clause is used to filter the results of the INNER JOIN operation based on specific conditions.
Lets consider two tables “Customers” and “Orders”. The “Customers” table contains information about customers such as customer ID, name, and address, while the “Orders” table contains information about the orders placed by customers, such as order ID, customer ID, and order date.
If we want to get information about the customers who have placed orders in the last month, we can use an INNER JOIN operation on the “Customers” and “Orders” tables based on the “CustomerID” column, and filter the results using the WHERE clause.
SQL code:
SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate >= DATEADD(month, -1, GETDATE());
The result of this INNER JOIN operation would be a new table that includes only the rows where there is a match in both the “Customers” and “Orders” tables based on the “CustomerID” column and where the “OrderDate” is in the last month. The new table would contain columns from both tables, including the “CustomerID”, “Name”, “Address”, “OrderID”, and “OrderDate”.
Example #3: Use INNER Join to INSERT result in another table
You can insert the result of an INNER JOIN operation into another table using the INSERT INTO command in SQL.
Lets take example with two tables “Customers” and “Orders”. The “Customers” table contains information about customers such as customer ID, name, and address, while the “Orders” table contains information about the orders placed by customers, such as order ID, customer ID, and order date.
If we want to store the information about the customers and their orders in a separate table, we can use an INNER JOIN operation on the “Customers” and “Orders” tables based on the “CustomerID” column, and insert the result into another table “CustomerOrders”.
First you need to create table using CREATE TABLE Statement OR you can also use existing table within database
CREATE TABLE CustomerOrders
(
CustomerID int,
Name varchar(50),
Address varchar(50),
OrderID int,
OrderDate date
);Now you can use below code to insert data from the result of INNER Join in sql.
INSERT INTO CustomerOrders
SELECT
Customers.CustomerID,
Customers.Name,
Customers.Address,
Orders.OrderID,
Orders.OrderDate
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
The result of this operation would be the creation of a new table “CustomerOrders” that contains the result of the INNER JOIN operation between the “Customers” and “Orders” tables based on the “CustomerID” column.
Example #4: INNER Join in SQL using GROUP by clause
The INNER JOIN can also be combined with the GROUP BY clause in SQL to group rows based on one or more columns. The GROUP BY clause is used to group the rows in the result of the INNER JOIN operation based on specific columns, and aggregate data based on these groups.
Lets take example, consider two tables “Employees” and “Orders”. The “Employees” table contains information about employees such as employee ID, name, and department, while the “Orders” table contains information about the orders placed by employees, such as order ID, employee ID, and order date.
If we want to get the number of orders placed by each employee in each department, we can use an INNER JOIN operation on the “Employees” and “Orders” tables based on the “EmployeeID” column, and group the result by the “Department” and “Name” columns.
SELECT
Employees.Department,
Employees.Name,
COUNT(Orders.OrderID) AS OrderCount
FROM Employees
INNER JOIN Orders
ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.Department, Employees.Name;
The result of this INNER JOIN operation would be a new table that includes the number of orders placed by each employee in each department, grouped by the “Department” and “Name” columns.