Joins in MySQL | INNER JOIN, LEFT JOIN, RIGHT JOIN and CROSS JOIN

In a relational database, information is often stored in multiple tables to avoid data duplication and improve data organization. To retrieve related data stored in different tables, MySQL provides Joins. A JOIN combines rows from two or more tables based on a common column.

For example, one table may contain student details, while another table stores examination marks. Using a JOIN, we can display the student's name along with the marks without storing the same information repeatedly.

---

Learning Outcomes

After studying this chapter, you will be able to:

• Understand the concept of Joins in MySQL.
• Identify the need for joining tables.
• Write SQL queries using different types of joins.
• Differentiate between INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN.
• Retrieve meaningful information from multiple related tables.

---

What is a JOIN?

---

Why Do We Need Joins?

Without JOIN	With JOIN
Data is stored in separate tables.	Related data from different tables can be displayed together.
Repeated information may be required.	Reduces data duplication.
Difficult to prepare combined reports.	Easy to generate meaningful reports.

---

Sample Table 1: Student

Student_ID	Name	Class
101	Aarav	XI
102	Diya	XI
103	Kabir	XI
104	Riya	XII

---

Sample Table 2: Result

Student_ID	Subject	Marks
101	Computer Science	91
102	Computer Science	84
103	Computer Science	88
105	Computer Science	79

---

Common Column Used for Joining

In the above tables, the column Student_ID is present in both tables. Therefore, it can be used as the common column to join the two tables.

---

Types of Joins in MySQL

Join Type	Description
INNER JOIN	Returns only matching records from both tables.
LEFT JOIN	Returns all records from the left table and matching records from the right table.
RIGHT JOIN	Returns all records from the right table and matching records from the left table.
CROSS JOIN	Returns all possible combinations of rows from both tables.

---

INNER JOIN

An INNER JOIN returns only those records where the values in the common column match in both tables.

Syntax

SELECT column_name(s)
FROM Table1
INNER JOIN Table2
ON Table1.common_column = Table2.common_column;

---

Example 1: Display Student Name and Marks

SELECT Student.Name,
       Result.Marks
FROM Student
INNER JOIN Result
ON Student.Student_ID = Result.Student_ID;

Output

Name	Marks
Aarav	91
Diya	84
Kabir	88

Notice that Student_ID 104 is not displayed because it has no matching record in the Result table. Similarly, Student_ID 105 is not displayed because it is not present in the Student table.

---

INNER JOIN with Multiple Columns

SELECT Student.Name,
       Result.Subject,
       Result.Marks
FROM Student
INNER JOIN Result
ON Student.Student_ID = Result.Student_ID;

This query retrieves the student's name, subject, and marks by combining information from both tables.

---

Advantages of INNER JOIN

• Returns only matching records.
• Removes unrelated data automatically.
• Produces accurate reports from multiple tables.
• Reduces duplication of information.
• Widely used in school management systems, banking, hospitals, and business applications.

---

Comparison: Simple SELECT vs INNER JOIN

Simple SELECT	INNER JOIN
Retrieves data from one table.	Retrieves related data from two or more tables.
No relationship between tables.	Uses a common column to establish a relationship.
Suitable for simple queries.	Suitable for relational database queries.

---

---

LEFT JOIN

A LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no matching record in the right table, NULL values are displayed for the columns of the right table.

Syntax

SELECT column_name(s)
FROM Table1
LEFT JOIN Table2
ON Table1.common_column = Table2.common_column;

---

Example 1: Display All Students with Their Marks

SELECT Student.Student_ID,
       Student.Name,
       Result.Marks
FROM Student
LEFT JOIN Result
ON Student.Student_ID = Result.Student_ID;

Output

Student_ID	Name	Marks
101	Aarav	91
102	Diya	84
103	Kabir	88
104	Riya	NULL

Since Student_ID 104 has no matching record in the Result table, MySQL displays NULL for the Marks column.

---

RIGHT JOIN

A RIGHT JOIN returns all records from the right table and only the matching records from the left table. If there is no matching record in the left table, NULL values are displayed for the columns of the left table.

Syntax

SELECT column_name(s)
FROM Table1
RIGHT JOIN Table2
ON Table1.common_column = Table2.common_column;

---

Example 2: Display All Results

SELECT Student.Name,
       Result.Student_ID,
       Result.Marks
FROM Student
RIGHT JOIN Result
ON Student.Student_ID = Result.Student_ID;

Output

Name	Student_ID	Marks
Aarav	101	91
Diya	102	84
Kabir	103	88
NULL	105	79

Student_ID 105 exists in the Result table but has no matching record in the Student table. Therefore, the Name column contains NULL.

---

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from both tables. It does not require any matching column.

Syntax

SELECT column_name(s)
FROM Table1
CROSS JOIN Table2;

---

Example 3

SELECT Student.Name,
       Result.Subject
FROM Student
CROSS JOIN Result;

If the Student table contains 4 records and the Result table contains 4 records, the CROSS JOIN produces:

4 × 4 = 16 Records

---

Comparison of Different Joins

Join Type	Records Returned
INNER JOIN	Only matching records.
LEFT JOIN	All records from the left table and matching records from the right table.
RIGHT JOIN	All records from the right table and matching records from the left table.
CROSS JOIN	All possible combinations of rows.

---

Solved Example 1

Display student names along with their marks.

Solution:

SELECT Student.Name,
       Result.Marks
FROM Student
INNER JOIN Result
ON Student.Student_ID = Result.Student_ID;

---

Solved Example 2

Display all students, including those whose marks are not available.

Solution:

SELECT Student.Name,
       Result.Marks
FROM Student
LEFT JOIN Result
ON Student.Student_ID = Result.Student_ID;

---

Solved Example 3

