What is the difference between inner join, left join, right join in SQL?

✅ 1. INNER JOIN

Definition:

Returns **only the matching rows** between two tables.

Meaning:

If a record exists in both Table A and Table B → it will appear in the result.

Example:

SELECT *
FROM A
INNER JOIN B
ON A.id = B.id;

✔ You get:

• Rows where A.id = B.id
• No unmatched records from A or B

✅ 2. LEFT JOIN (LEFT OUTER JOIN)

Definition:

Returns all rows from the left table, and the matching rows from the right table.

Meaning:

If a record exists in A but not in B → it will still appear, with NULL values from B.

Example:

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id;

✔ You get:

• All rows from A
• Matching rows from B
• Non-matching rows from B appear as NULL

✅ 3. RIGHT JOIN (RIGHT OUTER JOIN)

Definition:

Returns all rows from the right table, and matching rows from the left table.

Meaning:

If a record exists in B but not in A → it will still appear, with NULL values from A.

Example:

SELECT *
FROM A
RIGHT JOIN B
ON A.id = B.id;

✔ You get:

• All rows from B
• Matching rows from A
• Non-matching rows from A appear as NULL

🎨 Visual Summary (Easy to Remember)

Join TypeRows Returned**INNER JOINOnly A ∩ B (matching rows only)LEFT JOINAll A + matching B + NULL for missing BRIGHT JOIN**All B + matching A + NULL for missing A

📌 When to Use Which?

Use INNER JOIN when:

You want only records that exist in both tables. ✔ Best for clean comparisons.

Use LEFT JOIN when:

You want everything from the main (left) table, even if it has no match. ✔ Best for reporting, **data completeness**, hierarchy tables.

Use RIGHT JOIN when:

Same logic as LEFT JOIN, but you want all from the right table. ✔ Not commonly used — LEFT JOIN is preferred.

📘 Real-life Example

Assume:

Table A: Students

idname1John2Sara3Mike

Table B: Marks

idmarks190380470

✔ INNER JOIN

Returns only matching IDs (1 and 3):

idnamemarks1John903Mike80

✔ LEFT JOIN

All students + marks where available:

idnamemarks1John902SaraNULL3Mike80

✔ RIGHT JOIN

All marks + student names where available:

idnamemarks1John903Mike804NULL70