SQL - Inner Join

SQL INNER JOIN

The SQL JOIN clause is a fundamental tool used to combine rows from two or more tables based on a related column between them. This allows you to query data that is logically connected but stored separately for normalization purposes.

The SQL INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It is the most common type of join. If a row in one table does not have a corresponding match in the other table, it is excluded from the result set.

Think of it like the intersection of two sets in a Venn diagram. You only get the data that exists in the overlapping section.

Note: INNER JOIN is the default join type in most SQL databases. The keyword JOIN on its own is synonymous with INNER JOIN.

Syntax and Best Practices

The modern, explicit syntax for an INNER JOIN is highly recommended for its clarity.

SELECT
    t1.column_name,
    t2.column_name
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.common_column = t2.common_column;

Key best practices to follow:

• Use Table Aliases: Assign short, logical aliases to your tables (e.g., Customers AS c, Orders AS o). This makes your query much more readable, especially when joining multiple tables.
• Qualify Column Names: Prefix column names with the table alias (e.g., c.ID, o.Amount). This prevents ambiguity if tables have columns with the same name and improves readability.
• Separate Join Logic: The ON clause should contain only the conditions for joining the tables. Filtering conditions should be placed in a WHERE clause.

Example: Joining Customers and Orders

Let’s set up two tables: Customers and Orders. The Orders table has a CustomerID column that is a foreign key referencing the ID in the Customers table.

CREATE TABLE Customers (
    ID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    City VARCHAR(100)
);

INSERT INTO Customers (ID, Name, Age, City) VALUES
    (1, 'Ramesh', 32, 'Ahmedabad'),
    (2, 'Khilan', 25, 'Delhi'),
    (3, 'Kaushik', 23, 'Kota'),
    (4, 'Chaitali', 25, 'Mumbai');

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, OrderDate, CustomerID, Amount) VALUES
    (101, '2023-11-20', 2, 1560.00),
    (102, '2023-10-08', 3, 3000.00),
    (103, '2022-05-20', 4, 2060.00),
    (104, '2023-10-08', 3, 1500.00);

Now, let’s join them to see which customer placed which order. We only want to see customers who have placed an order.

SELECT
    c.ID AS CustomerID,
    c.Name AS CustomerName,
    o.Amount,
    o.OrderDate
FROM Customers AS c
INNER JOIN Orders AS o ON c.ID = o.CustomerID;

Output

Notice that Ramesh (ID 1), who has no orders, does not appear in the result.

CustomerID	CustomerName	Amount	OrderDate
2	Khilan	1560.00	2023-11-20
3	Kaushik	3000.00	2023-10-08
4	Chaitali	2060.00	2022-05-20
3	Kaushik	1500.00	2023-10-08

Joining Multiple Tables

You can chain INNER JOIN clauses to combine three or more tables. Each JOIN clause connects one more table to the result set built so far.

Example: Adding Shippers

Let’s add a Shippers table and link it to Orders.

CREATE TABLE Shippers (
    ShipperID INT PRIMARY KEY,
    ShipperName VARCHAR(100)
);

-- Add a ShipperID column to Orders table
ALTER TABLE Orders ADD COLUMN ShipperID INT;

INSERT INTO Shippers (ShipperID, ShipperName) VALUES
(1, 'Speedy Express'),
(2, 'United Package');

-- Update orders with shippers
UPDATE Orders SET ShipperID = 1 WHERE OrderID IN (101, 103);
UPDATE Orders SET ShipperID = 2 WHERE OrderID IN (102, 104);

Now, we can join all three tables to get a complete view of each order.

SELECT
    o.OrderID,
    c.Name AS CustomerName,
    o.Amount,
    s.ShipperName
FROM Customers AS c
INNER JOIN Orders AS o ON c.ID = o.CustomerID
INNER JOIN Shippers AS s ON o.ShipperID = s.ShipperID;

Output

OrderID	CustomerName	Amount	ShipperName
101	Khilan	1560.00	Speedy Express
103	Chaitali	2060.00	Speedy Express
102	Kaushik	3000.00	United Package
104	Kaushik	1500.00	United Package

Combining INNER JOIN with a WHERE Clause

After joining tables, you often want to filter the results. This is done by adding a WHERE clause after all the JOIN clauses.

Example: Find High-Value Orders

Let’s find all orders with an amount greater than 2000.00.

SELECT
    c.ID,
    c.Name,
    o.OrderDate,
    o.Amount
FROM Customers AS c
INNER JOIN Orders AS o ON c.ID = o.CustomerID
WHERE o.Amount > 2000.00;

Output

This query first joins the tables to get all customer/order pairs and then filters that result set to keep only the rows matching the WHERE condition.

ID	Name	OrderDate	Amount
3	Kaushik	2023-10-08	3000.00
4	Chaitali	2022-05-20	2060.00

Common Pitfalls

• Accidental Cartesian Product: Forgetting the ON clause (or providing an incorrect one) can cause every row from the first table to be joined with every row from the second. This can generate massive, incorrect result sets and cripple performance.
• Joining on Non-Indexed Columns: Joining tables on columns that are not indexed (especially primary or foreign keys) can be extremely slow on large tables. Always ensure your join keys are indexed.
• Implicit JOIN Syntax: Avoid the old comma-based syntax (FROM table1, table2 WHERE table1.id = table2.id). It mixes joining and filtering logic, making queries harder to read and maintain.