Operators

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  status = 'active'
  AND age >= 18
  AND age < 65
  AND score > 75

-- Without parentheses: AND binds tighter than OR
WHERE a = 1 OR b = 2 AND c = 3
-- Equivalent to: a = 1 OR (b = 2 AND c = 3)

-- With parentheses for clarity
WHERE (a = 1 OR b = 2) AND c = 3

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  (status = 'active' OR status = 'pending')
  AND NOT is_deleted
  AND (score > 90 OR is_premium = true)

SELECT
  quantity * unit_price AS line_total,
  (quantity * unit_price) * 1.0825 AS total_with_tax,
  total_amount / num_items AS average_item_price,
  score - baseline AS improvement
FROM company_data."123"

SELECT
  first_name || ' ' || last_name AS full_name,
  city || ', ' || state AS location
FROM company_data."123"

SELECT
  'User #' || user_id AS user_label,
  'Score: ' || score || '%' AS score_display
FROM company_data."123"

column IN (value1, value2, value3)
column NOT IN (value1, value2)

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  status IN ('active', 'pending', 'review')
  AND category NOT IN ('test', 'demo', 'internal')

SELECT user_id, email, status
FROM company_data."1"
WHERE user_id IN (
  SELECT user_id
  FROM company_data."2"
  WHERE is_premium = true
)

column BETWEEN lower_bound AND upper_bound

column >= lower_bound AND column <= upper_bound

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  age BETWEEN 25 AND 45
  AND event_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND score BETWEEN 80 AND 100

-- Correct
WHERE email IS NOT NULL
WHERE phone IS NULL

-- Incorrect (always returns no rows)
WHERE email = NULL
WHERE email <> NULL

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  email IS NOT NULL
  AND phone IS NOT NULL
  AND secondary_email IS NULL

-- Useful for MERGE ON conditions
MERGE ON target.id IS NOT DISTINCT FROM source.id

-- In WHERE clauses
WHERE status IS NOT DISTINCT FROM 'active'

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  name LIKE 'John%'           -- Starts with "John"
  AND email LIKE '%@gmail.com' -- Ends with "@gmail.com"
  AND code LIKE 'A__'          -- "A" followed by exactly 2 characters

WHERE UPPER(name) LIKE 'JOHN%'
WHERE LOWER(email) LIKE '%@gmail.com'

WHERE email NOT LIKE '%@test.com'

timestamp_column + INTERVAL 'n' unit
timestamp_column - INTERVAL 'n' unit

SELECT user_id, email, status, age
FROM company_data."123"
WHERE
  -- Last 30 days
  event_timestamp > CURRENT_DATE - INTERVAL '30' DAY

  -- Last 3 months
  AND created_at >= CURRENT_DATE - INTERVAL '3' MONTH

  -- Events in the next 7 days
  AND scheduled_date <= CURRENT_DATE + INTERVAL '7' DAY

SELECT
  event_timestamp,
  event_timestamp + INTERVAL '1' HOUR AS one_hour_later,
  DATE_TRUNC('month', event_timestamp) AS month_start,
  DATE_TRUNC('month', event_timestamp) + INTERVAL '1' MONTH - INTERVAL '1' DAY AS month_end
FROM company_data."123"

SELECT
  -- Arithmetic: multiplication before addition
  price + quantity * discount,       -- = price + (quantity * discount)
  (price + quantity) * discount,     -- Override precedence

  -- Logical: AND before OR
  a OR b AND c,                      -- = a OR (b AND c)
  (a OR b) AND c                     -- Override precedence
FROM company_data."123"