Filtering, NULL and Functions

Pattern matching with LIKE

The LIKE operator compares text with a pattern containing two wildcards: % stands for any run of characters (including none) and _ for exactly one character. It is the tool for "starts with", "contains" and "ends with" questions:

-- Names that start with "USB"
SELECT name FROM products WHERE name LIKE 'USB%' ORDER BY name;

-- Names that contain "cable" anywhere
SELECT name FROM products WHERE name LIKE '%cable%' ORDER BY name;

-- Last names whose second letter is "e"
SELECT first_name, last_name FROM customers WHERE last_name LIKE '_e%'
ORDER BY customer_id;

name
---------------
USB-C cable 2 m
USB-C hub

name
------------------
Ethernet cable 5 m
HDMI cable 3 m
USB-C cable 2 m

first_name  last_name
----------  ---------
Sanne       de Vries
Kwame       Mensah

NOT LIKE reverses the test. To search for a literal % or _, declare an escape character: LIKE '%50!%%' ESCAPE '!' finds text containing "50%": the ! makes the % after it an ordinary character.

Case sensitivity differs

Here the products disagree, and it matters when you move a query from one to another. In SQLite, LIKE ignores the difference between upper and lower case, but only for the ASCII letters A–Z; for a case-sensitive match SQLite offers GLOB, which uses Unix-style wildcards (* and ?):

SELECT name FROM products WHERE name LIKE 'usb%' ORDER BY name;

SELECT name FROM products WHERE name GLOB 'usb*' ORDER BY name;

name
---------------
USB-C cable 2 m
USB-C hub

name
----

A portable way to search without regard to case is to convert both sides: WHERE LOWER(name) LIKE '%usb%'.

IN and BETWEEN

IN tests whether a value is in a list: a shorter and safer way to write several OR conditions on one column. NOT IN is the opposite.

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

SELECT name, category
FROM products
WHERE category NOT IN ('Cables', 'Peripherals')
ORDER BY name;

order_id  status
--------  -------
104       shipped
107       shipped
108       pending
110       pending

name                      category
------------------------  -----------
27-inch monitor           Displays
Laptop stand              Accessories
Noise-cancelling headset  Audio
USB-C hub                 Accessories

BETWEEN low AND high tests a range and is inclusive: it means >= low AND <= high. Both boundary values are part of the result, as the prices 25 and 89 below show. It works for numbers, text and ISO dates:

SELECT name, price
FROM products
WHERE price BETWEEN 25 AND 89
ORDER BY price;

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2026-07-01' AND '2026-07-31'
ORDER BY order_date;

name                 price
-------------------  -----
Wireless mouse       25
Laptop stand         35
USB-C hub            45
Webcam 1080p         59
Mechanical keyboard  89

order_id  order_date
--------  ----------
104       2026-07-01
105       2026-07-09
106       2026-07-21

NULL: a missing value

One customer, Priya, has no city in the database. Her city is NULL: a marker meaning the value is unknown, missing or not applicable. NULL is not zero, not an empty string and not the text 'NULL'; it is the absence of a value. A column only accepts NULL if it is not declared NOT NULL, which is why city can be empty and email cannot.

Because NULL is unknown, no comparison with it can be true. Is an unknown city equal to an unknown city? Unknown. So = NULL never matches, not even for the row that is NULL. SQL has a special test, IS NULL (and IS NOT NULL):

-- Wrong: matches nothing
SELECT first_name, city FROM customers WHERE city = NULL;

-- Right
SELECT first_name, city FROM customers WHERE city IS NULL;

first_name  city
----------  ----

first_name  city
----------  ----
Priya       NULL

The less obvious consequence: a row with NULL also fails the opposite test. Asked for "customers not in Utrecht", the database leaves Priya out, because it cannot know that her unknown city is not Utrecht:

SELECT first_name, city
FROM customers
WHERE city <> 'Utrecht'
ORDER BY customer_id;

first_name  city
----------  ---------
Kwame       Rotterdam
Mei         Amsterdam
Omar        Antwerp
Lena        Cologne
Ana         Lisbon

