SQL INNER JOIN

Have you ever thought that what if you have multiple tables? And you want to merge them somehow. In this case, Joins are going to help you merge two tables and return them as one table. 

In this tutorial, we will discuss specifically INNER JOIN in SQL with examples. And how, by using INNER JOIN, we will be able to generate a merged table containing the matching values from the existing participating tables.

Table of Content

• What is SQL Inner Join?
• Syntax Breakdown
• SQL INNER JOIN Example
• INNER JOIN with WHERE and ORDER BY clause
• Conclusion

What is SQL Inner Join?

A SQL INNER JOIN is a technique that allows us to combine the data from two existing tables, and it checks the common columns that are present in both tables. Based on that common column, it merges both tables into one. 

Suppose we have two tables, Table A and Table B. If we perform INNER JOIN on these tables, we will get only those records (rows) which are common in both tables. To implement it, we need to know the syntax of the INNER JOIN.

Syntax Breakdown

SELECT columns

FROM tableA name

INNER JOIN tableB name ON tableA.coumn_x = tableB.column_y;

In the above syntax, SELECT, INNER JOIN, and ON are the keywords. We are selecting those columns that we want to have in our final table after combining tableA and tableB. We can specify the columns like tableA.col1, tableB.col2 etc. column_x and column_y are the columns for performing the INNER JOIN

Then we will start from the first table, that is tableA, and then we are going to perform the INNER JOIN. We are providing a condition that if rows of tableA and tableb are matched, they will be included in the result table.

SQL INNER JOIN Example

Let’s apply SQL INNER JOIN to two tables, the Customer table and the Orders table.

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    Name NVARCHAR(50),

    Email NVARCHAR(100)

);

INSERT INTO Customers (CustomerID, Name, Email)

VALUES

(1, 'Pranav', '[email protected]'),

(2, 'Aryan', '[email protected]'),

(3, 'Sahil', '[email protected]');

We created our first table, Customers with three records, including CustomerID Name and email.

Now lets create second table, Orders having columns, OrderID, CustomerID and OrderAmount. We have also applied the concept of FOREIGN KEY as CustomerID column is present in both tables.

CREATE TABLE Orders (

    OrderID INT PRIMARY KEY,

    CustomerID INT,

    OrderAmount DECIMAL(10, 2),

    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

INSERT INTO Orders (OrderID, CustomerID, OrderAmount)

VALUES

(101, 1, 250.00),

(102, 2, 500.00),

(103, 1, 150.00);

Let’s perform inner join now.

SELECT 

    Customers.CustomerID,

    Customers.Name,

    Customers.Email,

    Orders.OrderID,

    Orders.OrderAmount

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

As you can see, we are getting the merged output of both tables where each customer’s details are matched with their respective orders.

INNER JOIN with WHERE and ORDER BY clause

Let’s see how we can use inner join in SQL with WHERE condition and ORDER BY clause.

SELECT 

    Customers.CustomerID,

    Customers.Name,

    Customers.Email,

    Orders.OrderID,

    Orders.OrderAmount

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

WHERE Orders.OrderAmount > 200.00

ORDER BY Orders.OrderAmount DESC;

Here, we are using the WHERE clause to put a condition that I want to fetch data that has more than 200 order amounts. The ORDER BY clause is going to help me sort the result records in a specific order. In our case, we are arranging the data in descending order.

Conclusion

INNER JOIN in SQL is an important tool for merging multiple tables based on related data. It helps us to retrieve the records that are common between the two tables.

This brings us to the end of this INNER JOIN in the SQL tutorial section. Go ahead and link the tables from your database to get a better insight into your data.