Negating a condition in SQL

NOT reads well until NULLs arrive, and then it quietly drops rows.

SELECT
    id,
    status,
    total
FROM orders
WHERE status NOT IN ('canceled', 'refunded')
    AND status <> 'draft'
    AND shipped_at IS NOT NULL;

How it works

1. NOT IN and NOT LIKE negate the whole predicate.
2. <> is the portable not-equal; != works in most engines too.
3. NOT IN with a NULL in the list matches nothing at all.

Keywords and builtins used here

• AND
• FROM
• IN
• IS
• NOT
• NULL
• SELECT
• WHERE

The run, in numbers

Lines

8

Characters to type

131

Tokens

28

Three-star pace

75 tpm

At the three-star pace of 75 tokens a minute, this run takes about 22 seconds.

Type this snippet

Step 5 of 5 in Filtering, step 13 of 23 in Language basics.

← Previous Next →