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

Use % as a multi-character wildcard: 
SELECT * FROM myschema.products WHERE name LIKE 'Widget%'
SELECT * FROM myschema.products WHERE name LIKE '%Pro%'
SELECT * FROM myschema.products WHERE sku NOT LIKE 'DISC-%'
The _ single-character wildcard is not currently supported. Only % is implemented.

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