SQL Joins in Hindi | INNER, LEFT, RIGHT, FULL Join Explained

INNER JOIN  →  A ∩ B   (Only common rows)
LEFT JOIN   →  A ∪ (A ∩ B)  (All A + matching B)
RIGHT JOIN  →  B ∪ (A ∩ B)  (All B + matching A)
FULL JOIN   →  A ∪ B   (All rows from both tables)
    

Examples are simplified for teaching. For real projects, test queries on a copy and review explain plans. Content prepared by Vista Academy — instructors with industry experience in data analytics and SQL.

Choose the best answer. Click “Show Answer” to reveal explanation. Use this in-class or as self-practice.

1. 1. INNER JOIN क्या return करता है?

   A) Left table के सभी rows
   B) Right table के सभी rows
   C) दोनों tables के केवल matching rows
   D) दोनों tables के सभी rows

   Show Answer
   Answer: C — INNER JOIN केवल दोनों tables में common records return करता है।
2. 2. LEFT JOIN किसे return करेगा?

   A) केवल matching records
   B) Left table के सभी rows और matching right rows
   C) Right table के सभी rows
   D) दोनों tables के सभी rows

   Show Answer
   Answer: B — LEFT JOIN left table के सभी rows और right के matching rows return करेगा।
3. 3. MySQL में FULL OUTER JOIN कैसे करें?

   A) FULL JOIN keyword use करके
   B) LEFT JOIN और RIGHT JOIN का UNION करके
   C) CROSS JOIN करके
   D) INNER JOIN करके

   Show Answer
   Answer: B — MySQL में FULL OUTER JOIN को LEFT + RIGHT JOIN से UNION करके बनाया जाता है।
4. 4. RIGHT JOIN का result किस table के सभी rows show करेगा?

   A) Left table
   B) Right table
   C) दोनों tables
   D) None

   Show Answer
   Answer: B — RIGHT JOIN right table के सभी rows show करेगा।
5. 5. अगर rows duplicate चाहिए तो UNION की जगह क्या use करें?

   A) UNION ALL
   B) INTERSECT
   C) EXCEPT
   D) DISTINCT

   Show Answer
   Answer: A — UNION duplicates remove करता है; UNION ALL duplicates रखेगा।
6. 6. JOIN के condition में किसे use करना चाहिए performance के लिए?

   A) Non-indexed column
   B) Indexed column
   C) Large text columns
   D) Random functions on column

   Show Answer
   Answer: B — Indexed column use करें — इससे JOIN fast चलेगा।
7. 7. INNER JOIN और LEFT JOIN में difference क्या है?

   A) कोई difference नहीं
   B) INNER only matching, LEFT includes all left rows
   C) LEFT only matching, INNER includes all left rows
   D) दोनों same result देते हैं

   Show Answer
   Answer: B — INNER सिर्फ matching rows देता है; LEFT left table के सभी rows देता है।
8. 8. NULL values JOIN results में आना normal है — इन्हें handle करने के लिए कौन सा function use होता है?

   A) COALESCE()
   B) TO_CHAR()
   C) ROUND()
   D) TRIM()

   Show Answer
   Answer: A — COALESCE() से NULL values को default value दे सकते हैं।
9. 9. नीचे में से कौन सा SQL clause join condition define करता है?

   A) WHERE
   B) ON
   C) GROUP BY
   D) ORDER BY

   Show Answer
   Answer: B — JOIN condition को ON clause में define करते हैं।
10. 10. अगर आपको दोनों tables का combined result (including non-matching) चाहिए तो किस JOIN का use होगा?

    A) INNER JOIN
    B) LEFT JOIN
    C) RIGHT JOIN
    D) FULL OUTER JOIN

    Show Answer
    Answer: D — FULL OUTER JOIN दोनों tables के सभी rows return करेगा।

नीचे हर task के साथ expected output और solution query भी दिया गया है — copy-paste करके अपने DB में test करें. Tables used are students(id,name) और marks(id,marks) unless otherwise noted.

1. 1) Inner Join — नाम और marks दिखाइये (only matched)
   Expected rows: Rohan(85), Amit(78)

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

