SQL Joins Explained: Fast Revision Notes for IBPS SO IT Officer 2026

SQL Joins are one of the most important DBMS topics for competitive exams and technical interviews. Questions are usually conceptual and require you to identify which rows will appear in the result after applying a specific JOIN.

Instead of memorizing definitions, learn what each JOIN returns. Once you understand that, solving SQL Join questions becomes much easier.

Quick Revision Cheat Sheet

Why Are SQL Joins Needed?

In a relational database, related information is stored in separate tables to reduce redundancy.

For example:

• Employee details are stored in one table.
• Department details are stored in another table.

To display an employee along with the department name, SQL combines data from both tables using a JOIN.

Sample Tables

We'll use these two tables throughout the article, so it's worth keeping them in mind as you read.

Employee

Department

Notice the mismatch on purpose: Amit (DeptID 30) and Sneha (no DeptID) don't have a matching department, and Finance (DeptID 40) has no employee. This mismatch is exactly what makes the different JOIN types behave differently — keep an eye on these four "odd ones out" as we go.

Click to enlarge

Understanding How a JOIN Works

Every JOIN follows the same two-step logic — only the final "keep or discard" rule changes.

   Employee Table  ──┐
                      ├──►  Compare on Employee.DeptID = Department.DeptID  ──►  Apply JOIN rule  ──►  Result
   Department Table ─┘

Everything below is really just six different answers to "what do we keep in step 2?"

The Six JOIN Types — Set View

Before the details, here's how each JOIN maps to the three possible regions: rows only in the left table, rows that match in both, and rows only in the right table.

Keep this table in mind — every join below is just one row of it, explained in detail.

1. INNER JOIN

An INNER JOIN returns only the rows where the matching condition is satisfied in both tables.

Syntax

SELECT *
FROM Employee
INNER JOIN Department
ON Employee.DeptID = Department.DeptID;

Set Diagram

   Employee            Department
 ┌───────────┐       ┌───────────┐
 │ Amit      │       │           │      Only the overlapping
 │ Sneha     │▓▓▓▓▓▓▓│ Finance   │      region (Rahul→HR,
 │           │▓Rahul▓│           │      Priya→IT) is returned.
 │           │▓Priya▓│           │
 └───────────┘       └───────────┘

Result

Only matching rows appear. Employees without a matching department are excluded, and departments without employees are excluded too.

Remember

INNER = Intersection

2. LEFT JOIN

A LEFT JOIN returns:

• Every row from the left table
• Matching rows from the right table

If there is no matching row, SQL fills the right-side columns with NULL.

Syntax

SELECT *
FROM Employee
LEFT JOIN Department
ON Employee.DeptID = Department.DeptID;

Set Diagram

   Employee (ALL kept)   Department
 ┌───────────────────┐  ┌───────────┐
 │▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓│  │           │
 │▓ Amit  → NULL     ▓│  │ Finance   │  (not returned —
 │▓ Sneha → NULL     ▓│  │           │   no employee matches it)
 │▓ Rahul → HR       ▓│▓▓│           │
 │▓ Priya → IT       ▓│▓▓│           │
 └───────────────────┘  └───────────┘

Result

All employees are included, even when no matching department exists.

Memory Trick

LEFT = Everything on the LEFT + matches from the RIGHT

3. RIGHT JOIN

A RIGHT JOIN works exactly like a LEFT JOIN, but the roles of the tables are reversed. It returns:

• Every row from the right table
• Matching rows from the left table

Syntax

SELECT *
FROM Employee
RIGHT JOIN Department
ON Employee.DeptID = Department.DeptID;

Set Diagram

   Employee              Department (ALL kept)
 ┌───────────┐         ┌───────────────────────┐
 │ Amit      │         │▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓│
 │ Sneha     │         │▓ NULL   ← Finance     ▓│
 │           │▓▓▓▓▓▓▓▓▓│▓ Rahul  → HR          ▓│
 │           │▓▓▓▓▓▓▓▓▓│▓ Priya  → IT          ▓│
 └───────────┘         └───────────────────────┘

Result

The Finance department appears even though no employee belongs to it.

Memory Trick

RIGHT = Everything on the RIGHT + matches from the LEFT

4. FULL OUTER JOIN

A FULL OUTER JOIN returns:

• All matching rows
• All non-matching rows from the left table
• All non-matching rows from the right table

If no match exists, SQL fills the missing columns with NULL.

Syntax

SELECT *
FROM Employee
FULL OUTER JOIN Department
ON Employee.DeptID = Department.DeptID;

Set Diagram

   Employee (ALL)        Department (ALL)
 ┌───────────────────┐  ┌───────────────────┐
 │▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓│  │▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓│
 │▓ Amit  → NULL     ▓│  │▓ NULL  ← Finance  ▓│
 │▓ Sneha → NULL     ▓│  │▓                  ▓│
 │▓ Rahul → HR       ▓│▓▓│▓                  ▓│
 │▓ Priya → IT       ▓│▓▓│▓                  ▓│
 └───────────────────┘  └───────────────────┘
       Nothing is left out on either side

Result

Memory Trick

FULL = Left + Right, everything

5. CROSS JOIN

A CROSS JOIN returns the Cartesian Product of two tables — every row from the first table is paired with every row from the second table. There's no matching condition involved at all.

Syntax

SELECT *
FROM Employee
CROSS JOIN Department;

Row Count

Sample Result (first 6 of 12 rows)

Memory Trick

CROSS = Multiply rows (no matching, no filtering)

6. SELF JOIN

A SELF JOIN joins a table with itself. It is mainly used when rows within the same table are related to each other.

Typical examples:

• Employee → Manager
• Parent → Child
• Teacher → Mentor

Example

SELECT
    e.Name AS Employee,
    m.Name AS Manager
FROM Employee e
LEFT JOIN Employee m
ON e.ManagerID = m.EmpID;

How It Works

        Employee (as "e")            Employee (as "m")
        every employee row    ──►    matched against itself
                                       to find their Manager row

A SELF JOIN is not a separate JOIN type — it's simply any of the JOINs above (usually LEFT or INNER), applied to a table paired with itself, using table aliases (e, m) to tell the two copies apart.

SQL Joins Comparison Table

SQL Joins at a Glance

                         SQL JOINS
                             │
          ┌──────────────────┼──────────────────┐
          │                  │                  │
       INNER               OUTER              SPECIAL
    (intersection)            │             ┌────┴────┐
                    ┌─────────┼─────────┐   │         │
                    │         │         │  CROSS     SELF
                  LEFT      RIGHT      FULL  (product) (same table)
               (left + match) (right + match) (everything)

Click to enlarge

Common Exam Traps

One-Page Revision Sheet

Quick Memory Tricks

Click to enlarge

What to Study Next

Complete your SQL revision with these related articles.