Skip to main content

​
Comparison Operators

Standard equality and inequality operators: 
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

​
BETWEEN / NOT BETWEEN

SELECT * FROM myschema.orders WHERE total BETWEEN 50 AND 200
SELECT * FROM myschema.orders WHERE total NOT BETWEEN 50 AND 200
BETWEEN a AND b is inclusive on both bounds.

​
IN / NOT IN with Value List

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

​
IN / NOT IN with Subquery

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

​
IN / NOT IN with Row Value Constructor

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

​
EXISTS / NOT EXISTS (Correlated Subquery)

-- 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
)

​
LIKE / NOT LIKE

Two wildcard characters are supported:
WildcardMatches
%Any sequence of zero or more characters
_Exactly one character
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_%"
Use ESCAPE when you need literal %, _, or the escape character itself inside the pattern. Escaping is explicit only: there is no implicit backslash escape mode.

​
IS NULL / IS NOT NULL

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

​
Not Supported

  • IS DISTINCT FROM / IS NOT DISTINCT FROM
  • ANY / ALL quantifiers (e.g., WHERE x > ANY (SELECT ...))