How filtering and functions are evaluated

2.1 An expression is a small tree

The parser turns a condition into a tree of operations. A comparison such as LENGTH(name) > 10 has a function call on the left and a literal on the right, and the comparison is the root. A compound condition joins several comparison trees under AND and OR nodes. The engine walks the tree for each candidate row, evaluates the leaves first, and passes values toward the root.

Evaluation order inside the tree follows precedence, and precedence is fixed by the grammar rather than by the order you typed. Comparison operators rank above NOT, NOT ranks above AND, and AND ranks above OR. Parentheses override all of it because they change the tree itself, not the layout. When you debug a filter, redraw the tree with parentheses and the bug usually appears on the spot.

2.2 Three-valued logic, in full

SQL conditions have three results: TRUE, FALSE and UNKNOWN, where UNKNOWN comes from NULL. AND is TRUE only when both sides are TRUE, and FALSE as soon as one side is FALSE. OR is TRUE when either side is TRUE, and FALSE only when both sides are FALSE. NOT maps TRUE to FALSE, FALSE to TRUE, and UNKNOWN to UNKNOWN. These rules are the complete algebra, and they explain why NULL rows vanish from filters rather than raising errors.

WHERE keeps a row only on TRUE, so UNKNOWN and FALSE are removed together. Use IS NULL or IS NOT NULL to test for missing data, IS and IS NOT for the exact NULL-aware comparison, and COALESCE to replace a NULL with a usable value. PostgreSQL adds IS DISTINCT FROM and IS NOT DISTINCT FROM, which treat NULL as a value; SQLite's IS already behaves that way, so NULL IS NULL returns 1 while NULL = NULL returns NULL.

SELECT (NULL = 5) AS eq, (NULL IS NULL) AS is_null,
       (NULL AND 1) AS and_unknown, (NULL OR 1) AS or_true,
       (NOT NULL) AS not_unknown;

eq   | is_null | and_unknown | or_true | not_unknown
-----+---------+-------------+---------+------------
NULL | 1       | NULL        | 1       | NULL

2.3 How LIKE is matched

A LIKE pattern is a sequence of literal characters and wildcards. The matcher compares the text from left to right, skipping characters for % and consuming exactly one for _, with backtracking when a % needs to give characters back. The work grows with the length of the value and the number of wildcard positions, which is why a pattern with several % signs can be slower than a plain equality check even on a small table.

Case folding is the engine-specific part. SQLite folds ASCII letters for LIKE, so LIKE 'a%' matches Aarav, while GLOB stays case-sensitive with * and ? wildcards. PostgreSQL's LIKE is case-sensitive and ILIKE is the case-insensitive variant. ESCAPE turns the next wildcard into a literal, and the escape character itself must be escaped when it appears in the data.

The planner treats a pattern as an index opportunity only when the pattern has a fixed prefix. The plan output below shows a scan for both patterns, because the practice table has no index on name. A pattern with a fixed prefix can use an index when the comparison fits the index collation, while a leading wildcard cannot.

EXPLAIN QUERY PLAN SELECT name FROM students WHERE name LIKE 'A%';
EXPLAIN QUERY PLAN SELECT name FROM students WHERE name LIKE '%a%';

SCAN students
SCAN students

2.4 Functions, NULL and invalid input

Most scalar functions propagate NULL: give them NULL and they return NULL. Several built-ins deliberately break that rule because their job is to handle missing data. COALESCE(a, b, c) returns the first argument that is not NULL. IFNULL(a, b) is the two-argument SQLite version. NULLIF(a, b) returns NULL when both arguments are equal, which is useful for turning a sentinel zero into NULL. IIF(condition, a, b) is SQLite's short conditional expression, and CASE covers the general form.

Invalid input is a separate path. date('not-a-date') returns NULL, and CAST('abc' AS INTEGER) returns 0 in SQLite, because the cast is lenient by design. PostgreSQL raises an error for the same cast. That difference matters when a data cleaning script is written on SQLite and moved to a production database. Check the input with typeof or a validation query rather than trusting a cast.