Two customers live in Utrecht, so of the eight customers you might expect six; you get five. If customers with an unknown city should be included, say so: WHERE city <> 'Utrecht' OR city IS NULL.

Three-valued logic

Ordinary Boolean logic has two values, true and false. SQL conditions have three: TRUE, FALSE and UNKNOWN, the result of any comparison involving NULL. This is three-valued logic. The rules follow from asking "could the unknown value change the outcome?":

SQLite shows TRUE as 1, FALSE as 0 and UNKNOWN as NULL, so you can see the rules at work:

SELECT NULL = NULL             AS null_eq_null,
       NULL <> 1               AS null_ne_1,
       (NULL = 1) OR (1 = 1)   AS unknown_or_true,
       (NULL = 1) AND (1 = 2)  AS unknown_and_false,
       NULL IS NULL            AS is_null;

null_eq_null  null_ne_1  unknown_or_true  unknown_and_false  is_null
------------  ---------  ---------------  -----------------  -------
NULL          NULL       1                0                  1

The rule that ties it together: WHERE keeps a row only when its condition is TRUE. FALSE and UNKNOWN are both rejected. That is why Priya disappeared from both "in Utrecht" and "not in Utrecht".

The NOT IN trap

city NOT IN ('Utrecht', 'Rotterdam') means city <> 'Utrecht' AND city <> 'Rotterdam'. If the list contains a NULL, one of those parts becomes city <> NULL, which is UNKNOWN for every row, and an AND with UNKNOWN can never be TRUE. The query returns nothing at all:

SELECT first_name, city
FROM customers
WHERE city NOT IN ('Utrecht', 'Rotterdam')
ORDER BY customer_id;

SELECT first_name, city
FROM customers
WHERE city NOT IN ('Utrecht', 'Rotterdam', NULL)
ORDER BY customer_id;

first_name  city
----------  ---------
Mei         Amsterdam
Omar        Antwerp
Lena        Cologne
Ana         Lisbon

first_name  city
----------  ----

Nobody types a NULL into a list on purpose. In practice the list comes from a subquery (a query inside a query, see Subqueries, CTEs and Views), and one NULL in its result silently empties the answer. "Customers who live in a city where no Dutch customer lives" should return Omar, Lena and Ana, but Priya's NULL city is among the Dutch cities:

-- Returns nothing: the subquery's result contains NULL
SELECT first_name, city
FROM customers
WHERE city NOT IN (SELECT city FROM customers WHERE country = 'NL')
ORDER BY customer_id;

-- Fixed: keep NULL out of the list
SELECT first_name, city
FROM customers
WHERE city NOT IN (SELECT city FROM customers
                   WHERE country = 'NL' AND city IS NOT NULL)
ORDER BY customer_id;

first_name  city
----------  ----

first_name  city
----------  -------
Omar        Antwerp
Lena        Cologne
Ana         Lisbon

COALESCE and CASE

COALESCE: a fallback for NULL

COALESCE takes a list of values and returns the first one that is not NULL. With a column and a fallback, it replaces missing values in the output:

SELECT first_name, COALESCE(city, 'unknown') AS city
FROM customers
ORDER BY customer_id;

first_name  city
----------  ---------
Sanne       Utrecht
Kwame       Rotterdam
Mei         Amsterdam
Omar        Antwerp
Lena        Cologne
Priya       unknown
Tom         Utrecht
Ana         Lisbon

The stored data does not change; only the result shows the fallback. COALESCE is standard SQL and works everywhere. Products also have two-argument versions with their own names: IFNULL (SQLite, MySQL), ISNULL (SQL Server) and NVL (Oracle).

CASE: choosing a value by conditions

A CASE expression is SQL's if-then-else. The database tests the WHEN conditions in order and returns the value of the first one that is true; if none is, it returns the ELSE value (or NULL without an ELSE). Here it sorts products into price bands:

SELECT name, price,
       CASE
         WHEN price < 20  THEN 'budget'
         WHEN price < 100 THEN 'mid-range'
         ELSE 'premium'
       END AS price_band
FROM products
ORDER BY price;

