WHERE Predicates

SELECT * FROM myschema.orders WHERE status = 'pending'
SELECT * FROM myschema.orders WHERE status != 'cancelled'
SELECT * FROM myschema.orders WHERE status <> 'cancelled'   -- synonym for !=
SELECT * FROM myschema.orders WHERE total > 100
SELECT * FROM myschema.orders WHERE total >= 50
SELECT * FROM myschema.orders WHERE total < 1000
SELECT * FROM myschema.orders WHERE total <= 999.99

SELECT * FROM myschema.orders WHERE total BETWEEN 50 AND 200
SELECT * FROM myschema.orders WHERE total NOT BETWEEN 50 AND 200

SELECT * FROM myschema.orders WHERE status IN ('pending', 'processing', 'shipped')
SELECT * FROM myschema.orders WHERE status NOT IN ('cancelled', 'refunded')

SELECT * FROM myschema.customers
WHERE id IN (SELECT customer_id FROM myschema.orders WHERE total > 500)

SELECT * FROM myschema.shipments
WHERE (origin, destination) IN (('NYC', 'LAX'), ('SFO', 'ORD'))

-- Customers who have placed at least one order
SELECT c.id, c.name
FROM myschema.customers AS c
WHERE EXISTS (
  SELECT 1 FROM myschema.orders AS o WHERE o.customer_id = c.id
)

-- Customers with no orders
SELECT c.id, c.name
FROM myschema.customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM myschema.orders AS o WHERE o.customer_id = c.id
)

SELECT * FROM myschema.products WHERE name LIKE 'Widget%'   -- starts with "Widget"
SELECT * FROM myschema.products WHERE name LIKE '%Pro%'      -- contains "Pro"
SELECT * FROM myschema.products WHERE sku LIKE 'A__-%%'      -- "A" + 2 chars + "-" + anything
SELECT * FROM myschema.products WHERE sku NOT LIKE 'DISC-%'
SELECT * FROM myschema.products WHERE sku LIKE 'A#_%' ESCAPE '#'      -- starts with literal "A_"
SELECT * FROM myschema.products WHERE sku LIKE 'A#_#%' ESCAPE '#'     -- exactly literal "A_%"

SELECT * FROM myschema.orders WHERE shipped_at IS NULL
SELECT * FROM myschema.orders WHERE shipped_at IS NOT NULL