Skip to content

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

  1. Write INNER, LEFT, RIGHT, FULL and SELF joins, and multi-table joins.
  2. Distinguish non-correlated from correlated subqueries.
  3. Use IN, NOT IN, EXISTS, NOT EXISTS, ANY, ALL correctly.
  4. Handle the NOT IN with NULL trap.
  5. Express division ("for all") queries in SQL.
  6. Use UNION, INTERSECT and EXCEPT, 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);
The inner query returns 47 once; the outer scan then compares each course against 47.

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:

  1. Filter the NULLs out of the subquery.

    WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION WHERE Rollno IS NOT NULL)
    
    Correct, but relies on the author remembering the trap every time.

  2. Use NOT EXISTS.

    SELECT s.Name FROM STUDENT s
    WHERE  NOT EXISTS (SELECT 1 FROM ADMISSION a WHERE a.Rollno = s.Rollno);
    
    EXISTS tests only whether rows exist; a NULL a.Rollno simply fails the equality and contributes nothing. Immune by construction.

  3. Use an anti-join.

    SELECT s.Name FROM STUDENT s
    LEFT JOIN ADMISSION a ON s.Rollno = a.Rollno
    WHERE a.Rollno IS NULL;
    
    Also correct, and often the fastest.

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 );
Comparing against the maximum rather than using 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;
Note this does cut ties arbitrarily at the boundary; if that is unacceptable, a window function (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' );
The leading join is essential: it requires the student to have at least one admission. Without it, 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;
The two 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 in WHERE cancels 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 no DISTINCT, and it is NULL-safe in both its positive and negative forms, so the same habit protects you from the NOT IN trap.
  • If you need columns from both tables (say the course name alongside the student name), a JOIN is the natural choice — the duplication is then meaningful data, not an artefact.
  • IN is perfectly good for the positive case and is often the most readable; simply never use NOT IN against 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

  1. Why is explicit JOIN ... ON preferred to the comma form?
  2. What does EXISTS return, and why write SELECT 1?
  3. State the NOT IN with NULL trap in one sentence.
  4. Give the equivalent of > ANY and > ALL using MIN/MAX.
  5. Why must a derived table have an alias?
  6. When is UNION ALL preferable to UNION?
  7. Which clause should hold a condition on the right-hand table of a LEFT JOIN?

B. Write the SQL

  1. Students and the societies they belong to, including students in none.
  2. Courses and their teachers, for courses with at least one admission.
  3. Pairs of students in the same society.
  4. Students in both a full-time and a part-time course.
  5. Students in a society but no course.
  6. Courses with more admissions than the average.
  7. The student admitted to the greatest number of courses.
  8. Societies with no members.
  9. Students admitted to every full-time course (both methods).
  10. Students admitted only to part-time courses.
  11. A combined list of all student names and all society mentor names.
  12. Students who share a course with X2019 (excluding X2019).
  13. For each course, its name and the name of the youngest student admitted to it.

C. Predict and explain

  1. ADMISSION has one row with Rollno = NULL. What does SELECT Name FROM STUDENT WHERE Rollno NOT IN (SELECT Rollno FROM ADMISSION); return? Why?
  2. 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'
  3. 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

  1. Explain why the course Q12 query needs the initial join to ADMISSION. Construct the data that breaks it if the join is omitted.
  2. Rewrite three IN subqueries as joins and three as EXISTS, and comment on which reads best in each case.
  3. Explain how an optimiser can turn a correlated NOT EXISTS into an anti-join, and why that matters.
  4. Compare the two division methods on the empty-divisor edge case. Which matches relational algebra? Which matches typical user intent?
  5. 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

  1. NOT IN and NOT EXISTS always give the same result.
  2. A correlated subquery can be executed independently.
  3. UNION requires the same column names in both operands.
  4. LEFT JOIN with a WHERE condition on the right table behaves as an inner join.
  5. > ALL (empty set) is FALSE.
  6. EXISTS (SELECT 1 ...) and EXISTS (SELECT * ...) differ in result.

Answers to Section E

  1. False. They differ when the subquery yields NULL: NOT IN returns nothing, NOT EXISTS behaves correctly.
  2. False. It references an outer column and is not a complete query on its own.
  3. False. It requires the same number of columns with compatible types; names may differ, and the result takes the first operand's names.
  4. 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.
  5. False. It is vacuously TRUE — there is no counter-example in an empty set. (> ANY (empty) is FALSE.)
  6. False. EXISTS tests only whether a row is produced; the select list is never evaluated. SELECT 1 is a readability convention.

Lesson Summary

  • Prefer explicit JOIN ... ON. Conditions on the right table of an outer join belong in ON, not WHERE.
  • 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 IN with a NULL returns nothing. Use NOT EXISTS.
  • Division ("for all") is written as double NOT EXISTS or by counting; the two differ on an empty divisor.
  • Set operations need union compatibility; use UNION ALL when the operands are known disjoint.