INNER JOIN ON vs WHERE clause in MySQL

When working with MySQL, one of the most common tasks is combining data from multiple tables. Two common approaches to achieve this are using the INNER JOIN ON condition and the WHERE clause. Although they may sometimes produce similar results, they are conceptually different and have distinct use cases. In this blog, let us explore what INNER JOIN ON and WHERE Clause are, along with the differences between them in detail, with examples for each.

Table of Contents:

• What is INNER JOIN ON?
  • Advantages of INNER JOIN ON
• What is the WHERE Clause in MySQL?
  • Advantages of WHERE Clause
• Key Differences between INNER JOIN ON and WHERE Clause
• Performance Comparison between INNER JOIN ON and WHERE Clause
• Real-world examples for INNER JOIN ON and WHERE Clause
• Conclusion

Before we explore the INNER JOIN ON and WHERE Clause, let’s first create a dataset to understand them better. We will use this dataset for all the examples to help you understand them easily.

– Creating the customer table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

– Inserting values into the customer table
INSERT INTO Customers (CustomerID, Name) VALUES
(1, 'Frank'),
(2, 'Varun'),
(3, 'Tejas');

INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, '2024-02-01'),
(102, 2, '2024-02-02'),
(103, 3, '2024-02-03');
SELECT * FROM Customers;

Output:

This is how the table looks after creation. 

What is INNER JOIN ON?

The INNER JOIN ON is a MySQL operation that combines the rows from multiple tables based on conditions. The INNER JOIN ON in MySQL will fetch only the matching records from different tables. If there is no matching record in the table, the INNER JOIN ON function will remove the unmatched row from the table. The ON clause ensures that the matched columns are retrieved from the table. 

Syntax:

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

Example:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: The query fetched only the matched records. It fetched the records of customers who placed orders based on their customer ID.

Advantages of INNER JOIN ON

• It uses indexes efficiently. Reduces query execution time.
• It is easily readable.
• It avoids unnecessary filtering as it only retrieves the necessary records.
• Large datasets can be handled easily using an optimized join algorithm. 

What is the WHERE Clause in MySQL?

The WHERE clause in MySQL is a filter that is used to filter the records based on the conditions given. This WHERE clause is used in SELECT, UPDATE, INSERT, and DELETE statements to retrieve or modify the records. If there is no matching element in the table, this query returns the empty set. 

Syntax:

SELECT columns
FROM table1, table2
WHERE table1.column = table2.column
AND additional_condition;

Example:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID;

Output:

Explanation: The matched column has fetched the matching data using the WHERE clause.  

Advantages of WHERE Clause 

• WHERE Clause is very simple and basic. Easier to write and understand. 
• After the join operations, the WHERE clause can be used to filter multiple data.
• It is useful when we need to filter data quickly without any join syntax.
• In a single WHERE clause, we can apply multiple conditions to multiple columns. 

Key Differences between INNER JOIN ON and WHERE Clause

Performance Comparison between INNER JOIN ON and WHERE Clause

Real-world examples for INNER JOIN ON and WHERE Clause

Case 1: To get the details of the players who played in a tournament through an online gaming leaderboard using INNER JOIN ON in MySQL:

Example:

CREATE TABLE Players (
    PlayerID INT PRIMARY KEY,
    PlayerName VARCHAR(100)
);
CREATE TABLE Tournaments (
    TournamentID INT PRIMARY KEY,
    TournamentName VARCHAR(100)
);

CREATE TABLE Scores (
    ScoreID INT PRIMARY KEY,
    PlayerID INT,
    TournamentID INT,
    Score INT,
    FOREIGN KEY (PlayerID) REFERENCES Players(PlayerID),
    FOREIGN KEY (TournamentID) REFERENCES Tournaments(TournamentID)
);
INSERT INTO Players (PlayerID, PlayerName) VALUES
(1, 'Prem'),
(2, 'Kiran'),
(3, 'Gaurav');
INSERT INTO Tournaments (TournamentID, TournamentName) VALUES
(101, 'Winter Championship'),
(102, 'Summer Showdown');
INSERT INTO Scores (ScoreID, PlayerID, TournamentID, Score) VALUES
(1, 1, 101, 1500),
(2, 2, 101, 1700),
(3, 3, 102, 1600);
SELECT Players.PlayerName, Tournaments.TournamentName, Scores.Score  
FROM Players  
INNER JOIN Scores ON Players.PlayerID = Scores.PlayerID  
INNER JOIN Tournaments ON Scores.TournamentID = Tournaments.TournamentID;