SELECT COALESCE(NULL, 'fallback') AS coalesced,
       IFNULL(NULL, 'other') AS ifnull_value,
       NULLIF(5, 5) AS nullif_value,
       IIF(1 > 0, 'yes', 'no') AS iif_value,
       date('not-a-date') AS bad_date,
       CAST('abc' AS INTEGER) AS bad_cast;

coalesced | ifnull_value | nullif_value | iif_value | bad_date | bad_cast
----------+--------------+--------------+-----------+----------+---------
fallback  | other        | NULL         | yes       | NULL     | 0

2.5 Storage classes and coercion

SQLite stores every value in one of five classes: NULL, INTEGER, REAL, TEXT or BLOB. A column has a type affinity, which is a preference for one class, not a guarantee. A TEXT column happily stores 42, and an INTEGER column stores 42 only if the value can be converted without loss. This is different from PostgreSQL, where the column type is enforced and an error follows an invalid assignment.

Comparisons apply rules across classes. Two values in different classes are compared by the documented ordering, and numeric text can compare like a number in some contexts, which is a source of surprises. A column declared INTEGER but filled with '4999' may behave numerically in one place and lexically in another. Keep stored types consistent, and cast explicitly when arithmetic matters. The typeof output below shows that integer division keeps INTEGER while a cast to REAL changes the result class.

SELECT typeof(fee) AS fee_type,
       typeof(fee / 1000) AS int_div_type,
       typeof(CAST(fee AS REAL) / 1000) AS real_div_type,
       fee / 1000 AS int_result,
       CAST(fee AS REAL) / 1000 AS real_result
FROM courses
LIMIT 1;

fee_type | int_div_type | real_div_type | int_result | real_result
---------+--------------+---------------+------------+------------
integer  | integer      | real          | 4          | 4.999

2.6 Trace: filter, compute, then sort

Here is a full trace of SELECT name, UPPER(city) AS city_upper FROM students WHERE city IS NOT NULL AND LENGTH(name) > 8 ORDER BY city_upper LIMIT 3. Eight of the nine students survive: Zoya Ali is removed because her city is unknown, and every remaining name is longer than eight characters. The SELECT stage computes UPPER(city), and ORDER BY sorts by the alias that stage created. LIMIT keeps three rows.

The final three are Dev Patel in Ahmedabad, Neel Joshi in Bengaluru and Meera Iyer in Chennai, because the upper-case city values sort in that order. Naming the alias in ORDER BY is legal in every SQL dialect because ORDER BY runs after the select list; SQLite would also accept it in WHERE, and PostgreSQL would not.

SELECT name, city FROM students
WHERE city IS NOT NULL AND LENGTH(name) > 8;

name         | city
-------------+----------
Aarav Sharma | Delhi
Meera Iyer   | Chennai
Rohan Verma  | Delhi
Sana Khan    | Mumbai
Kabir Nair   | Kochi
Ishita Bose  | Kolkata
Neel Joshi   | Bengaluru
Dev Patel    | Ahmedabad

SELECT name, UPPER(city) AS city_upper FROM students
WHERE city IS NOT NULL AND LENGTH(name) > 8
ORDER BY city_upper
LIMIT 3;

name       | city_upper
-----------+-----------
Dev Patel  | AHMEDABAD
Neel Joshi | BENGALURU
Meera Iyer | CHENNAI

2.7 Where SQLite and PostgreSQL differ

Neither engine promises that a function on a column uses an index. Check the plan when a function-wrapped filter is slow, because a full scan is often exactly what the plan says.

2.8 Common mistakes

• Writing NOT (rating = 5) and expecting NULL ratings to appear. They do not.
• Forgetting that most functions return NULL for NULL input, which blanks concatenations.
• Assuming CAST rejects bad input. SQLite converts leniently instead.
• Comparing integer division results with decimal expectations.
• Treating LIKE as portable when case rules differ between engines.