name                      price  price_band
------------------------  -----  ----------
Ethernet cable 5 m        7      budget
USB-C cable 2 m           9      budget
HDMI cable 3 m            12     budget
Wireless mouse            25     mid-range
Laptop stand              35     mid-range
USB-C hub                 45     mid-range
Webcam 1080p              59     mid-range
Mechanical keyboard       89     mid-range
Noise-cancelling headset  129    premium
27-inch monitor           249    premium

Because the first true condition wins, the second test does not need price >= 20 AND: cheaper products were already caught by the first. When every condition compares the same value with =, the shorter simple form names the value once:

SELECT order_id, status,
       CASE status
         WHEN 'pending'   THEN 'Waiting for payment'
         WHEN 'shipped'   THEN 'On its way'
         WHEN 'delivered' THEN 'Delivered'
         ELSE 'Cancelled'
       END AS status_text
FROM orders
WHERE customer_id IN (1, 3, 4, 6)
ORDER BY order_id;

order_id  status     status_text
--------  ---------  -------------------
101       delivered  Delivered
103       delivered  Delivered
104       shipped    On its way
105       cancelled  Cancelled
108       pending    Waiting for payment
109       delivered  Delivered

CASE is an expression, so it can appear anywhere a value can: in SELECT, WHERE, ORDER BY, and inside the aggregate functions of Aggregation and Grouping.

Text functions

Functions take values and return a new value, row by row. The most used text functions, on the shop's Dutch customers:

SELECT UPPER(last_name)          AS upper_name,
       LOWER(email)              AS lower_email,
       LENGTH(first_name)        AS name_length,
       SUBSTR(first_name, 1, 3)  AS short_name
FROM customers
WHERE country = 'NL'
ORDER BY customer_id;

upper_name  lower_email                name_length  short_name
----------  -------------------------  -----------  ----------
DE VRIES    sanne.devries@example.com  5            San
MENSAH      kwame.mensah@example.com   5            Kwa
CHEN        mei.chen@example.com       3            Mei
PATEL       priya.patel@example.com    5            Pri
JANSEN      tom.jansen@example.com     3            Tom

SUBSTR(text, start, length) counts from 1, not from 0 as Python does. TRIM removes spaces at both ends (LTRIM and RTRIM at one end), which matters for data typed in by people; REPLACE swaps every occurrence of a piece of text:

SELECT '[' || TRIM('  Utrecht  ') || ']'           AS trimmed,
       '[' || LTRIM('  Utrecht  ') || ']'          AS left_trimmed,
       REPLACE('HDMI cable 3 m', 'cable', 'lead')  AS replaced;

trimmed    left_trimmed  replaced
---------  ------------  -------------
[Utrecht]  [Utrecht  ]   HDMI lead 3 m

Joining text together

The standard operator for concatenation (joining text) is ||. Like almost every operator, it returns NULL when one side is NULL (Oracle is the exception: there 'a' || NULL gives 'a'), so Priya's location disappears completely:

SELECT first_name || ' ' || last_name AS full_name,
       city || ', ' || country        AS location
FROM customers
ORDER BY customer_id;

full_name       location
--------------  -------------
Sanne de Vries  Utrecht, NL
Kwame Mensah    Rotterdam, NL
Mei Chen        Amsterdam, NL
Omar Haddad     Antwerp, BE
Lena Fischer    Cologne, DE
Priya Patel     NULL
Tom Jansen      Utrecht, NL
Ana Silva       Lisbon, PT

Wrap the column that may be missing in COALESCE(city, 'unknown') to keep the rest. Many products also have a CONCAT() function, and here the dialects differ more than anywhere:

Numbers and arithmetic

