Theory
Names live in one table, scores in another
The principal asks the simplest question yet: "show me student names with their DBMS scores."
Problem: names live in students, scores live in marks. Neither table alone can answer.
Splitting data this way was BCA105's normalization lesson doing its job (no repetition). But split data must be stitched back at query time, matching rows through the shared roll column. Stitching is called joining, and there is a small family of joins, each answering a different question.
Theory
Matching two registers by roll number
One register lists admissions (roll, name, city). Another lists exam entries (roll, subject, score).
A clerk answering the principal walks the admissions register and, for each roll, looks up matching lines in the exam register. Found: staple them together. The join family differs only in what the clerk does when a roll has no match: skip it, keep it with blanks, or pair everything regardless.
At a glance
The join family at a glance
| Join | Keeps | Typical question |
|---|---|---|
| INNER | Only matching pairs | Names with their scores |
| LEFT | ALL left rows; NULLs fill gaps | Include students with no marks |
| CROSS | Every pair (m × n rows) | Every student × every subject grid |
| SELF | Table matched to itself (aliases) | Pairs of students from one city |
| FULL OUTER | Unmatched rows of BOTH sides | Everything, matched or not |
Practical
The family, on college.db
-- INNER: names with scores (unmatched students vanish)
SELECT s.name, m.subject, m.score
FROM students s
INNER JOIN marks m ON s.roll = m.roll;
-- LEFT: EVERY student, NULL score if they never sat an exam
SELECT s.name, m.score
FROM students s
LEFT JOIN marks m ON s.roll = m.roll;
-- the classic use of LEFT: who has NO marks at all?
SELECT s.name
FROM students s
LEFT JOIN marks m ON s.roll = m.roll
WHERE m.roll IS NULL;
-- CROSS: every student paired with every subject (a blank grid)
SELECT s.name, sub.subject
FROM students s CROSS JOIN subjects sub;
-- SELF: pairs of students from the same city (aliases a, b)
SELECT a.name, b.name, a.city
FROM students a
JOIN students b ON a.city = b.city AND a.roll < b.roll;
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Theory
Reading the two tricky ones
SELF JOIN: one table, used twice under two aliases (a and b), because SQL needs different names to tell the copies apart. The a.roll < b.roll trick stops each pair appearing twice and stops students pairing with themselves.
FULL OUTER JOIN: keeps unmatched rows from both sides. For years SQLite famously lacked it (textbook answer: emulate with a LEFT JOIN each way, UNIONed); versions from 2022 support it natively. In an exam, state both facts and you are safe either way.
Quiz
students has 3 rows, marks has 4 rows, and one student has no marks at all. How many rows do INNER JOIN and LEFT JOIN (students LEFT JOIN marks, ON roll) each return?
- INNER: 4, LEFT: 5
- INNER: 4, LEFT: 4
- INNER: 3, LEFT: 4
- INNER: 12, LEFT: 12
Show the answer
INNER: 4, LEFT: 5
INNER returns one row per match: all 4 marks rows match some student, so 4. LEFT keeps those 4 AND adds the markless student once with NULLs: 5. Option D (12 = 3 × 4) is what CROSS JOIN would produce, or what happens when someone forgets the ON condition entirely: the error the last block warns about. Counting join outputs from row counts is a standard exam exercise; reason match by match.
Think first
Choose the join
Three requests: (1) attendance-eligible list: every student, with marks where they exist, blanks otherwise; (2) a printable blank grid of every student against every subject; (3) roommate suggestions: pairs of students from the same city. Name each join before tapping.
Show the answer
(1) LEFT JOIN: all left rows survive, gaps become NULL.
(2) CROSS JOIN: deliberate every-with-every pairing.
(3) SELF JOIN on city with two aliases (plus a.roll < b.roll to avoid duplicates and self-pairs).
The selection logic: who must survive without a match? Left rows → LEFT. Everyone with everyone → CROSS. The other copy of myself → SELF.
Watch out
The silent disasters
Forgetting ON: FROM students JOIN marks without a condition degenerates into a CROSS JOIN: thousands of nonsense rows, no error message.
INNER when you meant LEFT: the student with no marks silently disappears from reports; nobody notices until the markless student complains. When a report must account for everyone, default to LEFT and test with IS NULL.
Theory
Joins are the payoff of normalization
BCA105 taught you to split data into clean tables; joins are the promised reunion. Every real system runs on them: your college portal joining students to fees to hostels, Grishu joining topics to your quiz attempts. In Unit 4, pandas performs the same trick as pd.merge(students, marks, on='roll', how='left'): the how= parameter is literally this lesson's table.
Summary
Key takeaways
- Joins stitch two tables on a matching column: FROM a JOIN b ON a.key = b.key.
- INNER keeps only matches; LEFT keeps every left row with NULLs in the gaps.
- LEFT + WHERE right.key IS NULL finds left rows with no match (the markless student).
- CROSS pairs everything with everything (m × n); a forgotten ON gives the same disaster.
- SELF joins a table to itself via two aliases; a.roll < b.roll kills duplicates.
- FULL OUTER: unmatched from both sides; classically emulated in SQLite, native since 2022.
- Memory hook: what does the clerk do with a roll that has no match?