SQL JOINS Practice Questions with Solutions

The SQL JOIN clause is used to combine data from two or more tables based on a related column.

In real-world databases, information is usually stored across multiple tables instead of one large table. SQL JOINs help retrieve related data efficiently. SQL JOINS practice questions with solutions help to understand the concepts.

For example:

• Student details are stored in a students table.
• Course details are stored in a courses table.

Using a JOIN, you can display the student’s name along with the course name in a single query.

Why Do We Use SQL JOIN?

SQL JOIN is used to:

• Combine related tables
• Reduce data duplication
• Generate business reports
• Retrieve meaningful information
• Build dashboards
• Analyze data across multiple tables

Types of SQL JOIN

Note: MySQL supports INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and SELF JOIN. FULL OUTER JOIN is typically simulated using UNION.

Sample Tables Used in This Chapter

students

courses

1. SQL INNER JOIN to Display Student Names with Their Course Names

Problem Statement

A training institute stores student information and course information in separate tables.

Write an SQL query to display each student’s name along with the course they are enrolled in.

SQL Solution

SELECT
    students.student_name,
    courses.course_name

FROM students

INNER JOIN courses
ON students.course_id = courses.course_id;

Sample Output

Explanation

The INNER JOIN matches rows where the course_id exists in both tables.

Only matching records are returned.

Since every student has a valid course ID, all students appear in the result.

Concepts Covered

• INNER JOIN
• ON Clause
• Matching Records

2. SQL INNER JOIN to Display Employee Names with Department Names

Problem Statement

A company stores employee details and department details in separate tables.

Write an SQL query to display employee names along with their department names.

Sample Tables

employees

departments

SQL Solution

SELECT
    employees.employee_name,
    departments.department_name

FROM employees

INNER JOIN departments
ON employees.department_id = departments.department_id;

Sample Output

Explanation

The INNER JOIN matches employees with their respective departments using the department_id.

Only employees having a matching department are displayed.

Concepts Covered

• INNER JOIN
• Multiple Tables
• Primary Key
• Foreign Key

3. SQL INNER JOIN to Display Customer Names with Their Orders

Problem Statement

An online shopping website stores customer details and order details in separate tables.

Write an SQL query to display customer names along with their order IDs.

Sample Tables

customers

orders

SQL Solution

SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount

FROM customers

INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Sample Output

Explanation

The query joins customers with their orders using customer_id.

Since Rahul placed two orders, his name appears twice—once for each order.

This behavior is expected in one-to-many relationships.

Concepts Covered

• INNER JOIN
• One-to-Many Relationship
• Business Reporting

4. SQL INNER JOIN to Display Product Names with Category Names

Problem Statement

An e-commerce company stores product information and category information in separate tables.

Write an SQL query to display each product along with its category name.

Sample Tables

products

categories

SQL Solution

SELECT
    products.product_name,
    categories.category_name,
    products.price

FROM products

INNER JOIN categories
ON products.category_id = categories.category_id;

Sample Output

Explanation

The INNER JOIN connects the products table with the categories table using the category_id.

Each product is displayed together with the name of its category instead of just the category ID.

This is commonly used in:

• E-commerce websites
• Inventory management
• Product catalogs
• Shopping applications

Concepts Covered

• INNER JOIN
• Foreign Key
• Product Database

5. SQL INNER JOIN to Display Book Titles with Author Names

Problem Statement

A library stores books and authors in separate tables.

Write an SQL query to display every book along with its author’s name.

Sample Tables

books

authors

SQL Solution

SELECT
    books.book_title,
    authors.author_name

FROM books

INNER JOIN authors
ON books.author_id = authors.author_id;

Sample Output

Explanation

The INNER JOIN matches each book with its corresponding author using the author_id.

Since one author can write multiple books, the author’s name appears for each matching book.

This relationship is known as a one-to-many relationship.

Concepts Covered

• INNER JOIN
• One-to-Many Relationship
• Library Database

6. SQL LEFT JOIN to Display All Customers Including Those Without Orders

Problem Statement

An online shopping website wants to display all customers, even if some customers have not placed any orders yet.

Write an SQL query to display customer names along with their order IDs.

Sample Tables

customers

orders

SQL Solution

SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount

FROM customers

LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Sample Output

Explanation

A LEFT JOIN returns all records from the left table (customers).

If a customer has no matching order, SQL returns NULL for the order-related columns.

This is commonly used for:

• Customer Reports
• CRM Dashboards
• Marketing Campaigns
• Customer Activity Analysis

Concepts Covered

