INNER JOIN with ON condition in SQL

Introduction

An INNER JOIN combines rows from two tables when a related column matches in both. It helps find connected data easily.

You want to see orders along with customer details where the customer exists.

You need to list employees and their department names only if they belong to a department.

You want to find products and their suppliers where the supplier information is available.

Syntax

SQL

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

The ON condition tells how to match rows between the two tables.

Only rows with matching values in both tables appear in the result.

Examples

Shows all customers who have placed orders, combining their details.

SQL

SELECT *
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Lists employees with their department names only if they belong to a department.

SQL

SELECT Employees.Name, Departments.Name
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Sample Program

This query shows customers who have orders, matching by CustomerID. Note that order 103 has CustomerID 4, which does not exist in Customers, so it is excluded.

SQL

CREATE TABLE Customers (
  CustomerID INT,
  CustomerName VARCHAR(50)
);

CREATE TABLE Orders (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE
);

INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Orders VALUES (101, 1, '2024-01-10'), (102, 2, '2024-01-11'), (103, 4, '2024-01-12');

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

Important Notes

INNER JOIN only returns rows where the ON condition matches in both tables.

If no match is found, the row is not included in the result.

Use INNER JOIN to combine related data from two tables based on a common column.

Summary

INNER JOIN connects rows from two tables where a condition matches.

It helps combine related information from different tables.

Only matching rows appear in the result, unmatched rows are left out.