Chapter 7SQL Tutorial~2 min read

JOINs — Multiple Tables

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

Real applications मध्ये data multiple tables मध्ये organized असतो. JOIN वापरून tables एकत्र query करतात. INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN — प्रत्येकाचे वेगळे use cases.

Marathi Analogy

JOIN म्हणजे दोन registers एकत्र करणे. Students register (name, city) आणि Results register (marks, subject). JOIN केल्यावर: Rahul — Pune — Maths — 85. Common column (student_id) वापरून match करतात.

Sample tables setup

sql

-- Students table
CREATE TABLE students (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    city VARCHAR(50)
);

-- Enrollments table
CREATE TABLE enrollments (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course     VARCHAR(100),
    marks      INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students VALUES
    (1, 'Rahul',  'Pune'),
    (2, 'Priya',  'Mumbai'),
    (3, 'Amit',   'Nagpur'),
    (4, 'Sneha',  'Pune');     -- no enrollment

INSERT INTO enrollments VALUES
    (1, 1, 'Python',  85),
    (2, 1, 'React',   90),
    (3, 2, 'Python',  92),
    (4, 3, 'Java',    78),
    (5, 5, 'SQL',     88);    -- student_id=5 नाही students table मध्ये

INNER JOIN — दोन्हींमध्ये match

INNER JOIN

sql

-- फक्त दोन्ही tables मध्ये match असलेले rows
SELECT s.name, s.city, e.course, e.marks
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;

-- Result:
-- Rahul | Pune   | Python | 85
-- Rahul | Pune   | React  | 90
-- Priya | Mumbai | Python | 92
-- Amit  | Nagpur | Java   | 78
-- Sneha (no enrollment) — नाही दिसत
-- enrollment id=5 (no student) — नाही दिसत

LEFT JOIN — Left table सगळे

LEFT JOIN — NULL with unmatched

sql

-- सगळे students — enrollment असो वा नसो
SELECT s.name, s.city, e.course, e.marks
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

-- Result:
-- Rahul | Pune   | Python | 85
-- Rahul | Pune   | React  | 90
-- Priya | Mumbai | Python | 92
-- Amit  | Nagpur | Java   | 78
-- Sneha | Pune   | NULL   | NULL  ← enrolled नाही, पण दिसतो

-- Enrolled नाही असे students शोधणे
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;
-- Sneha

RIGHT JOIN आणि Self JOIN

RIGHT JOIN and SELF JOIN

sql

-- RIGHT JOIN — Right table सगळे
SELECT s.name, e.course, e.marks
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;
-- enrollment id=5 (no student) — NULL name सह दिसतो

-- SELF JOIN — एकच table स्वतःशी join
-- Example: employees आणि त्यांचे managers (same table!)
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INT  -- same table चा reference
);

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

✅ Key Points — लक्षात ठेवा

▸INNER JOIN — दोन्ही tables match — most common
▸LEFT JOIN — left सगळे + right match (NULL for no match)
▸RIGHT JOIN — right सगळे + left match
▸ON s.id = e.student_id — join condition
▸Table alias (s, e) — long names साठी shortcut

0/10 chapters पूर्ण

मागे — Previous

Aggregate Functions & GROUP BY

पुढे — Next

Subqueries