• LEFT JOIN
• NULL Values
• One-to-Many Relationship

7. SQL LEFT JOIN to Display All Employees Including Those Without Departments

Problem Statement

An HR manager wants to display all employees, including employees who are not assigned to any department.

Write an SQL query to display employee names with their department names.

Sample Tables

employees

departments

SQL Solution

SELECT
    employees.employee_name,
    departments.department_name

FROM employees

LEFT JOIN departments
ON employees.department_id = departments.department_id;

Sample Output

Explanation

Every employee is displayed because the employees table is on the left side of the join.

Since Vikas is not assigned to any department, the department name appears as NULL.

Concepts Covered

• LEFT JOIN
• NULL
• Employee Database

8. SQL LEFT JOIN to Display All Students Including Those Without Projects

Problem Statement

A college wants to display all students, including students who have not been assigned any project.

Write an SQL query to display student names with their project titles.

Sample Tables

students

projects

SQL Solution

SELECT
    students.student_name,
    projects.project_title

FROM students

LEFT JOIN projects
ON students.student_id = projects.student_id;

Sample Output

Explanation

The query displays every student.

Students who do not have a project assigned receive NULL in the project_title column.

This report helps identify students who still need project allocation.

Concepts Covered

• LEFT JOIN
• NULL
• Student Management

9. SQL LEFT JOIN to Display All Products Including Uncategorized Products

Problem Statement

An online store wants to display all products, including products that have not been assigned to any category.

Write an SQL query to display product names with category names.

Sample Tables

products

categories

SQL Solution

SELECT
    products.product_name,
    categories.category_name

FROM products

LEFT JOIN categories
ON products.category_id = categories.category_id;

Sample Output

Explanation

Every product appears in the result.

The Keyboard has no category assigned, so SQL displays NULL.

This helps store administrators identify products requiring categorization.

Concepts Covered

• LEFT JOIN
• NULL
• Product Database

10. SQL LEFT JOIN to Display All Teachers Including Those Without Subjects

Problem Statement

A school wants to display all teachers, even if they have not yet been assigned any subject.

Write an SQL query to display teacher names with their subject names.

Sample Tables

teachers

subjects

SQL Solution

SELECT
    teachers.teacher_name,
    subjects.subject_name

FROM teachers

LEFT JOIN subjects
ON teachers.teacher_id = subjects.teacher_id;

Sample Output

Explanation

The query returns every teacher.

Teachers without an assigned subject receive NULL in the subject_name column.

This report helps school administrators identify unassigned teachers.

Concepts Covered

• LEFT JOIN
• NULL Values
• School Database

11. SQL RIGHT JOIN to Display All Departments Including Empty Departments

Problem Statement

A company wants to display all departments, including departments that currently have no employees.

Write an SQL query to display department names along with employee names.

Sample Tables

employees

departments

SQL Solution

SELECT
    employees.employee_name,
    departments.department_name

FROM employees

RIGHT JOIN departments
ON employees.department_id = departments.department_id;

Sample Output

Explanation

A RIGHT JOIN returns all records from the right table (departments).

Departments without employees still appear, while employee columns become NULL.

This report is useful for:

• HR Analytics
• Department Audits
• Organization Planning

Concepts Covered

• RIGHT JOIN
• NULL Values
• Department Reports

12. SQL RIGHT JOIN to Display All Categories Including Empty Categories

Problem Statement

An e-commerce website wants to display every product category, including categories that currently contain no products.

Sample Tables

products

categories

SQL Solution

SELECT
    products.product_name,
    categories.category_name

FROM products

RIGHT JOIN categories
ON products.category_id = categories.category_id;

Sample Output

Explanation

Every category appears in the result.

Since no product belongs to the Gaming category, SQL returns NULL for the product name.

Concepts Covered

• RIGHT JOIN
• Product Database
• NULL Values

13. SQL CROSS JOIN to Generate Every Student-Course Combination

Problem Statement

A training institute wants to generate every possible combination of students and available courses for batch planning.

Sample Tables

students

courses

SQL Solution

SELECT
    students.student_name,
    courses.course_name

FROM students

CROSS JOIN courses;

Sample Output

Explanation

A CROSS JOIN creates the Cartesian Product of two tables.

Each student is paired with every available course.

This type of join is commonly used for:

• Scheduling
• Simulation
• Test Data Generation
• Planning Systems

Concepts Covered

• CROSS JOIN
• Cartesian Product

14. SQL SELF JOIN to Display Employees with Their Managers

Problem Statement

A company stores employee and manager information in the same table.

Write an SQL query to display each employee along with their manager.

