Difference

Definition

Difference (symbol: −, sometimes written \) returns the rows that appear in the FIRST relation but NOT in the second — order matters, unlike Union and Intersection. A − B is generally not the same as B − A.

How It Works

sql
SELECT student_id FROM honors_students
EXCEPT
SELECT student_id FROM deans_list
ORDER BY student_id;

This returns honors students who are NOT on the dean's list. Standard SQL's keyword is EXCEPT (PostgreSQL, SQL Server, modern MySQL); Oracle historically used MINUS for the identical operation — a good example of the same relational-algebra concept getting different SQL spellings across vendors (see Chapter 8's SQL Dialects).

Edge Cases and Pitfalls

• Order genuinely matters: honors EXCEPT deans (honors students excluded from deans) is a completely different result from deans EXCEPT honors (deans students excluded from honors) — unlike Union/Intersection, which don't care about operand order.
• A common real use of Difference is finding "what's missing" — e.g. all_required_courses EXCEPT completed_courses finds exactly the courses a student still needs, a pattern closely related to the Division operation (7.12) for more complex "must satisfy all of" questions.
• Just like UNION/INTERSECT, the two relations combined with EXCEPT/MINUS must be union-compatible (same column count, compatible types).

Key Takeaways

• Difference (−) returns rows in the first relation but not the second; operand order matters.
• SQL spells it EXCEPT (most dialects) or MINUS (Oracle) — same operation, different keyword.
• "What's missing" queries (required minus completed) are a classic real-world use of Difference.