Practice questions: joins and relationships
Joins and relationships20 min readLesson 3 of 3
These questions cover joins the way exams ask them: which join type, what happens to unmatched rows, and what the result count tells you. Run each query on the practice database instead of guessing. Every block below has the real SQLite 3.53 result.
3.1.1 Q1 What is the difference between INNER JOIN and LEFT JOIN?
Answer (English): INNER JOIN returns only pairs where the condition is TRUE, so a row without a partner disappears. LEFT JOIN returns every row of the left table, plus matching right-side values, and fills the missing right-side columns with NULL. On the LearnHub data, an inner join from courses to instructors returns five courses, while a left join returns all six and shows Python Basics with a NULL instructor.
Hinglish: INNER JOIN sirf matching pairs deta hai, partner na ho to row gayab. LEFT JOIN left table ki saari rows deta hai aur missing right values NULL kar deta hai. LearnHub data par courses se instructors ka inner join paanch courses deta hai, left join chhe deta hai aur Python Basics ka instructor NULL dikhata hai.
SELECT c.course_code, c.title, i.name AS instructor
FROM courses c
INNER JOIN instructors i ON c.instructor_id = i.instructor_id
ORDER BY c.course_code;Output
course_code | title | instructor ------------+-------------------------+------------- CN | Computer Networks | Nadia Sheikh DS | Data Structures | Arjun Das ML | Machine Learning Basics | Vikram Rao SQL | SQL and Databases | Priya Menon WEB | Web Development | Vikram Rao
3.1.2 Q2 What happens when you join two tables without an ON condition, and why is it dangerous?
Answer (English): The engine pairs every row of the first table with every row of the second, which is a cross join. Six courses and five instructors produce 30 rows. It is dangerous because a forgotten ON clause in a large table is silent: the query returns too many rows, a later aggregate multiplies values, and nothing raises an error unless a memory limit is hit.
Hinglish: Bina ON condition ke engine pehli table ki har row ko doosri ki har row se jodta hai, ise cross join kehte hain. Chhe courses aur paanch instructors 30 rows dete hain. Ye khatarnak hai kyunki bade table mein bhoola hua ON clause chupchap millions rows bana deta hai aur error nahi aata.
SELECT COUNT(*) AS pairs FROM courses CROSS JOIN instructors;Output
pairs ----- 30
3.1.3 Q3 Write a three-table query that lists student names with their course titles for students from Delhi.
Answer (English): Start from the junction table, join it to students on student_id, then join courses on course_code. The junction table is the natural centre because it holds both foreign keys. Add the city filter in WHERE, sort for readable output, and qualify columns with aliases so the two name-like columns stay distinct. The result has three rows: Aarav in Data Structures, Aarav in SQL and Rohan in Python.
Hinglish: Junction table se shuru karo, students ko student_id par jodo, phir courses ko course_code par. Junction ke paas dono foreign keys hain, isliye woh beech mein rehti hai. City filter WHERE mein lagao aur aliases se columns qualify karo. Result teen rows ka aata hai: Aarav Data Structures aur SQL mein, Rohan Python mein.
SELECT s.name, c.title, e.status
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_code = e.course_code
WHERE s.city = 'Delhi'
ORDER BY s.name, c.title;Output
name | title | status -------------+-------------------+---------- Aarav Sharma | Data Structures | active Aarav Sharma | SQL and Databases | completed Rohan Verma | Python Basics | active
3.1.4 Q4 Why does adding a WHERE condition on the right table change a LEFT JOIN result?
Answer (English): A LEFT JOIN fills the right side with NULL when there is no match, and a WHERE condition on the right table is evaluated after that padding. A condition such as e.status = 'active' is UNKNOWN for the NULL row, which drops it, and the unmatched left rows disappear. The query behaves like an inner join. Put the condition in the ON clause when you want to keep all left rows and only match some of the right rows.
Hinglish: LEFT JOIN bina match wali right side ko NULL se bharta hai, aur WHERE condition uske baad chalti hai. e.status = 'active' NULL row ke liye UNKNOWN hota hai, isliye woh row hat jaati hai aur unmatched left rows bhi gayab ho jaati hain; query inner join jaisi ban jaati hai. Saari left rows rakhni hon to condition ON mein daalo.
3.1.5 Q5 When do you need a self join, and how do you write one?
Answer (English): A self join is needed when a table stores a reference to another row of the same table, such as a student's mentor, an employee's manager or a category's parent. Give the table two aliases, join on the reference, and select the roles separately. The inner self join below returns the six students who have a mentor; students without a mentor need a left join to appear.
Hinglish: Self join tab chahiye jab table khud ke kisi doosre row ko reference karta ho, jaise student ka mentor, employee ka manager ya category ka parent. Table ko do aliases do, reference par join karo aur roles alag select karo. Neeche inner self join chhe students deta hai jinka mentor hai; bina mentor wale students left join se dikhenge.
SELECT s.name AS student, m.name AS mentor
FROM students s
JOIN students m ON s.mentor_id = m.student_id
ORDER BY student;Output
student | mentor ------------+------------- Ishita Bose | Meera Iyer Kabir Nair | Meera Iyer Neel Joshi | Meera Iyer Rohan Verma | Aarav Sharma Sana Khan | Aarav Sharma Zoya Ali | Aarav Sharma
3.1.6 Q6 Why does a many-to-many relationship need a junction table, and what key does the junction table use?
Answer (English): One student takes several courses and one course has several students, so placing a course key on the student row would repeat rows and break first normal form. The junction table stores one row per pair: student_id plus course_code. Its primary key is the pair, which makes a duplicate enrollment impossible, and each column is a foreign key to one side. Relationship-specific data such as status and rating belongs on the junction row, not on either parent table.
Hinglish: Ek student kai courses le sakta hai aur ek course mein kai students hote hain, isliye student row par course key rakhne se rows repeat hongi aur 1NF tootega. Junction table har pair ki ek row rakhti hai: student_id plus course_code. Uska primary key pair hota hai, isliye duplicate enrollment possible nahi, aur dono columns foreign keys hain. Status aur rating jaise relationship facts junction row par rehte hain.
3.1.7 Q7 Compare UNION, UNION ALL, INTERSECT and EXCEPT.
Answer (English): UNION combines two result sets and removes duplicates. UNION ALL combines them and keeps everything, so it is cheaper and preserves multiplicity. INTERSECT returns only rows present in both inputs. EXCEPT returns rows from the first input that are absent from the second. All four require matching column counts and compatible types. On the city lists from the notes, UNION gives 8 rows and UNION ALL gives 14 before filtering, while INTERSECT gives the four shared cities plus a shared NULL.
Hinglish: UNION do results ko jodta hai aur duplicates hata deta hai. UNION ALL sab rakhta hai, isliye sasta hai. INTERSECT sirf common rows deta hai. EXCEPT pehle input ki woh rows deta hai jo doosre mein nahi hain. Chaaron mein column count aur types match karne chahiye. City lists par UNION 8 rows deta hai, UNION ALL 14, aur INTERSECT shared cities plus shared NULL.
3.1.8 Q8 What is fan-out double counting, and how do you detect it?
Answer (English): Fan-out happens when a parent row joins to several child rows, so the parent's columns repeat in the joined result. An aggregate written for the parent table then counts or sums each parent value once per child. Detect it by comparing the parent aggregate against the joined aggregate: course fees sum to 33994 across the courses table but to 72486 after joining enrollments. Fix it by aggregating one side first, or by using COUNT(DISTINCT) when the question is about distinct entities.
Hinglish: Fan-out tab hota hai jab parent row kai child rows se join ho, jisse parent ke columns join result mein repeat hote hain. Parent table ke liye likha aggregate phir har child ke saath parent ko dobara ginta hai. Pehchanne ka tarika: parent aggregate aur joined aggregate compare karo; courses ki fees 33994 hain par enrollments join ke baad 72486. Fix: pehle ek side aggregate karo ya COUNT(DISTINCT) use karo.
SELECT SUM(fee) AS fee_per_course FROM courses;
SELECT SUM(c.fee) AS fee_per_enrollment
FROM courses c JOIN enrollments e ON e.course_code = c.course_code;Output
fee_per_course -------------- 33994 fee_per_enrollment ------------------ 72486
3.1.9 Q9 What happens to a row whose foreign key is NULL when you inner join it to the parent table?
Answer (English): The join condition compares foreign key to parent key, and NULL equals nothing, so the row has no match and an inner join drops it. In the practice database, Python Basics has a NULL instructor_id, so an inner join from courses to instructors omits Python Basics entirely. A left join keeps the course and pads the instructor columns with NULL. Reports that suddenly lose rows after a join usually have a nullable foreign key on one side.
Hinglish: Join condition foreign key ko parent key se compare karti hai, aur NULL kisi ke barabar nahi hota, isliye row ka match nahi banta aur inner join use hata deta hai. Practice database mein Python Basics ka instructor_id NULL hai, isliye courses se instructors ka inner join Python Basics ko chhod deta hai. LEFT JOIN us course ko rakhta hai aur instructor columns NULL kar deta hai.
3.1.10 Q10 How do you find parents that have no children, and which anti-join form is safest with NULLs?
Answer (English): The two common forms are a left join with IS NULL on the child key, and NOT EXISTS with a correlated subquery. Both are safe. NOT IN with a subquery is the unsafe form when the subquery can return NULL, because the comparison becomes UNKNOWN and no row passes. The query below returns Dev Patel, the only student with no enrollment, using a left join; replacing it with NOT EXISTS gives the same row.
Hinglish: Do common tarike hain: left join ke baad child key par IS NULL, ya correlated subquery ke saath NOT EXISTS. Dono safe hain. NOT IN tab unsafe hota hai jab subquery NULL de sakti ho, kyunki comparison UNKNOWN ban jaata hai aur koi row pass nahi hoti. Neeche left join Dev Patel deta hai, jo bina enrollment wala akela student hai; NOT EXISTS bhi wahi row deta hai.
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
WHERE e.student_id IS NULL;Output
name --------- Dev Patel
3.2 Quiz pointer
The five-question quiz for this module is stored separately. Take it without looking back, then rerun any join you missed on the practice database. If a row count surprises you, check the join type and the grain of both inputs before changing the query.