SQL Operators

Operators are small, and almost every real-world query bug traces back to one of them. Wrong precedence, a NULL where you assumed a value, a BETWEEN that stopped one second early. Each one returns rows without complaint, just the wrong rows.

Arithmetic and comparison#

Arithmetic works as you expect: + - * / %, with ^ for powers in PostgreSQL. Remember that integer division truncates.

SELECT 7 / 2 AS int_div, 7 / 2.0 AS decimal_div, 7 % 2 AS remainder;

 int_div |     decimal_div     | remainder
---------+---------------------+-----------
       3 |  3.5000000000000000 |         1

Comparison operators are =, <> (or !=), <, >, <=, >=. Note = for equality, not ==. Assignment in UPDATE uses the same =, and the context tells them apart.

Logical operators and precedence#

AND, OR and NOT combine conditions. Their precedence is the trap: NOT binds first, then AND, then OR.

A user asks for customers in the USA or India who live in Austin. A natural but wrong attempt:

SELECT full_name, city, country
FROM customers
WHERE country = 'USA' OR country = 'India' AND city = 'Austin';

Because AND binds tighter, this means USA OR (India AND Austin). You get every American customer plus nobody from India:

 full_name  |   city    | country
------------+-----------+---------
 Ben Carter | Austin    | USA
 Grace Lin  | Seattle   | USA
 Jon Smith  | Austin    | USA

The fix is parentheses, and use them whenever AND and OR share a WHERE, even if you are sure about the precedence.

WHERE (country = 'USA' OR country = 'India') AND city = 'Austin';

 full_name  |  city  | country
------------+--------+---------
 Ben Carter | Austin | USA
 Jon Smith  | Austin | USA
(2 rows)

NULL and three-valued logic#

NULL means "unknown". Any comparison with an unknown is itself unknown, not true or false.

SELECT NULL = NULL;     -- NULL, not TRUE
SELECT NULL <> 5;       -- NULL
SELECT 5 = NULL;        -- NULL

WHERE keeps only rows where the condition is TRUE. Unknown is discarded, exactly like false. That is why WHERE phone = NULL returns nothing, ever.

Use the dedicated test:

SELECT full_name, phone FROM customers WHERE phone IS NULL;

   full_name   | phone
---------------+-------
 Chloe Dubois  |
 Elena Petrova |
 Isla Murphy   |
(3 rows)

Logic with unknowns follows a truth table. Learn it once.

NOT UNKNOWN is UNKNOWN. So negating a condition does not recover the rows it dropped. WHERE phone = '+1' and WHERE NOT (phone = '+1') both exclude the three NULL-phone customers.

When you want NULL to compare like an ordinary value, IS DISTINCT FROM treats two NULLs as equal:

SELECT * FROM customers WHERE phone IS DISTINCT FROM '+91-98450-11122';
-- returns everyone except that one number, including the NULL-phone rows

IN#

IN tests membership in a list, and replaces a chain of ORs.

SELECT order_id, status FROM orders WHERE status IN ('paid', 'pending') ORDER BY order_id;

 order_id | status
----------+---------
        8 | paid
       12 | pending
       14 | pending

IN also accepts a subquery, which is the main reason to use it.

The NOT IN trap#

NOT IN is where NULL does its worst damage. Find employees who do not manage anyone:

SELECT full_name FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

You expect six people. You get none.

NOT IN (1, 2, NULL) expands to NOT (x = 1 OR x = 2 OR x = NULL). For any x not in the list, the first two comparisons are false and the third is unknown, so the whole thing is unknown and the row is discarded. One NULL in the subquery, here Meera Iyer's missing manager, empties the result.

Two safe fixes:

-- Exclude NULLs from the subquery
SELECT full_name FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

-- Or use NOT EXISTS, which does not have the problem
SELECT e.full_name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id);

Both return the six non-managers. Prefer NOT EXISTS for anything that can contain NULL.

BETWEEN#

BETWEEN a AND b is inclusive on both ends, equal to >= a AND <= b.

SELECT order_id, order_date, status FROM orders
WHERE order_date BETWEEN '2024-03-01' AND '2024-04-30'
ORDER BY order_date;

 order_id | order_date |  status
----------+------------+-----------
        5 | 2024-03-02 | cancelled
        2 | 2024-03-15 | delivered
        6 | 2024-03-18 | delivered
        7 | 2024-04-05 | delivered
        8 | 2024-04-11 | paid
        9 | 2024-04-25 | delivered

On a DATE column that is fine. On a TIMESTAMP it quietly loses data. BETWEEN '2024-03-01' AND '2024-03-31' means up to midnight at the start of March 31st, so everything that happened on the 31st after 00:00 is gone.

For timestamps, use a half-open range:

WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01'

It also lets you chain months with no gaps and no overlaps.

LIKE and pattern matching#

LIKE matches text against a pattern. % stands for any run of characters, _ for exactly one.

SELECT name FROM products WHERE name LIKE '%Desk%';

     name
---------------
 Desk Lamp
 Standing Desk

LIKE is case-sensitive in PostgreSQL. ILIKE ignores case. A pattern with a leading % cannot use a normal index, because the database has no starting point to look up. That is fine on 11 rows and slow on 50 million.

WHERE email ILIKE 'a%'          -- starts with a or A
WHERE name LIKE '_ouse'         -- exactly five characters ending in "ouse"
WHERE note LIKE '100\%%'        -- literal percent sign, escaped

For richer patterns PostgreSQL has regular expressions (~, ~*) and SIMILAR TO. Use them when LIKE runs out.

Concatenation#

|| joins strings. A NULL on either side makes the whole result NULL.

SELECT full_name || ' (' || city || ')' AS label FROM customers LIMIT 2;
SELECT 'ID: ' || NULL;                         -- NULL
SELECT CONCAT('ID: ', NULL);                   -- 'ID: '  (CONCAT ignores NULL)

Common mistakes#

Mixing AND and OR without parentheses. The result looks plausible and is wrong.

Writing = NULL or <> NULL. Always IS NULL or IS NOT NULL.

NOT IN against a column that can be NULL. Zero rows, no error. Use NOT EXISTS.

BETWEEN on timestamps. Use >= and < against the next boundary.

Leading wildcards on big tables. LIKE '%term%' forces a full scan. Use full-text search or a trigram index when you need it fast.

Practice#

1. Find the customers in India or Japan. Then add a condition for a city without breaking the logic. Use parentheses and confirm your count by hand.
2. List customers whose phone is not null and who live outside the USA. You should get 4 rows.
3. Predict, then run, the result of SELECT COUNT(*) FROM employees WHERE manager_id <> 1;. Which employees are missing and why?
4. Rewrite the NOT IN employee query so it returns the six non-managers, in two different ways.
5. Find all orders placed on or after 1 April 2024 and before 1 June 2024 without using BETWEEN. You should get 6 orders.