BETWEEN a AND b is equivalent to col >= a AND col <= b and specifies a range that includes both boundary values. It works with numbers, dates, and strings.

SELECT * FROM orders
WHERE  ordered_at BETWEEN '2024-01-01' AND '2024-03-31';
-- ↑ equivalent to ordered_at >= '2024-01-01' AND ordered_at <= '2024-03-31'

From the orders table, retrieve orders where ordered_at falls from January 1 through March 31, 2024 (Q1). Return order_id, user_id, amount, status, ordered_at, ordered by ordered_at ascending.

Expected output (ordered_at ascending):

Note: order 103 (2024-04-05: after the range) and order 104 (2023-12-01: before the range) are excluded.

SELECT
  order_id, user_id, amount, status, ordered_at
FROM   orders
WHERE  ordered_at BETWEEN '2024-01-01' AND '2024-03-31'
--                ↑ equivalent to >= '2024-01-01' AND <= '2024-03-31'
--                  DATE includes both endpoints. For TIMESTAMP, note that '2024-03-31 00:00:00' is the upper bound.
ORDER BY ordered_at;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                            → read 6 rows
  2. WHERE ordered_at BETWEEN ...           → filter down to 4 rows
  3. SELECT order_id, user_id, amount, ...  → select 5 columns
  4. ORDER BY ordered_at                    → sort by date ascending
  */

The LIKE predicate is used for string pattern matching. % (percent) matches any string of zero or more characters, while _ (underscore) matches exactly one character.

WHERE name LIKE 'Smart%'  -- prefix match: starts with "Smart"
   OR name LIKE '%Pro'      -- suffix match: ends with "Pro"
   OR name LIKE '%Air%'     -- infix match: contains "Air" (cannot use an index)
   OR name LIKE '__blet';  -- _ is one arbitrary character (two characters + blet)

From the products table, retrieve products whose name starts with "Smart" or whose name ends with "Pro". Return product_id, name, category, price, ordered by price ascending.

Expected output (price ascending):

Note: product 2 matches both "Smart%" and "%Pro". Because this is an OR condition, it is output once without duplication.

SELECT
  product_id, name, category, price
FROM   products
WHERE  name LIKE 'Smart%'  -- prefix match: starts with "Smart"
    OR name LIKE '%Pro'       -- suffix match: ends with "Pro"
ORDER BY price;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products                 → read 6 rows
  2. WHERE name LIKE ... OR ...    → filter down to 3 rows
  3. SELECT product_id, name, ...  → select 4 columns
  4. ORDER BY price                → sort by price ascending
  */

SQL uses TRUE / FALSE / UNKNOWN three-valued logic rather than two-valued TRUE / FALSE logic. NULL means "an unknown value": both NULL = NULL and NULL <> NULL evaluate to UNKNOWN. Always use IS NULL or IS NOT NULL to check for NULL.

-- ✗ Incorrect: = cannot be used with NULL (always UNKNOWN → 0 rows)
WHERE coupon_code = NULL

-- ✓ Correct: check with IS NULL
WHERE coupon_code IS NULL

-- ✓ Retrieve non-NULL rows
WHERE coupon_code IS NOT NULL

From the orders table, retrieve orders where coupon_code is NULL (no coupon used). Return order_id, user_id, amount, coupon_code, ordered_at, ordered by order_id ascending.

Expected output (order_id ascending):

Note: orders 101 (SUMMER10) and 104 (WINTER20) are excluded because their coupons were used.

SELECT
  order_id, user_id, amount, coupon_code, ordered_at
FROM   orders
WHERE  coupon_code IS NULL   -- Always use IS NULL for a NULL check (= NULL is always UNKNOWN)
ORDER BY order_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                    → read 5 rows
  2. WHERE coupon_code IS NULL      → filter down to 3 rows
  3. SELECT order_id, user_id, ...  → select 5 columns
  4. ORDER BY order_id              → sort by order_id ascending

  ▸ Three-valued logic reminder:
      'SUMMER10' IS NULL  → FALSE   → excluded
      NULL       IS NULL  → TRUE    → passes
      NULL = NULL          → UNKNOWN → excluded (a common mistake)
*/

WHERE col IN (v1, v2, …) is shorthand for col = v1 OR col = v2 OR …. It expresses multiple matches cleanly by listing the accepted values.

WHERE category IN ('PC', 'Tablet')
-- ↑ equivalent to category = 'PC' OR category = 'Tablet'

From the products table, retrieve products whose category is 'Smartphone', 'Tablet', or 'PC'. Return product_id, name, category, price, stock, ordered by category ascending and then price ascending.

Expected output (category ascending → price ascending):

Note: products 4 and 5 are excluded because their category is 'Accessories', which is outside the IN list.

SELECT
  product_id, name, category, price, stock
FROM   products
WHERE  category IN ('Smartphone', 'Tablet', 'PC')
ORDER BY category, price;                      -- ↑ equivalent to category = 'Smartphone' OR category = 'Tablet' OR category = 'PC'

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products                 → read 6 rows
  2. WHERE category IN (...)       → filter down to 4 rows
  3. SELECT product_id, name, ...  → select 5 columns
  4. ORDER BY category, price      → category ascending → price ascending
  */