Sample Table

employees

SQL Solution

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager

FROM employees e

LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Sample Output

Explanation

A SELF JOIN joins a table with itself.

Aliases (e and m) are used so SQL can treat the same table as two separate tables.

This technique is commonly used for:

• Organization Charts
• Employee Hierarchies
• Family Trees
• Category Hierarchies

Concepts Covered

• SELF JOIN
• Table Alias
• LEFT JOIN

15. SQL INNER JOIN with Three Tables

Problem Statement

An online shopping website wants to display:

• Customer Name
• Order ID
• Product Name
• Quantity Ordered

The data is stored across three different tables.

Sample Tables

customers

orders

products

SQL Solution

SELECT
    customers.customer_name,
    orders.order_id,
    products.product_name,
    orders.quantity

FROM customers

INNER JOIN orders
ON customers.customer_id = orders.customer_id

INNER JOIN products
ON orders.product_id = products.product_id;

Sample Output

Explanation

This query joins three tables:

1. customers
2. orders
3. products

It retrieves complete order information in a single query.

Multi-table joins are widely used in:

• E-commerce Platforms
• ERP Systems
• Banking Applications
• CRM Software
• Business Intelligence Reports

Concepts Covered

• Multiple INNER JOINs
• Multi-Table Queries
• Relational Databases

Chapter Summary

In this chapter, you learned how SQL JOIN operations combine data from two or more related tables to produce meaningful results.

In real-world database applications, information is rarely stored in a single table. Instead, related data is separated into multiple tables to reduce redundancy and improve data management. SQL JOINs make it possible to retrieve this related information efficiently.

Throughout this chapter, you practiced:

• Using INNER JOIN to retrieve only matching records.
• Using LEFT JOIN to display all records from the left table, even if there is no match.
• Using RIGHT JOIN to display all records from the right table.
• Using CROSS JOIN to generate every possible combination of rows.
• Using SELF JOIN to relate records within the same table.
• Joining three tables in a single query for real-world reporting.

These concepts are essential for building reports, dashboards, and applications that rely on relational databases.

Key Takeaways

• SQL JOIN combines data from multiple tables.
• INNER JOIN returns only matching records.
• LEFT JOIN returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN returns all rows from the right table and matching rows from the left table.
• CROSS JOIN creates every possible combination of rows.
• SELF JOIN joins a table with itself using aliases.
• Multi-table joins are common in business reporting.
• Primary keys and foreign keys are used to establish relationships between tables.
• JOINs are one of the most important SQL interview topics.
• Mastering JOINs is essential for Data Analysts, SQL Developers, Backend Developers, and Data Engineers.

Frequently Asked Questions (FAQs)

1. What is a SQL JOIN?

A SQL JOIN combines data from two or more related tables.

Example:

SELECT
    students.student_name,
    courses.course_name

FROM students

INNER JOIN courses
ON students.course_id = courses.course_id;

2. What is the difference between INNER JOIN and LEFT JOIN?

3. When should I use LEFT JOIN?

Use LEFT JOIN when you want to display all records from the left table, even if there is no matching record in the right table.

Example:

SELECT
    customers.customer_name,
    orders.order_id

FROM customers

LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

4. What is a CROSS JOIN?

A CROSS JOIN returns the Cartesian product of two tables.

Example:

SELECT
    students.student_name,
    courses.course_name

FROM students

CROSS JOIN courses;

Every student is paired with every course.

5. What is a SELF JOIN?

A SELF JOIN joins a table with itself.

Example:

SELECT
    e.employee_name,
    m.employee_name AS manager

FROM employees e

LEFT JOIN employees m
ON e.manager_id = m.employee_id;

This is commonly used to display employee-manager relationships.

6. Can SQL JOIN combine more than two tables?

Yes.

Example:

SELECT
    customers.customer_name,
    products.product_name

FROM customers

INNER JOIN orders
ON customers.customer_id = orders.customer_id

INNER JOIN products
ON orders.product_id = products.product_id;

SQL can join multiple related tables in one query.

7. Why are primary keys and foreign keys important in JOINs?

• A Primary Key uniquely identifies each record in a table.
• A Foreign Key references the primary key of another table.

These relationships allow SQL JOINs to match related records correctly.

8. Where are SQL JOINs used in real-world applications?

SQL JOINs are widely used in:

• Banking Systems
• E-commerce Platforms
• Student Management Systems
• HR Management Software
• Hospital Databases
• CRM Applications
• ERP Systems
• Sales Dashboards
• Financial Reporting
• Business Intelligence Projects

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar