EXISTS and NOT EXISTS

EXISTS Tests Rows, Not Values

EXISTS is TRUE as soon as its subquery has at least one qualifying row. NOT EXISTS is TRUE when no qualifying row exists. They are ideal for “has at least one” and “has none” questions.

WHERE EXISTS (
  SELECT 1
  FROM sq_results AS r
  WHERE r.student_id = s.student_id
)

The correlation links inner r to current outer s. Selecting 1 is conventional; the actual projected value is not used.

Verified Results Lab

CREATE TABLE sq_subjects (
  subject_code VARCHAR(10) PRIMARY KEY,
  subject_name VARCHAR(60) NOT NULL
);

CREATE TABLE sq_results (
  student_id INT NOT NULL,
  subject_code VARCHAR(10) NOT NULL,
  marks DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (student_id, subject_code),
  FOREIGN KEY (student_id)
    REFERENCES sq_students(student_id),
  FOREIGN KEY (subject_code)
    REFERENCES sq_subjects(subject_code)
);

INSERT INTO sq_subjects VALUES
('CSC', 'Computer Science'),
('MAT', 'Mathematics');

INSERT INTO sq_results VALUES
(1, 'MAT', 86), (1, 'CSC', 91),
(2, 'MAT', 92), (3, 'CSC', 74),
(4, 'MAT', 89);

Aarav has two results; Meera, Kabir and Sana have one each; Vihaan has none.

Students Having at Least One Result

SELECT s.student_id, s.student_name
FROM sq_students AS s
WHERE EXISTS (
  SELECT 1
  FROM sq_results AS r
  WHERE r.student_id = s.student_id
)
ORDER BY s.student_id;

Aarav appears once even though two inner rows match. EXISTS answers a Boolean question for each outer row and stops needing additional matches once existence is known.

Students Without Any Result

SELECT s.student_id, s.student_name
FROM sq_students AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM sq_results AS r
  WHERE r.student_id = s.student_id
);

This anti-match form is robust because it correlates on the key. The equivalent LEFT JOIN pattern tests WHERE r.student_id IS NULL, but NOT EXISTS often expresses “no related row” more directly.

Relational Division: Took Every Subject

SELECT s.student_id, s.student_name
FROM sq_students AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM sq_subjects AS sub
  WHERE NOT EXISTS (
    SELECT 1
    FROM sq_results AS r
    WHERE r.student_id = s.student_id
      AND r.subject_code = sub.subject_code
  )
)
ORDER BY s.student_id;

Read it as: there is no required subject for which Aarav lacks a result. Only Aarav has both CSC and MAT. Be careful with an empty requirements table: mathematically every student satisfies “all zero required subjects,” which may or may not match the business rule.

EXISTS Versus JOIN, IN and NOT IN

A JOIN from students to results returns Aarav twice unless grouped or deduplicated because there are two result rows. EXISTS retains each qualifying student once without hiding cardinality using DISTINCT.

Indexes, EXPLAIN and Practice

• Index the inner correlation columns. The primary key (student_id, subject_code) supports this lesson's lookups.
• For queries driven first by subject_code, an additional index beginning with subject_code may help.
• Use EXPLAIN; MySQL may implement eligible EXISTS/IN forms using semijoin strategies.
• Keep the subquery predicate selective and verify keys/data types.

Practice: find students having Mathematics; find students without Computer Science; find subjects taken by no student; then add a third required subject and predict the double-NOT-EXISTS result before running it.

Official References

• MySQL 8.4: EXISTS and NOT EXISTS
• MySQL 8.4: Subquery Optimization
• MySQL 8.4: Subquery Restrictions

Syntax and behavior were checked against the official MySQL 8.4 manual. Test every query on a disposable copy before production use.