INNER JOIN with multiple conditions in SQL - Mini Project: Build & Apply

INNER JOIN with multiple conditions

📖 Scenario: You are managing a small bookstore database. You have two tables: Books and Sales. You want to find the sales details for books that match both the book ID and the store location.

🎯 Goal: Build an SQL query using INNER JOIN with multiple conditions to combine the Books and Sales tables on book_id and store_location.

📋 What You'll Learn

Create a Books table with columns book_id, title, and store_location.

Create a Sales table with columns sale_id, book_id, store_location, and quantity.

Insert the exact data rows provided into both tables.

Write an INNER JOIN query joining Books and Sales on both book_id and store_location.

Select title, store_location, and quantity from the joined tables.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use databases to track inventory and sales across multiple locations. Joining tables on multiple conditions helps get accurate combined data.

💼 Career

Knowing how to write INNER JOIN queries with multiple conditions is essential for data analysts, database administrators, and backend developers working with relational databases.

1

Create the Books table and insert data

Create a table called Books with columns book_id (integer), title (text), and store_location (text). Insert these rows exactly: (1, 'SQL Basics', 'Downtown'), (2, 'Advanced SQL', 'Uptown'), (3, 'Database Design', 'Downtown').

SQL

-- Create Books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the Sales table and insert data

Create a table called Sales with columns sale_id (integer), book_id (integer), store_location (text), and quantity (integer). Insert these rows exactly: (101, 1, 'Downtown', 5), (102, 2, 'Uptown', 3), (103, 3, 'Downtown', 7), (104, 1, 'Uptown', 2).

SQL

CREATE TABLE Books (
    book_id INTEGER,
    title TEXT,
    store_location TEXT
);

INSERT INTO Books (book_id, title, store_location) VALUES
(1, 'SQL Basics', 'Downtown'),
(2, 'Advanced SQL', 'Uptown'),
(3, 'Database Design', 'Downtown');

-- Create Sales table and insert data
-- Your code here

Hint

Define the Sales table with the specified columns and insert the rows exactly.

3

Write INNER JOIN query with multiple conditions

Write an SQL query that uses INNER JOIN to join Books and Sales on both book_id and store_location. Select title, store_location, and quantity from the joined tables.

SQL

CREATE TABLE Books (
    book_id INTEGER,
    title TEXT,
    store_location TEXT
);

INSERT INTO Books (book_id, title, store_location) VALUES
(1, 'SQL Basics', 'Downtown'),
(2, 'Advanced SQL', 'Uptown'),
(3, 'Database Design', 'Downtown');

CREATE TABLE Sales (
    sale_id INTEGER,
    book_id INTEGER,
    store_location TEXT,
    quantity INTEGER
);

INSERT INTO Sales (sale_id, book_id, store_location, quantity) VALUES
(101, 1, 'Downtown', 5),
(102, 2, 'Uptown', 3),
(103, 3, 'Downtown', 7),
(104, 1, 'Uptown', 2);

-- Write INNER JOIN query with multiple conditions
-- Your code here

Hint

Use INNER JOIN with ON clause containing both conditions joined by AND.

4

Complete the query with aliasing

Modify the previous query to use table aliases b for Books and s for Sales. Select b.title, b.store_location, and s.quantity using these aliases.

SQL

CREATE TABLE Books (
    book_id INTEGER,
    title TEXT,
    store_location TEXT
);

INSERT INTO Books (book_id, title, store_location) VALUES
(1, 'SQL Basics', 'Downtown'),
(2, 'Advanced SQL', 'Uptown'),
(3, 'Database Design', 'Downtown');

CREATE TABLE Sales (
    sale_id INTEGER,
    book_id INTEGER,
    store_location TEXT,
    quantity INTEGER
);

INSERT INTO Sales (sale_id, book_id, store_location, quantity) VALUES
(101, 1, 'Downtown', 5),
(102, 2, 'Uptown', 3),
(103, 3, 'Downtown', 7),
(104, 1, 'Uptown', 2);

-- Modify query to use aliases b and s
-- Your code here

Hint

Use b and s as aliases for the tables and update the SELECT and ON clauses accordingly.

Practice

1. What does an INNER JOIN with multiple conditions do in SQL?

easy

A. It returns rows where all join conditions are true.

B. It returns rows where any one of the join conditions is true.

C. It returns all rows from both tables regardless of conditions.

D. It returns rows only from the first table.

Solution

1. Step 1: Understand INNER JOIN basics

   INNER JOIN returns rows that have matching values in both tables based on join conditions.

2. Step 2: Analyze multiple conditions with AND

   When multiple conditions are combined with AND, all must be true for a row to be included.

3. Final Answer:

   It returns rows where all join conditions are true. -> Option A

4. Quick Check:

   INNER JOIN + AND = all conditions true [OK]

Hint: All join conditions must be true with AND in INNER JOIN [OK]

Common Mistakes:

