Lesson 17: Joins, Subqueries and Set Operations¶
Combining tables — and queries inside queries
About this lesson
Course: Database Management Systems
Unit 4: Structured Query Language · 12 Lecture Hours
Lesson duration: 2 lectures
Reading: Elmasri & Navathe, Ch. 6.3, 7.1 · Murach's MySQL, Ch. 4, 7
Learning Outcomes
- Write
INNER,LEFT,RIGHT,FULLandSELFjoins, and multi-table joins. - Distinguish non-correlated from correlated subqueries.
- Use
IN,NOT IN,EXISTS,NOT EXISTS,ANY,ALLcorrectly. - Handle the
NOT INwith NULL trap. - Express division ("for all") queries in SQL.
- Use
UNION,INTERSECTandEXCEPT, and their MySQL substitutes.
17.1 Joins¶
The syntax forms¶
-- Implicit (old style) -- avoid: easy to forget the condition
SELECT s.Name, a.SID FROM STUDENT s, ADMISSION a WHERE s.Rollno = a.Rollno;
-- Explicit (ANSI, preferred) -- join condition cannot be forgotten
SELECT s.Name, a.SID FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno;
-- USING, when the column names match
SELECT s.Name, a.SID FROM STUDENT s JOIN ADMISSION a USING (Rollno);
-- NATURAL: joins on ALL same-named columns -- risky, avoid
SELECT Name, SID FROM STUDENT NATURAL JOIN ADMISSION;
Always use explicit JOIN ... ON
With the implicit comma form, forgetting the WHERE produces a silent Cartesian product — 5 students × 6 admissions = 30 meaningless rows, with no error. With JOIN ... ON the condition is syntactically required, so the mistake cannot happen. Explicit syntax also separates join conditions from filter conditions, which matters for outer joins (Section 5.1.4).
INNER JOIN¶
Returns only rows with a match on both sides.
SELECT s.Rollno, s.Name, c.Cname, a.Dateofadmission
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
ORDER BY s.Rollno;
Course Q1 — names of students enrolled in any course:
SELECT DISTINCT s.Name
FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno;
DISTINCT is required: a student in two courses produces two rows.
Course Q2 — names of students in at least one part-time course:
SELECT DISTINCT s.Name
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE c.Coursetype = 'Parttime';
OUTER JOINs¶
SELECT s.Name, c.Cname
FROM STUDENT s
LEFT JOIN ADMISSION a ON s.Rollno = a.Rollno
LEFT JOIN COURSE c ON a.SID = c.SID;
Every student appears; Arjun Nair shows NULL for Cname.
MySQL has no FULL OUTER JOIN; simulate it:
SELECT s.Name, a.SID FROM STUDENT s LEFT JOIN ADMISSION a ON s.Rollno=a.Rollno
UNION
SELECT s.Name, a.SID FROM STUDENT s RIGHT JOIN ADMISSION a ON s.Rollno=a.Rollno;
SELF JOIN¶
A table joined to itself, using aliases — the SQL form of a recursive relationship (Unit 2, Lesson 3).
-- Pairs of distinct students admitted to the same course
SELECT a1.Rollno AS Student1, a2.Rollno AS Student2, a1.SID
FROM ADMISSION a1 JOIN ADMISSION a2 ON a1.SID = a2.SID
WHERE a1.Rollno < a2.Rollno;
The condition a1.Rollno < a2.Rollno (rather than <>) does two jobs: it excludes a student paired with themselves, and it lists each pair once rather than twice in both orders.
-- Employees and their supervisors
SELECT e.Name AS Employee, s.Name AS Supervisor
FROM EMPLOYEE e LEFT JOIN EMPLOYEE s ON e.SuperSsn = s.Ssn;
The LEFT JOIN keeps the chief executive, who has no supervisor.
The outer-join trap: ON vs WHERE¶
A condition in WHERE silently cancels an outer join
-- WRONG: behaves as an INNER join
SELECT c.Cname, COUNT(a.Rollno)
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
WHERE a.Dateofadmission >= '2019-08-01'
GROUP BY c.SID, c.Cname;
-- RIGHT: the extra condition belongs in ON
SELECT c.Cname, COUNT(a.Rollno)
FROM COURSE c LEFT JOIN ADMISSION a
ON c.SID = a.SID AND a.Dateofadmission >= '2019-08-01'
GROUP BY c.SID, c.Cname;
Why. The LEFT JOIN first pads unmatched courses with NULLs. The WHERE clause then tests NULL >= '2019-08-01', which is UNKNOWN, so those rows are discarded — undoing the outer join entirely.
Rule: conditions on the outer (right-hand) table belong in ON; conditions on the preserved (left-hand) table belong in WHERE.
17.2 Subqueries¶
Subquery (nested query)
A SELECT statement inside another statement. The inner query is the subquery; the enclosing one is the outer query.
Non-correlated: independent of the outer query — evaluated once. Correlated: references a column of the outer query — evaluated once per outer row.
Scalar subqueries¶
Return exactly one value; usable anywhere a value is allowed.
SELECT Cname, TotalSeats FROM COURSE
WHERE TotalSeats > (SELECT AVG(TotalSeats) FROM COURSE);
SELECT Name, Dateofbirth FROM STUDENT
WHERE Dateofbirth = (SELECT MAX(Dateofbirth) FROM STUDENT);
IN and NOT IN¶
SELECT Name FROM STUDENT
WHERE Rollno IN (SELECT Rollno FROM ADMISSION
WHERE SID IN (SELECT SID FROM COURSE WHERE Coursetype='Parttime'));
The NOT IN + NULL trap — the most dangerous bug in SQL
SELECT Name FROM STUDENT
WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION);
If any row of the subquery returns NULL, this query returns no rows at all — silently, with no error.
Why. x NOT IN (a, b, NULL) expands to x<>a AND x<>b AND x<>NULL. The last conjunct is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN, so the whole condition can never be TRUE.
Three safe alternatives:
-- 1. Exclude NULLs explicitly
WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION WHERE Rollno IS NOT NULL)
-- 2. Use NOT EXISTS -- immune to the problem
WHERE NOT EXISTS (SELECT 1 FROM ADMISSION a WHERE a.Rollno = s.Rollno)
-- 3. Use a LEFT JOIN ... IS NULL
FROM STUDENT s LEFT JOIN ADMISSION a ON s.Rollno=a.Rollno WHERE a.Rollno IS NULL
Prefer NOT EXISTS. It handles NULLs correctly by construction and is usually at least as fast.
EXISTS and NOT EXISTS — correlated subqueries¶
EXISTS
EXISTS (subquery) is TRUE if the subquery returns at least one row, regardless of its contents. Convention: write SELECT 1, since the values are never examined.
-- Students admitted to at least one course
SELECT s.Name FROM STUDENT s
WHERE EXISTS (SELECT 1 FROM ADMISSION a WHERE a.Rollno = s.Rollno);
-- Students admitted to no course
SELECT s.Name FROM STUDENT s
WHERE NOT EXISTS (SELECT 1 FROM ADMISSION a WHERE a.Rollno = s.Rollno);
The reference s.Rollno inside the subquery makes it correlated: it cannot run alone, and is conceptually re-evaluated for each candidate s.
Correlated vs non-correlated
| Non-correlated | Correlated | |
|---|---|---|
| References the outer query | No | Yes |
| Evaluated | Once | Conceptually once per outer row |
| Can run standalone | Yes | No |
| Typical operators | IN, =, > with a scalar |
EXISTS, NOT EXISTS |
Modern optimisers frequently rewrite a correlated subquery into a semi-join or anti-join (Unit 3, Lesson 4), so the "once per row" cost is conceptual rather than actual.
ANY / SOME and ALL¶
SELECT Cname FROM COURSE
WHERE TotalSeats > ANY (SELECT TotalSeats FROM COURSE WHERE Coursetype='Parttime');
-- greater than AT LEAST ONE part-time course, i.e. > the MINIMUM
SELECT Cname FROM COURSE
WHERE TotalSeats > ALL (SELECT TotalSeats FROM COURSE WHERE Coursetype='Parttime');
-- greater than EVERY part-time course, i.e. > the MAXIMUM
| Construct | Equivalent |
|---|---|
> ANY (S) |
> MIN(S) |
> ALL (S) |
> MAX(S) |
< ANY (S) |
< MAX(S) |
< ALL (S) |
< MIN(S) |
= ANY (S) |
IN (S) |
<> ALL (S) |
NOT IN (S) |
Note: > ALL on an empty subquery is vacuously TRUE; > ANY on an empty subquery is FALSE.
Subqueries in FROM — derived tables¶
SELECT Coursetype, AVG(Enrolled) AS MeanEnrolment
FROM ( SELECT c.SID, c.Coursetype, COUNT(a.Rollno) AS Enrolled
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID = a.SID
GROUP BY c.SID, c.Coursetype ) AS per_course
GROUP BY Coursetype;
A derived table must be given an alias. This is the standard way to aggregate over an aggregate.
17.3 Division in SQL — the "for all" queries¶
SQL has no division operator (Unit 3, Lesson 4). Two idioms.
Query: students admitted to all part-time courses.
Method 1 — double NOT EXISTS¶
The logic: "there is no part-time course for which this student has no admission."
SELECT s.Name
FROM STUDENT s
WHERE NOT EXISTS (
SELECT 1 FROM COURSE c
WHERE c.Coursetype = 'Parttime'
AND NOT EXISTS ( SELECT 1 FROM ADMISSION a
WHERE a.Rollno = s.Rollno AND a.SID = c.SID )
);
Read the double negative carefully — it is the direct translation of "for all", since ∀x P(x) is equivalent to ¬∃x ¬P(x).
Method 2 — counting (usually clearer and faster)¶
The logic: "the number of distinct part-time courses this student has equals the total number of part-time courses."
SELECT s.Rollno, s.Name
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE c.Coursetype = 'Parttime'
GROUP BY s.Rollno, s.Name
HAVING COUNT(DISTINCT c.SID) = (SELECT COUNT(*) FROM COURSE WHERE Coursetype='Parttime');
COUNT(DISTINCT c.SID) rather than COUNT(*) guards against duplicate admission rows inflating the count.
Course Q12 — students admitted to full-time courses only (a related but different pattern — "none of the other kind"):
SELECT DISTINCT s.Rollno, s.Name
FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno
WHERE NOT EXISTS ( SELECT 1 FROM ADMISSION a2 JOIN COURSE c2 ON a2.SID = c2.SID
WHERE a2.Rollno = s.Rollno AND c2.Coursetype <> 'Fulltime' );
The initial join ensures the student has at least one admission — otherwise a student with no admissions at all would vacuously qualify.
17.4 Set Operations¶
| Operation | Meaning | MySQL support |
|---|---|---|
UNION |
In either, duplicates removed | Yes |
UNION ALL |
In either, duplicates kept | Yes |
INTERSECT |
In both | 8.0.31+; else simulate |
EXCEPT / MINUS |
In the first, not the second | 8.0.31+; else simulate |
All require the operands to be union compatible: same number of columns, compatible types (Unit 3, Lesson 3).
Course Q14 — names of all students who took admission to any course, and course names in which at least one student has enrolled:
SELECT DISTINCT s.Name AS Item, 'Student' AS Kind
FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno
UNION
SELECT DISTINCT c.Cname, 'Course'
FROM COURSE c JOIN ADMISSION a ON c.SID = a.SID;
The literal Kind column makes the combined list interpretable — without it the two sorts of name are indistinguishable.
Simulating INTERSECT in older MySQL:
-- Students in both CS01 and MA02
SELECT DISTINCT a1.Rollno FROM ADMISSION a1
WHERE a1.SID='CS01' AND EXISTS (SELECT 1 FROM ADMISSION a2
WHERE a2.Rollno=a1.Rollno AND a2.SID='MA02');
Simulating EXCEPT:
SELECT DISTINCT a1.Rollno FROM ADMISSION a1
WHERE a1.SID='CS01' AND NOT EXISTS (SELECT 1 FROM ADMISSION a2
WHERE a2.Rollno=a1.Rollno AND a2.SID='MA02');
UNION vs UNION ALL
UNION removes duplicates, which requires a sort or hash of the combined result — real work on a large query.
UNION ALL does not. If you know the operands are disjoint, always write UNION ALL — it is strictly faster and gives the same answer.
17.5 Solved Questions¶
Solved Question 1
Q. Distinguish correlated from non-correlated subqueries with an example of each. How does each execute?
A. A non-correlated subquery is self-contained: it references nothing from the outer query and can be executed on its own. The DBMS evaluates it once, and uses the resulting value or set for every outer row.
SELECT Cname FROM COURSE
WHERE TotalSeats > (SELECT AVG(TotalSeats) FROM COURSE);
A correlated subquery references a column of the outer query, so it cannot run alone and is conceptually re-evaluated for each outer row.
SELECT s.Name FROM STUDENT s
WHERE EXISTS (SELECT 1 FROM ADMISSION a WHERE a.Rollno = s.Rollno);
s.Rollno binds to the outer row, so the subquery asks a different question for each student.
| Non-correlated | Correlated | |
|---|---|---|
| References outer query | No | Yes |
| Executions (conceptually) | 1 | 1 per outer row |
| Runs standalone | Yes | No |
| Typical operators | =, >, IN |
EXISTS, NOT EXISTS |
In practice the "once per row" cost is conceptual: modern optimisers rewrite a correlated EXISTS into a semi-join and a correlated NOT EXISTS into an anti-join (Unit 3, Lesson 4), evaluating it with a single pass and an index. So EXISTS should not be avoided on performance grounds.
Solved Question 2
Q. Explain the NOT IN with NULL trap. Give a failing example and three fixes, recommending one.
A. x NOT IN (S) is defined as x <> s1 AND x <> s2 AND ... AND x <> sn. If any si is NULL, that conjunct evaluates to UNKNOWN. Since TRUE AND UNKNOWN = UNKNOWN and WHERE retains only TRUE, the condition can never be satisfied — so the query returns zero rows, whatever the data.
There is no error and no warning; the result simply looks like "no such students exist".
Failing example. Suppose ADMISSION is altered so that Rollno is nullable, and one row has Rollno = NULL:
SELECT Name FROM STUDENT
WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION); -- returns NOTHING
Even though Arjun Nair clearly has no admission, he is not returned.
Three fixes:
-
Filter the NULLs out of the subquery.
Correct, but relies on the author remembering the trap every time.WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION WHERE Rollno IS NOT NULL) -
Use
NOT EXISTS.SELECT s.Name FROM STUDENT s WHERE NOT EXISTS (SELECT 1 FROM ADMISSION a WHERE a.Rollno = s.Rollno);EXISTStests only whether rows exist; a NULLa.Rollnosimply fails the equality and contributes nothing. Immune by construction. -
Use an anti-join.
Also correct, and often the fastest.SELECT s.Name FROM STUDENT s LEFT JOIN ADMISSION a ON s.Rollno = a.Rollno WHERE a.Rollno IS NULL;
Recommendation: NOT EXISTS. It is correct regardless of NULLs, states the intent directly, and optimisers implement it as an anti-join, so it matches the performance of option 3 without the subtlety of reasoning about padded NULLs.
Solved Question 3
Q. Write SQL for course practicals 1, 2, 10, 11, 12 and 14.
A.
Q1 — names of students enrolled in any course.
SELECT DISTINCT s.Name
FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno;
Q2 — names of students enrolled in at least one part-time course.
SELECT DISTINCT s.Name
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE c.Coursetype = 'Parttime';
Q10 — the most popular society.
SELECT y.Socname, COUNT(*) AS Members
FROM SOCIETY y JOIN ENROLLMENT e ON y.SocID = e.SocID
GROUP BY y.SocID, y.Socname
HAVING COUNT(*) = ( SELECT MAX(cnt) FROM
(SELECT COUNT(*) AS cnt FROM ENROLLMENT GROUP BY SocID) t );
ORDER BY ... LIMIT 1 correctly returns all societies that tie for first place.
Q11 — the two most popular part-time courses.
SELECT c.Cname, COUNT(*) AS Enrolled
FROM COURSE c JOIN ADMISSION a ON c.SID = a.SID
WHERE c.Coursetype = 'Parttime'
GROUP BY c.SID, c.Cname
ORDER BY Enrolled DESC
LIMIT 2;
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) <= 2) returns all courses in the top two ranks.
Q12 — students admitted to full-time courses only.
SELECT DISTINCT s.Rollno, s.Name
FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno
WHERE NOT EXISTS ( SELECT 1 FROM ADMISSION a2 JOIN COURSE c2 ON a2.SID = c2.SID
WHERE a2.Rollno = s.Rollno AND c2.Coursetype <> 'Fulltime' );
Arjun Nair — who has no admissions at all — would satisfy the NOT EXISTS vacuously and be wrongly returned.
Q14 — names of all students who took admission to any course, and course names in which at least one student enrolled.
SELECT DISTINCT s.Name AS Item, 'Student' AS Kind
FROM STUDENT s JOIN ADMISSION a ON s.Rollno = a.Rollno
UNION
SELECT DISTINCT c.Cname, 'Course'
FROM COURSE c JOIN ADMISSION a ON c.SID = a.SID;
SELECTs are union compatible (two columns, both strings). The literal Kind column distinguishes the two sorts of name in the combined list.
Solved Question 4
Q. Express "students admitted to all part-time courses" in two ways. Compare them.
A.
Method 1 — double NOT EXISTS. Direct translation of the logical identity ∀x P(x) ≡ ¬∃x ¬P(x): "there is no part-time course for which this student has no admission."
SELECT s.Rollno, s.Name
FROM STUDENT s
WHERE NOT EXISTS (
SELECT 1 FROM COURSE c
WHERE c.Coursetype = 'Parttime'
AND NOT EXISTS ( SELECT 1 FROM ADMISSION a
WHERE a.Rollno = s.Rollno AND a.SID = c.SID ) );
Method 2 — counting. "The number of distinct part-time courses this student is admitted to equals the total number of part-time courses."
SELECT s.Rollno, s.Name
FROM STUDENT s
JOIN ADMISSION a ON s.Rollno = a.Rollno
JOIN COURSE c ON a.SID = c.SID
WHERE c.Coursetype = 'Parttime'
GROUP BY s.Rollno, s.Name
HAVING COUNT(DISTINCT c.SID) =
(SELECT COUNT(*) FROM COURSE WHERE Coursetype = 'Parttime');
Comparison.
Double NOT EXISTS |
Counting | |
|---|---|---|
| Readability | Hard — a double negative | Easier — states the arithmetic directly |
| Correctness with duplicates | Immune | Needs COUNT(DISTINCT ...), else duplicate admissions inflate the count |
| Empty divisor (no part-time courses exist) | Returns every student, matching the mathematical definition of division | Returns no student, since no student has any part-time admission to group |
| Typical performance | Anti-join, usually good | One grouped pass, usually good |
The empty-divisor row is the important difference: the two methods genuinely disagree in that edge case. Method 1 matches the relational-algebra definition (a vacuous "for all" is true); Method 2 matches the more usual practical intent. State which behaviour you want.
Both correctly return no rows on our sample data, since no student is admitted to both MA02 and EN03.
Solved Question 5
Q. Why does adding WHERE a.Dateofadmission >= '2019-08-01' to a LEFT JOIN query turn it into an inner join? Give the fix.
A. A LEFT JOIN executes in two conceptual stages: first it matches rows, then it pads every unmatched left row with NULLs in the right table's columns. Only after that does the WHERE clause run.
For an unmatched course such as PH01, the padded row has a.Dateofadmission = NULL. The condition NULL >= '2019-08-01' evaluates to UNKNOWN, and WHERE retains only rows evaluating to TRUE. So every padded row is discarded — precisely the rows the outer join existed to preserve. The net effect is identical to an inner join.
The fix: move the condition into the ON clause, so it participates in matching rather than in filtering after padding.
SELECT c.Cname, COUNT(a.Rollno) AS RecentAdmissions
FROM COURSE c
LEFT JOIN ADMISSION a
ON c.SID = a.SID AND a.Dateofadmission >= '2019-08-01'
GROUP BY c.SID, c.Cname;
Now a course with no qualifying admission still yields one padded row, and COUNT(a.Rollno) correctly reports 0 for it.
The general rule:
- Conditions on the preserved (left) table →
WHERE. They legitimately remove left rows. - Conditions on the optional (right) table →
ON. Putting them inWHEREcancels the outer join. - The one exception is a deliberate
WHERE <right column> IS NULL, which is the anti-join idiom and is meant to keep only the unmatched rows.
Solved Question 6
Q. Compare IN, EXISTS and JOIN for "students admitted to a part-time course". Which is preferable?
A. Three formulations of the same question:
-- 1. IN
SELECT Name FROM STUDENT
WHERE Rollno IN (SELECT a.Rollno FROM ADMISSION a JOIN COURSE c ON a.SID=c.SID
WHERE c.Coursetype='Parttime');
-- 2. EXISTS (correlated)
SELECT s.Name FROM STUDENT s
WHERE EXISTS (SELECT 1 FROM ADMISSION a JOIN COURSE c ON a.SID=c.SID
WHERE a.Rollno = s.Rollno AND c.Coursetype='Parttime');
-- 3. JOIN
SELECT DISTINCT s.Name FROM STUDENT s
JOIN ADMISSION a ON s.Rollno=a.Rollno
JOIN COURSE c ON a.SID=c.SID
WHERE c.Coursetype='Parttime';
IN |
EXISTS |
JOIN |
|
|---|---|---|---|
| Duplicates | None — IN is a membership test |
None | DISTINCT required, else a student in two part-time courses appears twice |
| NULL safety | Safe for IN; unsafe for NOT IN |
Safe both ways | Safe |
| Readability | Good | Good for existence questions | Best when columns from both tables are needed |
| Performance | Optimised to a semi-join | Optimised to a semi-join | Fine, but DISTINCT costs a sort or hash |
Recommendation.
- If you need only to test existence and want no columns from the other table, prefer
EXISTS: it needs noDISTINCT, and it is NULL-safe in both its positive and negative forms, so the same habit protects you from theNOT INtrap. - If you need columns from both tables (say the course name alongside the student name), a
JOINis the natural choice — the duplication is then meaningful data, not an artefact. INis perfectly good for the positive case and is often the most readable; simply never useNOT INagainst a nullable column.
All three are typically compiled to the same semi-join plan, so the choice is one of clarity and correctness rather than speed.
17.6 Exercises¶
A. Short answer
- Why is explicit
JOIN ... ONpreferred to the comma form? - What does
EXISTSreturn, and why writeSELECT 1? - State the
NOT INwith NULL trap in one sentence. - Give the equivalent of
> ANYand> ALLusingMIN/MAX. - Why must a derived table have an alias?
- When is
UNION ALLpreferable toUNION? - Which clause should hold a condition on the right-hand table of a
LEFT JOIN?
B. Write the SQL
- Students and the societies they belong to, including students in none.
- Courses and their teachers, for courses with at least one admission.
- Pairs of students in the same society.
- Students in both a full-time and a part-time course.
- Students in a society but no course.
- Courses with more admissions than the average.
- The student admitted to the greatest number of courses.
- Societies with no members.
- Students admitted to every full-time course (both methods).
- Students admitted only to part-time courses.
- A combined list of all student names and all society mentor names.
- Students who share a course with
X2019(excludingX2019). - For each course, its name and the name of the youngest student admitted to it.
C. Predict and explain
ADMISSIONhas one row withRollno = NULL. What doesSELECT Name FROM STUDENT WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION);return? Why?- Explain the difference in results between:
a.
FROM COURSE c LEFT JOIN ADMISSION a ON c.SID=a.SID WHERE a.SID='CS01'b.FROM COURSE c LEFT JOIN ADMISSION a ON c.SID=a.SID AND a.SID='CS01' - What does
SELECT Cname FROM COURSE WHERE TotalSeats > ALL (SELECT TotalSeats FROM COURSE WHERE Coursetype='Diploma');return when no diploma courses exist? Why?
D. Analytical
- Explain why the course Q12 query needs the initial join to
ADMISSION. Construct the data that breaks it if the join is omitted. - Rewrite three
INsubqueries as joins and three asEXISTS, and comment on which reads best in each case. - Explain how an optimiser can turn a correlated
NOT EXISTSinto an anti-join, and why that matters. - Compare the two division methods on the empty-divisor edge case. Which matches relational algebra? Which matches typical user intent?
- Design and write a single query producing, for each course: the name, the enrolment count, the vacant seats, the percentage full, and the name of the earliest-admitted student.
E. Objective — True or False with reason
NOT INandNOT EXISTSalways give the same result.- A correlated subquery can be executed independently.
UNIONrequires the same column names in both operands.LEFT JOINwith aWHEREcondition on the right table behaves as an inner join.> ALL (empty set)is FALSE.EXISTS (SELECT 1 ...)andEXISTS (SELECT * ...)differ in result.
Answers to Section E
- False. They differ when the subquery yields NULL:
NOT INreturns nothing,NOT EXISTSbehaves correctly. - False. It references an outer column and is not a complete query on its own.
- False. It requires the same number of columns with compatible types; names may differ, and the result takes the first operand's names.
- True in general — the padded NULLs fail the condition and are discarded. The deliberate exception is
WHERE <right col> IS NULL, the anti-join idiom. - False. It is vacuously TRUE — there is no counter-example in an empty set. (
> ANY (empty)is FALSE.) - False.
EXISTStests only whether a row is produced; the select list is never evaluated.SELECT 1is a readability convention.
Lesson Summary
- Prefer explicit
JOIN ... ON. Conditions on the right table of an outer join belong inON, notWHERE. - Self-joins need aliases; use
<rather than<>to list unordered pairs once. - Non-correlated subqueries run once; correlated ones reference the outer row and are optimised into semi-/anti-joins.
NOT INwith a NULL returns nothing. UseNOT EXISTS.- Division ("for all") is written as double
NOT EXISTSor by counting; the two differ on an empty divisor. - Set operations need union compatibility; use
UNION ALLwhen the operands are known disjoint.