2. 2) Left Join — सभी students और उनका marks (missing -> 0)
   Expected rows: Rohan(85), Sia(0), Amit(78)

   SELECT s.id, s.name, COALESCE(m.marks,0) AS marks FROM students s LEFT JOIN marks m ON s.id = m.id;

3. 3) Right Join — सभी marks और student नाम (missing -> ‘Unknown’)
   Expected rows: Rohan(85), Amit(78), (90 -> Unknown)

   SELECT m.id, COALESCE(s.name,'Unknown') AS name, m.marks FROM students s RIGHT JOIN marks m ON s.id = m.id;

4. 4) Full Outer (MySQL) — combined rows using UNION
   Expected rows: ids 1,2,3,4 with appropriate names/marks

   SELECT s.id, s.name, m.marks FROM students s LEFT JOIN marks m ON s.id = m.id
   UNION
   SELECT s.id, s.name, m.marks FROM students s RIGHT JOIN marks m ON s.id = m.id;

5. 5) Find students without marks (use LEFT and WHERE)
   Expected: Sia

   SELECT s.id, s.name FROM students s LEFT JOIN marks m ON s.id = m.id WHERE m.id IS NULL;

6. 6) Students who scored >80 (JOIN + WHERE)
   Expected: Rohan(85)

   SELECT s.id, s.name, m.marks FROM students s JOIN marks m ON s.id = m.id WHERE m.marks > 80;

7. 7) Students with no student record but have marks (use RIGHT and WHERE)
   Expected: id 4 with marks 90 (name NULL)

   SELECT m.id, m.marks FROM students s RIGHT JOIN marks m ON s.id = m.id WHERE s.id IS NULL;

8. 8) Create report: all students and a status column ‘HasMark’ (YES/NO)
   Expected rows: Rohan(YES), Sia(NO), Amit(YES)

   SELECT s.id, s.name, CASE WHEN m.id IS NULL THEN 'NO' ELSE 'YES' END AS HasMark
   FROM students s LEFT JOIN marks m ON s.id = m.id;

9. 9) Top scorer list with student names (JOIN + ORDER BY)
   Expected: id4(90), id1(85), id3(78)

   SELECT COALESCE(s.name,'Unknown') AS name, m.marks FROM marks m LEFT JOIN students s ON s.id = m.id ORDER BY m.marks DESC;

10. 10) Use UNION ALL to show duplicate-preserving full result
    Expected: same as full outer but duplicates preserved if any

    SELECT s.id, s.name, m.marks FROM students s LEFT JOIN marks m ON s.id = m.id
    UNION ALL
    SELECT s.id, s.name, m.marks FROM students s RIGHT JOIN marks m ON s.id = m.id;

11. 11) Self Join: Employees table — list employee and their manager name
    Assume employees(id,name,manager_id). Expected: employee + manager (NULL if top)

    SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

12. 12) Cross Join: Generate all combinations (small sets)
    Use carefully — expected rows = product of counts

    SELECT a.val AS a, b.val AS b FROM setA a CROSS JOIN setB b;

13. 13) Join with aggregation: average marks per student (handle NULL as 0)
    Expected: avg per student (useful if multiple marks rows exist)

    SELECT s.id, s.name, COALESCE(AVG(m.marks),0) AS avg_marks FROM students s LEFT JOIN marks m ON s.id = m.id GROUP BY s.id, s.name;

14. 14) Find students having marks entry but name shows ‘Unknown’ — show their ids
    Expected: id 4

    SELECT m.id FROM marks m LEFT JOIN students s ON s.id = m.id WHERE s.id IS NULL;

15. 15) Performance task: add index and explain — create index on marks(id) and analyze
    Expectation: faster joins after index (run EXPLAIN to compare).

    CREATE INDEX idx_marks_id ON marks(id);
    -- Then run EXPLAIN SELECT s.id, s.name, m.marks FROM students s JOIN marks m ON s.id = m.id;

Need solutions for a different schema (Orders/Customers/Products)? Reply with the table names and sample rows — I will craft customized assignments and solutions.

इस section में हम Section 4 के कुछ महत्वपूर्ण problems को step-by-step solve करेंगे, EXPLAIN output कैसे पढ़ें, और performance कैसे सुधारें — practical tips के साथ।

EXPLAIN SELECT s.id, s.name, m.marks FROM students s INNER JOIN marks m ON s.id = m.id;