SQL Querying

SELECT Expressions and Filtering

SELECT derives the columns of a result, while WHERE chooses source rows whose search condition is true. Correct queries make expression meaning, null behavior, precedence, and duplicate intent explicit.
  • Projection is expression evaluation, not merely column selection.
  • WHERE retains only rows for which its condition evaluates to TRUE; both FALSE and UNKNOWN are rejected.
  • Null needs dedicated predicates.
  • Parenthesize mixed AND and OR conditions to expose the intended grouping rather than relying on remembered precedence.
  • DISTINCT applies to the complete projected row and can hide an accidental many-to-many multiplication rather than fix it.
  • Filtering expressions influence access paths.

Derive a useful result after choosing rows

Filtered product projection
SELECT p.product_id,
       p.name,
       p.current_price,
       CASE
         WHEN p.current_price < 25 THEN 'budget'
         WHEN p.current_price < 100 THEN 'standard'
         ELSE 'premium'
       END AS price_band
FROM products AS p
WHERE p.current_price >= 10
  AND p.current_price < 500;

The filter first keeps products priced from 10 up to, but not including, 500. For each survivor, the CASE expression derives exactly one price band. A price of 20 becomes budget; a null current_price is removed because both comparisons are unknown.

Common expression roles
ConstructRoleCommerce example
AliasName an output expressionquantity * unit_price AS line_total
CASEConditional resultMap price ranges to display bands
COALESCEFirst non-null valueShow a fallback category label
IS NULLTest missing markerFind products not discontinued
DISTINCTRemove duplicate result rowsUnique customer IDs, when uniqueness is truly intended