Home › SQL Tutorial › SQL Join

SQL Join

⏱ 6 min read Updated: 04 Nov 2026

SQL Joins are used to retrieve data from two or more tables based on a related column between them. They help us to combine information stored in different tables into a single result. In a relational database, data is divided into multiple tables to avoid duplication and maintain better organization. For example, a database may have separate tables for students and courses. SQL uses the joins function to retrieve student details along with their course information in one query.

In this article, we will learn about different types of SQL Joins, their syntax, examples, and differences.

What are Joins in SQL?

A Join in SQL is a clause used to combine rows from two or more tables based on a common column or a specified condition. For example, consider two tables: Students and Courses. The Students table contains student information, while the Courses table stores course details. Both tables have a common column called CourseID.

Why are SQL Joins Used?

SQL Joins are useful for the following reasons:

  • Combine Data: It is used to retrieve related information from multiple tables in a single query.
  • Reduce Data Duplication: It is used to store related information in separate tables and combine it when required.
  • Retrieve Related Records: It is used to find records that have matching values in different tables.
  • Generate Reports: It is used to create meaningful reports using information from multiple database tables.
  • Simplify Queries: It avoids writing multiple separate queries to retrieve related information.

Types of Joins in SQL

SQL supports different types of Joins to retrieve data according to specific requirements.

The main types of SQL Joins are:

  1. INNER JOIN
  2. LEFT JOIN (LEFT OUTER JOIN)
  3. RIGHT JOIN (RIGHT OUTER JOIN)
  4. FULL JOIN (FULL OUTER JOIN)
  5. CROSS JOIN
  6. SELF JOIN

Let's understand each type with examples.

1. INNER JOIN in SQL

An INNER JOIN returns only those records that have matching values in both tables. If a record does not have a matching value in the other table, it will not appear in the result.

Syntax of INNER JOIN

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

Example of INNER JOIN

Consider the following two tables.

Students Table

StudentIDStudentNameCourseID
1Rahul101
2Priya102
3Amit103
4Neha104

Courses Table

CourseIDCourseName
101Java
102Python
103SQL
105PHP

Now, we use the following query to retrieve student names along with their course names.

SELECT    Students.StudentName,    Courses.CourseName FROM Students INNER JOIN Courses ON Students.CourseID = Courses.CourseID;

Output:

StudentNameCourseName
RahulJava
PriyaPython
AmitSQL

Explanation: 

The INNER JOIN compares the CourseID column in both tables. It returns only the records where the CourseID matches. Neha is excluded because CourseID 104 does not exist in the Courses table.

2. LEFT JOIN in SQL

A LEFT JOIN returns all records from the left table and matching records from the right table. If no matching record exists in the right table, SQL returns NULL for its columns. LEFT JOIN is also known as LEFT OUTER JOIN.

Syntax of LEFT JOIN

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

Example of LEFT JOIN

Using the same Students and Courses tables, execute the following query:

SELECT    Students.StudentName,    Courses.CourseName FROM Students LEFT JOIN Courses ON Students.CourseID = Courses.CourseID;

Output:

StudentNameCourseName
RahulJava
PriyaPython
AmitSQL
NehaNULL

Explanation: The LEFT JOIN returns every record from the Students table. It also retrieves matching course information from the Courses table. Since Neha's CourseID does not have a match, the CourseName column displays NULL.

3. RIGHT JOIN in SQL

A RIGHT JOIN returns all records from the right table and matching records from the left table. If there is no matching record in the left table, SQL displays NULL for the corresponding columns. RIGHT JOIN is also known as RIGHT OUTER JOIN.

Syntax of RIGHT JOIN

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

Example of RIGHT JOIN

Execute the following query:

SELECT    Students.StudentName,    Courses.CourseName FROM Students RIGHT JOIN Courses ON Students.CourseID = Courses.CourseID;

Output:

StudentNameCourseName
RahulJava
PriyaPython
AmitSQL
NULLPHP

Explanation: The RIGHT JOIN returns all records from the Courses table, including PHP, which does not have a matching student. In this case, the StudentName column displays NULL.

4. FULL JOIN in SQL

A FULL JOIN returns all records from both tables. It includes matching records as well as non-matching records from either table. If a record does not have a match in the other table, SQL displays NULL for the missing values. FULL JOIN is also known as FULL OUTER JOIN.

Syntax of FULL JOIN

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;

Example of FULL JOIN

SELECT    Students.StudentName,    Courses.CourseName FROM Students FULL OUTER JOIN Courses ON Students.CourseID = Courses.CourseID;

Output:

StudentNameCourseName
RahulJava
PriyaPython
AmitSQL
NehaNULL
NULLPHP

Explanation: 

The FULL JOIN returns all records from both Students and Courses tables. Matching records are combined, while unmatched records are also included with NULL values.

Note: MySQL does not directly support FULL OUTER JOIN. You can achieve similar results using LEFT JOIN and RIGHT JOIN with UNION.

5. CROSS JOIN in SQL

A CROSS JOIN returns the Cartesian product of two tables. It combines every row of the first table with every row of the second table. For example, if the first table contains 3 records and the second table contains 2 records, the CROSS JOIN produces 6 rows.

Syntax of CROSS JOIN

SELECT columns FROM table1 CROSS JOIN table2;

Example of CROSS JOIN

Consider the following tables:

Students Table

StudentIDStudentName
1Rahul
2Priya

Courses Table

CourseIDCourseName
101Java
102Python

Execute the following query:

SELECT    Students.StudentName,    Courses.CourseName FROM Students CROSS JOIN Courses;

Output:

StudentNameCourseName
RahulJava
RahulPython
PriyaJava
PriyaPython

Explanation: 

The CROSS JOIN combines every student with every available course. Since there are 2 students and 2 courses, the result contains 4 rows.

6. SELF JOIN in SQL

A SELF JOIN is used to join a table with itself. It is useful when records in the same table are related to each other. For example, an Employees table may contain employee details along with their manager IDs. Since managers are also employees, we can use a SELF JOIN to display employee names and their respective manager names.

Syntax of SELF JOIN

SELECT columns FROM table1 AS A JOIN table1 AS B ON A.common_column = B.common_column;

Example of SELF JOIN

Consider the following Employees table:

EmployeeIDEmployeeNameManagerID
1AmitNULL
2Rahul1
3Priya1
4Neha2

Now, use the following query:

SELECT    E.EmployeeName AS Employee,    M.EmployeeName AS Manager FROM Employees E LEFT JOIN Employees M ON E.ManagerID = M.EmployeeID;

Output:

EmployeeManager
AmitNULL
RahulAmit
PriyaAmit
NehaRahul

Explanation: 

The Employees table is joined with itself using two aliases: E and M. The first alias represents employees, while the second represents their managers. The LEFT JOIN also includes employees who do not have a manager.

Difference Between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN

The following table explains the main differences between these SQL Joins:

Join TypeDescription
INNER JOINIt returns only matching records from both tables.
LEFT JOINIt returns all records from the left table and matching records from the right table.
RIGHT JOINIt returns all records from the right table and matching records from the left table.