SQL comparison results are not two-valued TRUE / FALSE; they are three-valued TRUE / FALSE / UNKNOWN. When NULL is involved, both = and <> produce UNKNOWN, and a WHERE clause passes only TRUE rows. In other words, if you write status <> 'cancelled', rows whose status is NULL disappear from the result without an error or warning.

-- ✗ NULL <> 'cancelled' is UNKNOWN → it cannot pass WHERE and silently disappears
WHERE status <> 'cancelled'

-- ✓ Compare NULL as “one value” → the result is always TRUE/FALSE
WHERE status IS DISTINCT FROM 'cancelled'

-- ✓ Traditional form for compatibility (same meaning)
WHERE (status <> 'cancelled' OR status IS NULL)

The status column in orders allows NULL (orders migrated from a legacy system have an unset status = NULL). Retrieve all orders other than cancelled orders. Include orders whose status is NULL as “not cancelled”. The output columns must be order_id, status, amount (order_id ascending).

※ Before looking at the answer, predict how many rows WHERE status <> 'cancelled' returns.

Expected output (order_id ascending):

With only <>, the two NULL rows (ids 3 and 6) silently disappear, leaving only 2 rows. In a sales aggregate, that would omit 2800 yen.

SELECT   order_id, status, amount
FROM     orders
WHERE    status IS DISTINCT FROM 'cancelled'  -- NULL is also judged to be “different from cancelled”
ORDER BY order_id;

-- Alternative (traditional form that works on older DBMSs): WHERE (status <> 'cancelled' OR status IS NULL)

/*
  Execution order:
  1. FROM: scan orders
  2. WHERE: evaluate status IS DISTINCT FROM 'cancelled' for each row
     → NULL is treated as a “value” in a two-valued comparison, so the result is always TRUE / FALSE (never UNKNOWN)
     → NULL rows also become TRUE and pass
  3. SELECT: finalize the three columns
  4. ORDER BY: sort by order_id ascending
  → Result: 4 rows × 3 columns (nothing is missed)
*/

If you write “orders from May 2026” with DATE_TRUNC or EXTRACT, the column is wrapped in a function and the index cannot be used (the function must be evaluated for every row before comparison). A period is really a continuous interval on the time axis, so rewriting it as the half-open interval >= start AND < end lets a B-tree Range Scan work directly. In addition, BETWEEN on a timestamp column has a correctness trap that misses data on the last day.

-- ✗ Wrap the column in a function → evaluate every row (no index)
WHERE DATE_TRUNC('month', ordered_at) = DATE '2026-05-01'

-- ✗ BETWEEN's upper bound is '2026-05-31 00:00:00' → daytime data on 5/31 disappears
WHERE ordered_at BETWEEN '2026-05-01' AND '2026-05-31'

-- ✓ Half-open interval [5/1, 6/1): the index works and nothing is missed
WHERE ordered_at >= DATE '2026-05-01'
AND   ordered_at <  DATE '2026-06-01'

The ordered_at column in orders (8 million actual rows) is a timestamp with an index named idx_orders_ordered_at. Aggregate the number of orders and total amount for May 2026 in a form that enables an Index Range Scan and does not miss end-of-month data. The output columns must be order_cnt, total_amount.

※ Pay special attention to order_id=104 (18:45 on 5/31). What happens to it if you write BETWEEN '2026-05-01' AND '2026-05-31'?

Expected output:

The matching ids are 102, 103, 104, and 106: 4 orders. The BETWEEN version drops 104 and produces 3 orders / 2500 yen, silently corrupting the monthly report.

SELECT   COUNT(*)     AS order_cnt,
         SUM(amount)  AS total_amount
FROM     orders
WHERE    ordered_at >= DATE '2026-05-01'   -- include the start
AND      ordered_at <  DATE '2026-06-01';  -- exclude the end (half-open interval)

/*
  Execution order:
  1. Range condition in WHERE  → choose idx_orders_ordered_at
  2. Binary search the B-tree  → jump to the start of the interval
  3. Scan continuously in time order → stop at the end of the interval
  4. COUNT / SUM only within the interval → aggregate in one pass
  */

A one-to-many JOIN duplicates each row in the left table once for every matching row on the right (fan-out). If you write “list customers who have placed an order” with a JOIN, each customer is duplicated by their order count and then those rows are collapsed with DISTINCT—in other words, you create a large number of duplicates and then pay to remove them. If all you need is an existence check, EXISTS (a Semi Join) is correct: stop searching as soon as the first match is found and never create the duplicates.

-- ✗ Inflate rows with JOIN, then cover it with DISTINCT
SELECT DISTINCT u.user_id, u.name
FROM users u JOIN orders o ON o.user_id = u.user_id

-- ✓ EXISTS: tell the DB that finding one match is enough (Semi Join)
SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id)

From users (100,000 actual rows) and orders (5 million actual rows, indexed on user_id), retrieve the list of customers who have placed at least one order. Write it so that duplicate rows are never created (do not use DISTINCT). The output columns must be user_id, name (user_id ascending).

※ Imagine how many rows JOIN + DISTINCT would create and then reduce with real data averaging 50 orders per customer.

Expected output (user_id ascending):

Ito, who has no orders, is excluded. The EXISTS version creates no duplicate rows at all and stops searching orders at the first match for each customer.

SELECT   u.user_id, u.name
FROM     users u
WHERE EXISTS (
           SELECT 1                      -- only the existence truth value is needed (the value is unused)
           FROM   orders o
           WHERE  o.user_id = u.user_id
         )
ORDER BY u.user_id;

/*
  Execution order:
  1. FROM users         → scan the outer table
  2. WHERE EXISTS       → stop after one hit through the index (Semi Join)
  3. Only TRUE users pass → no joined rows are created, so there are no duplicates
  4. SELECT / ORDER BY   → finalize two columns in user_id ascending order
  */

For a column with an extremely skewed distribution, such as status with 99% 'done' and 0.5% 'pending', a normal index on every row is dead weight: most of its entries are never read. A partial index uses CREATE INDEX ... WHERE condition to index only rows that satisfy the condition. Its size drops dramatically, and write cost is limited to the target rows.

-- Normal index: entries for 1 million rows (99% are unused 'done')
CREATE INDEX idx_tasks_status ON tasks (status);

-- Partial index: only about 5,000 'pending' rows, ordered by created_at
CREATE INDEX idx_tasks_pending ON tasks (created_at)
WHERE status = 'pending';

In the tasks table (1 million actual rows, 99% are 'done' and about 0.5% are 'pending'), a screen that displays 20 unprocessed tasks in oldest-created-first order is called frequently. Create a partial index dedicated to this screen and write a SELECT that can use it. The output columns must be task_id, title, created_at.

※ The key question is which column to use as the key so the index can also eliminate the ORDER BY created_at sort.

Expected output (created_at ascending, up to 20 rows):

Even with a 1-million-row table, the query reads only the first up to 20 entries of a small index containing pending tasks in created_at order.

CREATE INDEX idx_tasks_pending
ON tasks (created_at)           -- key = the ordering column (eliminate the sort)
WHERE status = 'pending';       -- index only the minority 'pending' rows

SELECT   task_id, title, created_at
FROM     tasks
WHERE    status = 'pending'      -- same as the index WHERE condition → the partial index can be used
ORDER BY created_at
LIMIT    20;

/*
  Execution order:
  1. Check implication between WHERE and the index condition → adopt the partial index
  2. Read idx_tasks_pending from the beginning → already ordered by created_at ascending
  3. Fetch columns from the heap and stop at LIMIT → no sort stage
  */