Display all examination results, even if student details are missing.

Solution:

SELECT Student.Name,
       Result.Marks
FROM Student
RIGHT JOIN Result
ON Student.Student_ID = Result.Student_ID;

---

Solved Example 4

Display every possible combination of students and subjects.

Solution:

SELECT Student.Name,
       Result.Subject
FROM Student
CROSS JOIN Result;

---

Real-Life Applications

Organization	Application	Join Used
School	Student details with examination marks	INNER JOIN
School	List all students, including absentees	LEFT JOIN
Hospital	Patient details with medical reports	INNER JOIN
Company	Employees with department details	LEFT JOIN
Bank	Customers with account information	INNER JOIN
Online Store	Products with category information	LEFT JOIN

---

Common Errors

Mistake	Correct Practice
Joining tables without a common column.	Always identify the related column before joining tables.
Using an incorrect ON condition.	Join using the appropriate matching columns.
Confusing LEFT JOIN with RIGHT JOIN.	Remember: LEFT JOIN returns all rows from the left table, while RIGHT JOIN returns all rows from the right table.
Using CROSS JOIN unintentionally.	Use CROSS JOIN only when every possible combination is required.
Ignoring NULL values in LEFT or RIGHT JOIN.	Handle NULL values appropriately in the result.

---

Interview Corner

---

---

Competency-Based Questions

1. A school stores student details in the Student table and examination marks in the Result table. Write an SQL query to display the student's name along with the marks using an INNER JOIN.
2. Write an SQL query to display all students, including those who have not yet received their examination results.
3. A database administrator wants to display all examination records, even if some student details are missing. Which JOIN should be used? Write the SQL query.
4. Differentiate between INNER JOIN and LEFT JOIN with suitable examples.
5. Explain why a common column is required while joining two tables.

---

Multiple Choice Questions

1. A JOIN is used to:
   • (a) Delete records
   • (b) Combine data from two or more related tables
   • (c) Create a new database
   • (d) Update records
2. Which JOIN returns only the matching records from both tables?
   • (a) LEFT JOIN
   • (b) INNER JOIN
   • (c) RIGHT JOIN
   • (d) CROSS JOIN
3. Which JOIN returns all records from the left table and matching records from the right table?
   • (a) LEFT JOIN
   • (b) INNER JOIN
   • (c) RIGHT JOIN
   • (d) CROSS JOIN
4. Which JOIN returns all records from the right table and matching records from the left table?
   • (a) LEFT JOIN
   • (b) INNER JOIN
   • (c) RIGHT JOIN
   • (d) CROSS JOIN
5. Which JOIN returns every possible combination of rows from both tables?
   • (a) INNER JOIN
   • (b) LEFT JOIN
   • (c) RIGHT JOIN
   • (d) CROSS JOIN
6. Which clause specifies the matching condition between two tables?
   • (a) WHERE
   • (b) ON
   • (c) ORDER BY
   • (d) GROUP BY
7. If there is no matching record in a LEFT JOIN, MySQL displays:
   • (a) 0
   • (b) Blank Space
   • (c) NULL
   • (d) FALSE
8. The common column used to join two tables is generally:
   • (a) Randomly selected
   • (b) A related key present in both tables
   • (c) Always the first column
   • (d) Always a text column
9. Which JOIN is generally used most frequently in real-world database applications?
   • (a) INNER JOIN
   • (b) CROSS JOIN
   • (c) RIGHT JOIN
   • (d) SELF JOIN
10. Which SQL query correctly displays student names and marks using an INNER JOIN?
    • (a) SELECT Name, Marks FROM Student;
    • (b) SELECT Name FROM Student, Result;
    • (c) SELECT Student.Name, Result.Marks FROM Student INNER JOIN Result ON Student.Student_ID = Result.Student_ID;
    • (d) SELECT Student JOIN Result;

---

Quick Revision

JOIN Type	Purpose
INNER JOIN	Returns only matching records from both tables.
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	Returns every possible combination of rows from both tables.
ON Clause	Specifies the condition used to match records.
NULL	Represents missing or unavailable data.

---

Important Points to Remember

• A JOIN combines data from two or more related tables.
• A common column (such as a Primary Key and Foreign Key) is required to establish a relationship between tables.
• INNER JOIN returns only the matching records.
• LEFT JOIN returns all records from the left table and matching records from the right table.
• RIGHT JOIN returns all records from the right table and matching records from the left table.
• CROSS JOIN returns every possible combination of rows from both tables and does not require a matching column.
• The ON clause specifies the matching condition between tables.
• NULL values appear when matching records are not available in LEFT JOIN or RIGHT JOIN operations.

---

CBSE Exam Tips

• Understand the purpose of each JOIN instead of memorizing only the syntax.
• Practice identifying the correct common column before writing a JOIN query.
• Remember that the ON clause is mandatory for INNER, LEFT, and RIGHT JOIN.
• Do not confuse LEFT JOIN with RIGHT JOIN. Focus on which table's records are always included.
• Use table names or aliases when two tables contain columns with the same name.
• Expect competency-based SQL questions based on school management, banking, hospital, library, and employee databases.

---

Summary

A JOIN is one of the most important features of relational databases. It allows data stored in different tables to be combined into a single result based on a common column. INNER JOIN returns only matching records, LEFT JOIN returns all rows from the left table, RIGHT JOIN returns all rows from the right table, and CROSS JOIN generates every possible combination of rows. Understanding joins enables you to retrieve meaningful information from multiple tables and write efficient SQL queries for real-world applications as well as the CBSE Class 11 Informatics Practices examination.

---

What's Next?

In the next chapter, you will learn SQL Constraints in MySQL. You will understand the purpose of constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK, along with practical examples and CBSE examination-oriented questions.