INNER JOIN with multiple conditions in SQL

Introduction

INNER JOIN with multiple conditions helps you combine rows from two tables only when all the conditions match. It lets you be more specific about how tables relate.

When you want to find matching records based on more than one rule, like matching both customer ID and order date.

When you need to combine data from two tables but only if several columns match exactly.

When filtering joined data with multiple criteria to get precise results.

When you want to avoid incorrect matches by checking multiple columns.

When you want to join tables on a combination of keys or attributes.

Syntax

SQL

SELECT columns
FROM table1
INNER JOIN table2
ON table1.columnA = table2.columnA
AND table1.columnB = table2.columnB;

Use AND to add multiple conditions in the ON clause.

All conditions must be true for rows to join.

Examples

Joins Employees and Departments only if both DepartmentID and Location match.

SQL

SELECT *
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.ID
AND Employees.Location = Departments.Location;

Joins Orders and Customers where CustomerID matches and order status is 'Completed'.

SQL

SELECT Orders.OrderID, Customers.Name
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.ID
AND Orders.Status = 'Completed';

Sample Program

This query joins Students and Scores tables only when both StudentID and Class match. It shows each student's score in their class.

SQL

CREATE TABLE Students (
  StudentID INT,
  Name VARCHAR(50),
  Class VARCHAR(10)
);

CREATE TABLE Scores (
  StudentID INT,
  Class VARCHAR(10),
  Score INT
);

INSERT INTO Students VALUES (1, 'Alice', 'Math');
INSERT INTO Students VALUES (2, 'Bob', 'Science');
INSERT INTO Students VALUES (3, 'Charlie', 'Math');

INSERT INTO Scores VALUES (1, 'Math', 85);
INSERT INTO Scores VALUES (1, 'Science', 90);
INSERT INTO Scores VALUES (2, 'Science', 88);
INSERT INTO Scores VALUES (3, 'Math', 92);

SELECT Students.Name, Scores.Score
FROM Students
INNER JOIN Scores
ON Students.StudentID = Scores.StudentID
AND Students.Class = Scores.Class;

Important Notes

Make sure all columns used in multiple conditions exist in both tables.

Using multiple conditions helps avoid wrong matches when one column alone is not unique.

You can combine multiple conditions with AND and even use OR if needed, but be careful with logic.

Summary

INNER JOIN with multiple conditions matches rows only when all conditions are true.

Use AND in the ON clause to add more than one matching rule.

This helps get precise joined data and avoid incorrect matches.