Blog · MySQL / SQL · 04 Jul 2026 · Hindi + English

Types of JOINs in SQL: INNER, LEFT, RIGHT, FULL (With Output)

One pair of tables, four JOINs, four different outputs shown side by side — so you finally see what INNER, LEFT, RIGHT and FULL actually keep and drop.

One pair of tables for everything

JOINs confuse people because every tutorial uses different tables. Here we fix that: ONE pair of tables, all four JOINs, four outputs side by side. Watch what each JOIN keeps and drops.

students                        marks
+---------+-------+            +---------+-------+
| roll_no | name  |            | roll_no | score |
|   101   | Aman  |            |   101   |  92   |
|   102   | Priya |            |   103   |  78   |
|   104   | Neha  |            +---------+-------+
+---------+-------+
-- Notice carefully:
-- 102 (Priya) and 104 (Neha) have NO marks entry
-- 103 has marks but NO student entry (data-entry mistake)

INNER JOIN — only the matches

SELECT s.name, m.score
FROM students s
INNER JOIN marks m ON s.roll_no = m.roll_no;

+------+-------+ | name | score | | Aman | 92 | +------+-------+

Only roll_no 101 exists in both tables, so only Aman appears. Priya, Neha (no marks) and 103 (no student) are all dropped. INNER = intersection.

LEFT JOIN — everyone from the left, matched or not

SELECT s.name, m.score
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no;

ALL students kept. Where no marks exist, SQL fills NULL. This is the most used JOIN in real work — "show all students, with marks if available". The NULLs also answer a classic question: find students who never appeared → add WHERE m.score IS NULL.

RIGHT JOIN — everyone from the right

SELECT s.name, m.score
FROM students s
RIGHT JOIN marks m ON s.roll_no = m.roll_no;

+------+-------+ | name | score | | Aman | 92 | | NULL | 78 | +------+-------+

ALL marks rows kept. The score 78 belongs to roll_no 103 who has no student record — name comes out NULL, instantly exposing the data-entry mistake. (In practice, most people rewrite RIGHT JOINs as LEFT JOINs by swapping table order.)

FULL JOIN — everyone from both sides

-- MySQL has NO FULL OUTER JOIN keyword! Simulate with UNION:
SELECT s.name, m.score FROM students s LEFT  JOIN marks m ON s.roll_no = m.roll_no
UNION
SELECT s.name, m.score FROM students s RIGHT JOIN marks m ON s.roll_no = m.roll_no;

MySQL trap: writing FULL OUTER JOIN in MySQL gives a syntax error — it simply doesn't exist there. The LEFT + UNION + RIGHT trick above is the standard workaround, and asking about it is a favourite way to check real MySQL experience.

The one-glance summary

JOIN	Keeps	Rows in our example	Typical question it answers
INNER	Matches only	1 (Aman)	Students who have marks
LEFT	All left + matches	3	All students, marks if any
RIGHT	All right + matches	2	All marks, student if any
FULL	Everything	4	Complete picture incl. mismatches

सब कुछ एक ही tables के pair पर

JOINs इसलिए confuse करते हैं क्योंकि हर tutorial अलग tables use करता है. यहां यह ठीक करते हैं: EK tables का pair, चारों JOINs, चार outputs साथ-साथ. देखिए हर JOIN क्या रखता है और क्या छोड़ता है.

students                        marks
+---------+-------+            +---------+-------+
| roll_no | name  |            | roll_no | score |
|   101   | Aman  |            |   101   |  92   |
|   102   | Priya |            |   103   |  78   |
|   104   | Neha  |            +---------+-------+
+---------+-------+
-- ध्यान से देखिए:
-- 102 (Priya) और 104 (Neha) की marks entry NAHI है
-- 103 के marks हैं पर student entry NAHI (data-entry गलती)

INNER JOIN — सिर्फ matches

SELECT s.name, m.score
FROM students s
INNER JOIN marks m ON s.roll_no = m.roll_no;

+------+-------+ | name | score | | Aman | 92 | +------+-------+

सिर्फ roll_no 101 दोनों tables में है, इसलिए सिर्फ Aman आया. Priya, Neha (marks नहीं) और 103 (student नहीं) सब drop. INNER = intersection.

LEFT JOIN — left के सब, match हो या न हो

SELECT s.name, m.score
FROM students s
LEFT JOIN marks m ON s.roll_no = m.roll_no;

SAARE students रखे गए. जहां marks नहीं, SQL NULL भरता है. Real काम में यही सबसे ज़्यादा used JOIN है — "सारे students दिखाओ, marks हों तो साथ में". NULLs से classic question भी solve होता है: कौन से students कभी exam में नहीं बैठे → WHERE m.score IS NULL जोड़ दो.

RIGHT JOIN — right के सब

SELECT s.name, m.score
FROM students s
RIGHT JOIN marks m ON s.roll_no = m.roll_no;

+------+-------+ | name | score | | Aman | 92 | | NULL | 78 | +------+-------+

SAARI marks rows रखी गईं. Score 78 roll_no 103 का है जिसकी student record नहीं — name NULL निकला, data-entry गलती तुरंत बेनकाब. (Practice में ज़्यादातर लोग table order बदलकर RIGHT को LEFT JOIN में लिख लेते हैं.)

FULL JOIN — दोनों तरफ के सब

-- MySQL में FULL OUTER JOIN keyword hai hi NAHI! UNION से simulate करें:
SELECT s.name, m.score FROM students s LEFT  JOIN marks m ON s.roll_no = m.roll_no
UNION
SELECT s.name, m.score FROM students s RIGHT JOIN marks m ON s.roll_no = m.roll_no;

MySQL trap: MySQL में FULL OUTER JOIN लिखने पर syntax error आता है — वहां यह है ही नहीं. ऊपर वाली LEFT + UNION + RIGHT trick standard workaround है, और इसके बारे में पूछना real MySQL experience check करने का favourite तरीका है.

एक नज़र में summary

JOIN	रखता है	हमारे example में rows	किस सवाल का जवाब
INNER	सिर्फ matches	1 (Aman)	जिन students के marks हैं
LEFT	सारा left + matches	3	सारे students, marks हों तो साथ
RIGHT	सारा right + matches	2	सारे marks, student हो तो साथ
FULL	सब कुछ	4	Mismatches समेत पूरी picture

Frequently Asked Questions

What are the types of JOINs in SQL?

INNER JOIN (matching rows only), LEFT JOIN (all left rows plus matches), RIGHT JOIN (all right rows plus matches) and FULL OUTER JOIN (everything from both sides, simulated in MySQL with LEFT UNION RIGHT).

Does MySQL support FULL OUTER JOIN?

No, MySQL has no FULL OUTER JOIN keyword; the standard workaround is combining a LEFT JOIN and a RIGHT JOIN of the same tables with UNION.

How to find students who have no marks using a JOIN?

Use a LEFT JOIN from students to marks and filter WHERE marks.score IS NULL — the NULL rows are exactly the unmatched students.