• Thinking any one condition is enough
• Confusing INNER JOIN with OUTER JOIN
• Ignoring the AND operator effect

2. Which of the following is the correct syntax for an INNER JOIN with two conditions on tables Orders and Customers?

easy

A. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status;

B. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID OR Orders.Status = Customers.Status;

C. SELECT * FROM Orders INNER JOIN Customers WHERE Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status;

D. SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.ID, Orders.Status = Customers.Status;

Solution

1. Step 1: Identify correct JOIN syntax

   INNER JOIN requires ON keyword followed by conditions combined with AND for multiple matches.

2. Step 2: Check each option's syntax

   SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status; correctly uses ON with two conditions joined by AND. SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID OR Orders.Status = Customers.Status; uses OR which is incorrect for multiple conditions requiring all true. SELECT * FROM Orders INNER JOIN Customers WHERE Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status; wrongly uses WHERE instead of ON. SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.ID, Orders.Status = Customers.Status; has invalid syntax with comma.

3. Final Answer:

   SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID AND Orders.Status = Customers.Status; -> Option A

4. Quick Check:

   INNER JOIN + ON + AND = correct syntax [OK]

Hint: Use ON with AND for multiple join conditions [OK]

Common Mistakes:

• Using OR instead of AND in ON clause
• Replacing ON with WHERE for join conditions
• Separating conditions with commas

3. Given tables Employees and Departments with columns Employees.DeptID, Departments.ID, and Departments.Location, what will this query return?

SELECT Employees.Name, Departments.Name FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.ID AND Departments.Location = 'NY';

medium

A. Syntax error due to multiple conditions in JOIN.

B. All employees with their department names regardless of location.

C. Departments located in NY with all employees listed multiple times.

D. Employees working only in departments located in NY with their department names.

Solution

1. Step 1: Understand the JOIN conditions

   The query joins Employees and Departments where DeptID matches ID and the department location is 'NY'.

2. Step 2: Analyze the result rows

   Only employees whose department is in NY will appear with their department names. Others are excluded.

3. Final Answer:

   Employees working only in departments located in NY with their department names. -> Option D

4. Quick Check:

   INNER JOIN + multiple conditions filters rows precisely [OK]

Hint: Multiple conditions filter rows strictly in INNER JOIN [OK]

Common Mistakes:

• Assuming all employees appear regardless of location
• Thinking multiple conditions cause syntax error
• Confusing join condition with WHERE clause

4. Identify the error in this SQL query:

SELECT * FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.ID, Products.Category = Suppliers.Category;

medium

A. Using ON instead of WHERE for join conditions.

B. Missing WHERE clause for the second condition.

C. Using comma instead of AND to combine join conditions.

D. INNER JOIN cannot have multiple conditions.

Solution

1. Step 1: Review JOIN condition syntax

   Multiple join conditions must be combined with AND inside the ON clause.

2. Step 2: Identify the error in the query

   The query incorrectly uses a comma to separate conditions, which is invalid syntax.

3. Final Answer:

   Using comma instead of AND to combine join conditions. -> Option C

4. Quick Check:

   Multiple conditions joined by AND, not comma [OK]

Hint: Use AND, not comma, to join multiple ON conditions [OK]

Common Mistakes:

• Separating conditions with commas
• Confusing WHERE and ON clauses
• Believing INNER JOIN allows only one condition

5. You have two tables: Orders(OrderID, CustomerID, Status) and Customers(CustomerID, Country, Status). You want to find orders where the customer is from 'USA' and both order and customer have the same status. Which query correctly uses INNER JOIN with multiple conditions?

hard

A. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.Country = 'USA' AND Orders.Status = Customers.Status;

B. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Customers.Country = 'USA' AND Orders.Status = Customers.Status;

C. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID OR Customers.Country = 'USA' AND Orders.Status = Customers.Status;

D. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID, Customers.Country = 'USA', Orders.Status = Customers.Status;

Solution

1. Step 1: Understand the filtering requirements

   We need to join on CustomerID and filter where customer is from USA and order status matches customer status.

2. Step 2: Check correct use of multiple conditions in INNER JOIN

   SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Customers.Country = 'USA' AND Orders.Status = Customers.Status; correctly uses AND to combine all conditions inside ON clause. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID OR Customers.Country = 'USA' AND Orders.Status = Customers.Status; wrongly uses OR which changes logic. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.Country = 'USA' AND Orders.Status = Customers.Status; moves conditions to WHERE, which is valid but not the question's focus on multiple ON conditions. SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID, Customers.Country = 'USA', Orders.Status = Customers.Status; uses commas incorrectly.

3. Final Answer:

   SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Customers.Country = 'USA' AND Orders.Status = Customers.Status; -> Option B

4. Quick Check:

   Multiple AND conditions inside ON for precise join [OK]

Hint: Combine all join filters with AND inside ON clause [OK]

Common Mistakes:

• Using OR instead of AND in join conditions
• Placing join filters in WHERE instead of ON
• Separating conditions with commas