Output:

Explanation: The INNER JOIN ON command fetched the details of the players who participated through leaderboard scores. 

Case 2: To filter the borrower name list from the library through the book name they borrowed. 

Example:

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255),
    Author VARCHAR(100)
);
CREATE TABLE Borrowers (
    BorrowerID INT PRIMARY KEY,
    BorrowerName VARCHAR(100)
);
CREATE TABLE BorrowedBooks (
    BorrowID INT PRIMARY KEY,
    BookID INT,
    BorrowerID INT,
    BorrowDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID)
);
INSERT INTO Books (BookID, Title, Author) VALUES
(1, 'Pride and Prejudice', 'Jane Austen'),
(2, 'The Odyssey', 'Homer'),
(3, '1984', 'George Orwell');

INSERT INTO Borrowers (BorrowerID, BorrowerName) VALUES
(101, 'Steve'),
(102, 'Cavin');
INSERT INTO BorrowedBooks (BorrowID, BookID, BorrowerID, BorrowDate) VALUES
(1, 1, 101, '2024-02-01'),
(2, 2, 102, '2024-02-05');
SELECT Books.Title, Books.Author, Borrowers.BorrowerName, BorrowedBooks.BorrowDate  
FROM Books  
INNER JOIN BorrowedBooks ON Books.BookID = BorrowedBooks.BookID  
INNER JOIN Borrowers ON BorrowedBooks.BorrowerID = Borrowers.BorrowerID;

Output:

Explanation: The INNER JOIN fetched the details of books that were borrowed from the library. 

Case 3: To filter the most streamed songs using the WHERE Clause

Example:

CREATE TABLE Songs (
    SongID INT PRIMARY KEY,
    SongName VARCHAR(255),
    Artist VARCHAR(100),
    Streams INT
);

INSERT INTO Songs (SongID, SongName, Artist, Streams) VALUES
(1, 'Blinding Lights', 'The Weeknd', 3200000000),
(2, 'Shape of You', 'Ed Sheeran', 2900000000),
(3, 'Perfect', 'Ed Sheeran', 1500000000),
(4, 'Cupid', 'Fifty Fifty', 800000000);
SELECT SongName, Artist  
FROM Songs  
WHERE Streams > 1000000;

Output:

Explanation: The WHERE clause filters out the songs whose streams > 1000000 from the table. 

Case 4: To filter the available properties from the real estate websites. 

Example:

CREATE TABLE Properties (
    PropertyID INT PRIMARY KEY,
    Location VARCHAR(255),
    City VARCHAR(100),
    Price DECIMAL(10,2),
    Status VARCHAR(50) -- Available, Sold, Pending
);

INSERT INTO Properties (PropertyID, Location, City, Price, Status) VALUES
(1, '123 Main St', 'New Delhi', 850000.00, 'Available'),
(2, '456 Sector', 'Mumbai', 1250000.00, 'Sold'),
(3, '789 street', 'Madya Pradesh', 950000.00, 'Available'),
(4, '10th Cross street', 'New Delhi', 500000.00, 'Available');
SELECT PropertyID, Location, Price  
FROM Properties  
WHERE Status = 'Available' AND City = 'New Delhi';

Output:

Explanation: The WHERE clause filtered only the available property at New Delhi by using this command: “WHERE Status = ‘Available’ AND City = ‘New Delhi’;” 

Conclusion

The INNER JOIN ON and the WHERE clause are two of the essentials in MySQL to handle data from multiple tables. INNER JOIN ON will efficiently convert matching records based on conditions from multiple tables. It will only fetch the matched rows, and if there is any row that doesn’t have matching records, the inner join will remove that row from the table and return only the matching records. The WHERE clause will filter the rows after the joining operation in a table. The WHERE clause is very useful and faster when filtering the data, but is usually slower than the INNER JOIN ON when working on larger datasets. While both are used to filter and fetch the data in the most optimized way, you can choose any function based on the condition of your table. 

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.

INNER JOIN ON vs WHERE clause – FAQs