SQL Joins: Inner, Left, Right, Full Outer, Cross, and Self Joins

Why Do We Need Joins in SQL? (In Simple Words)

In a normalized database, data is deliberately split across multiple related tables to prevent redundancy (e.g., student details in Student and department details in Department).

When an application needs to display a unified report (e.g., “Show the student’s name along with their department name”), we use a JOIN clause to reassemble the data on the fly.

1. Visual Overview of SQL Joins

Sample Data Used for Examples:

Table A: Student

Table B: Department

2. INNER JOIN (The Default Join)

Returns only records that have matching values in both tables:

• Notice that Sneha (Dept_ID is NULL) and Commerce (no students enrolled) are both excluded!

SELECT 
    Student.Roll_No,
    Student.Student_Name,
    Department.Dept_Name
FROM Student
INNER JOIN Department 
    ON Student.Dept_ID = Department.Dept_ID;

Result:

3. LEFT OUTER JOIN (or LEFT JOIN)

Returns all rows from the left table (Student), even if there are no matching rows in the right table (Department). Missing right-side values appear as NULL:

SELECT 
    Student.Roll_No,
    Student.Student_Name,
    Department.Dept_Name
FROM Student
LEFT JOIN Department 
    ON Student.Dept_ID = Department.Dept_ID;

Result:

(Sneha is preserved even though she has no department assigned!).

4. RIGHT OUTER JOIN (or RIGHT JOIN)

Returns all rows from the right table (Department), even if no student from the left table belongs to it:

SELECT 
    Student.Roll_No,
    Student.Student_Name,
    Department.Dept_Name
FROM Student
RIGHT JOIN Department 
    ON Student.Dept_ID = Department.Dept_ID;

Result:

(Commerce is preserved even though zero students belong to it!).

5. FULL OUTER JOIN

Combines the results of both Left and Right joins. It returns all rows from both tables, filling NULL wherever a match does not exist:

SELECT 
    Student.Student_Name,
    Department.Dept_Name
FROM Student
FULL OUTER JOIN Department 
    ON Student.Dept_ID = Department.Dept_ID;

Result:

• Shows Aman, Priya, Rohan (matched).
• Shows Sneha with NULL department.
• Shows Commerce with NULL student.

6. CROSS JOIN (Cartesian Product in SQL)

Combines every single row of the first table with every single row of the second table without any ON condition:

• If Table A has 4 rows and Table B has 3 rows: Output has 4 × 3 = 12 rows.

SELECT * FROM Student CROSS JOIN Department;

7. Self Join (Joining a Table with Itself)

A Self Join is a regular join where a table is joined with itself using table aliases. It is used to query hierarchical data stored in a single table (like an Employee-Manager hierarchy):

Table: Employee (Emp_ID, Emp_Name, Manager_ID)

SELECT 
    E.Emp_Name AS Employee_Name,
    M.Emp_Name AS Manager_Name
FROM Employee E
LEFT JOIN Employee M 
    ON E.Manager_ID = M.Emp_ID;

💡 DAVV Exam Preparation Tips

5-Mark Question: “Differentiate between INNER JOIN and LEFT OUTER JOIN with a small example.”

• Define INNER JOIN (only matching tuples).
• Define LEFT JOIN (all left-table rows preserved, unmatched right fields are NULL).
• Provide the 2-table Student-Department output comparison.

10-Mark Question: “Explain all types of joins in SQL: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN. Write SQL queries and show output tables for each.”

• Present the Student and Department sample dataset.
• Write out the explicit SQL query for all 6 join types.
• Display the resulting output table for each query clearly highlighting NULL positions.