+, -, *, / and % (remainder) work as you would expect, with one trap: dividing two integers gives an integer in SQLite, PostgreSQL and SQL Server, discarding the fraction. Make one side a decimal number to get a fractional result (MySQL's / always returns a decimal result):

SELECT 9 / 2         AS int_division,
       9 / 2.0       AS real_division,
       9 % 2         AS remainder,
       ROUND(9.0 / 2) AS rounded;

int_division  real_division  remainder  rounded
------------  -------------  ---------  -------
4             4.5            1          5.0

ROUND(value, decimals) rounds to the given number of decimal places. Why it is needed shows up as soon as you calculate with fractions. Adding 21% value-added tax (VAT) to the cable prices:

SELECT name, price,
       price * 1.21           AS with_vat,
       ROUND(price * 1.21, 2) AS with_vat_rounded
FROM products
WHERE category = 'Cables'
ORDER BY price;

name                price  with_vat           with_vat_rounded
------------------  -----  -----------------  ----------------
Ethernet cable 5 m  7      8.469999999999999  8.47
USB-C cable 2 m     9      10.89              10.89
HDMI cable 3 m      12     14.52              14.52

7 × 1.21 is exactly 8.47, but SQLite calculates with binary floating-point numbers, which cannot store 1.21 exactly. Rounding fixes the display; for money, PostgreSQL, MySQL, SQL Server and Oracle store amounts in a DECIMAL(10,2) column, which calculates exactly in decimal (SQLite has no exact decimal type; there, store whole cents as INTEGER; see Designing Tables). ABS returns the absolute value, for example the distance between the stock level and a target of 50:

SELECT name, stock, ABS(stock - 50) AS distance_from_50
FROM products
WHERE category = 'Cables'
ORDER BY product_id;

name                stock  distance_from_50
------------------  -----  ----------------
USB-C cable 2 m     120    70
HDMI cable 3 m      80     30
Ethernet cable 5 m  200    150

Dates

SQLite has no date data type. Dates are stored as text in the ISO 8601 format YYYY-MM-DD (or as numbers), and a set of functions understands that text. Because the format puts the year first and always uses two digits for month and day, text order is date order, so =, < and BETWEEN just work. date() calculates with dates and strftime() formats them or extracts parts:

SELECT order_id, order_date,
       date(order_date, '+30 days')  AS return_deadline,
       strftime('%Y-%m', order_date) AS order_month
FROM orders
WHERE order_id IN (101, 104, 107)
ORDER BY order_id;

order_id  order_date  return_deadline  order_month
--------  ----------  ---------------  -----------
101       2026-06-02  2026-07-02       2026-06
104       2026-07-01  2026-07-31       2026-07
107       2026-08-03  2026-09-02       2026-08

julianday() turns a date into a day number, so the difference between two dates is a subtraction. Two details to know: adding a month to 31 January lands in March (SQLite first makes "31 February" and then rolls over the extra days), and a date that is not in the exact format is not recognized at all:

SELECT julianday('2026-06-18') - julianday('2026-06-02') AS days_between,
       date('2026-01-31', '+1 month')                     AS one_month_later,
       date('2026-6-2')                                   AS not_padded;

days_between  one_month_later  not_padded
------------  ---------------  ----------
16.0          2026-03-03       NULL

date('now') and CURRENT_DATE return today's date (in UTC), which is useful in queries such as "orders from the last 30 days" but not in examples whose output must stay the same.

Dates in other systems

PostgreSQL, MySQL and SQL Server have real date types (DATE, plus types for date and time such as TIMESTAMP, DATETIME or DATETIME2). The database then validates every value and refuses 2026-02-30, and date arithmetic uses dedicated syntax. Adding a month to 31 January gives 28 February in all three. The same return deadline and order month, in each dialect:

-- PostgreSQL
SELECT order_date + 30                    AS return_deadline,
       to_char(order_date, 'YYYY-MM')     AS order_month,
       EXTRACT(YEAR FROM order_date)      AS order_year
FROM orders;

-- MySQL
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS return_deadline,
       DATE_FORMAT(order_date, '%Y-%m')      AS order_month,
       YEAR(order_date)                      AS order_year
FROM orders;

-- SQL Server
SELECT DATEADD(day, 30, order_date)       AS return_deadline,
       FORMAT(order_date, 'yyyy-MM')      AS order_month,
       YEAR(order_date)                   AS order_year
FROM orders;

Whatever the product, store dates in a date type (or, in SQLite, as ISO 8601 text), never in a local format such as 02-06-2026: that sorts wrongly and is ambiguous (2 June or 6 February?).