SQL Set Operations & Conditional Logic: UNION, CASE, NULL Handling and Advanced SQL Patterns

SQL set operations provide a way to combine the results of multiple queries vertically, while conditional expressions allow SQL to express branching logic directly inside a query. UNION combines result sets and removes duplicates, whereas UNION ALL simply appends the rows and is generally the better default when duplicate elimination is unnecessary. INTERSECT returns rows present in both result sets, while EXCEPT returns rows present in the first result but absent from the second; Oracle uses MINUS for the latter operation. Set operations require the same number of columns with compatible types, and columns are matched by position rather than name. The first SELECT determines the output column names, while a final ORDER BY applies to the combined result.
A common source of SQL errors is confusing set operations with joins. Set operations stack rows with the same structure, whereas joins combine related tables horizontally by adding attributes and can change row counts through fan-out. CASE addresses a different problem: it returns a value based on conditions and can therefore be used in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, and aggregate expressions. SQL supports both searched CASE, which evaluates arbitrary Boolean conditions from top to bottom, and simple CASE, which compares one expression against multiple values. The first matching branch wins, so condition ordering matters. If ELSE is omitted, unmatched rows produce NULL, and all branches must return compatible types.
SQL’s handling of NULL is based on three-valued logic: a condition can evaluate to TRUE, FALSE, or UNKNOWN. Comparisons involving NULL normally produce UNKNOWN, which explains why NULL = NULL is not true and why = NULL should be replaced with IS NULL. This behaviour becomes especially important with NOT IN, because a NULL in the comparison list can make the predicate evaluate to UNKNOWN and prevent rows from qualifying. SQL provides several tools for controlling this behaviour. COALESCE returns the first non-NULL argument, NULLIF converts a specified value into NULL, and IS DISTINCT FROM provides NULL-safe equality semantics. Together, these functions support fallback values, data cleaning, safe division, outer-join reporting, and comparisons involving nullable columns.
These features also enable several practical SQL patterns without requiring procedural code. Conditional aggregation with SUM(CASE...) can create portable static pivots, transforming categories such as quarters into separate columns. CASE can implement custom business-priority sorting, create age or revenue buckets, perform conditional updates, and construct optional filters. For data reconciliation, running EXCEPT in both directions reveals rows missing from either system; the two difference sets can then be combined and paired with a FULL OUTER JOIN to produce a labelled report showing missing, extra, or differing records. The presentation also highlights an important performance consideration: UNION and EXCEPT require duplicate elimination, typically through sorting or hashing, while UNION ALL performs a straightforward append. For large datasets, an indexed NOT EXISTS anti-join can sometimes be a more efficient alternative to EXCEPT.
The key to reliable SQL set and conditional logic is understanding exactly how rows, values, and NULL states behave. Use UNION ALL when duplicate removal is not required, explicitly parenthesise mixed set-operation chains because INTERSECT has higher precedence than UNION and EXCEPT, order CASE conditions from narrow to broad, and provide an ELSE when an unmatched result should not become NULL. For safe ratios, the presentation recommends the idiom COALESCE(a / NULLIF(b, 0), 0), which prevents division by zero while supplying a fallback value. Finally, remember that COUNT(column) ignores NULL values whereas COUNT(*) counts every row. These principles make set operations predictable, conditional logic expressive, and NULL-heavy SQL substantially easier to reason about.