WHERE, comparison and logical operators

A table usually holds many more rows than one question needs. WHERE is the filter that throws the rest away: you write a condition, the database checks it against every row of the table, and only the rows for which it comes out true carry on to the rest of the query. The columns you list after SELECT, and everything else in this module, work on the rows that are left.

How WHERE picks rows

Think of the condition as a question asked once per row, with that row’s values filled in. “Is total_paise at least 100000?” is true for some orders and false for the others; the true ones are kept. The condition can use any column of the table, even one you do not show in the output.

The examples on this page use eight orders of a small online grocery. The first example creates the whole table; the others create the same orders again with only the columns they need, because every example runs in a fresh, empty database.

-- Orders of a small online grocery. Money is stored as whole paise
-- (100 paise = 1 rupee), so 100000 means 1,000 rupees.
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer    TEXT    NOT NULL,
  city        TEXT    NOT NULL,
  status      TEXT    NOT NULL,
  placed_at   TEXT    NOT NULL,  -- 'YYYY-MM-DD HH:MM'
  total_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (101, 'Ishaan',   'Pune',    'paid',      '2026-02-27 09:15',  84000),
  (102, 'Revathi',  'Chennai', 'shipped',   '2026-03-01 11:40', 125000),
  (103, 'Gurpreet', 'Pune',    'shipped',   '2026-03-09 18:05',  46000),
  (104, 'Ananya',   'Kolkata', 'cancelled', '2026-03-14 07:30', 210000),
  (105, 'Tanvir',   'Pune',    'paid',      '2026-03-20 21:10',  99000),
  (106, 'Lakshmi',  'Chennai', 'refunded',  '2026-03-27 13:00',  38000),
  (107, 'Arjun',    'Indore',  'paid',      '2026-03-31 19:45', 150000),
  (108, 'Neha',     'Pune',    'delivered', '2026-04-02 10:20',  72000);

-- Orders of 1,000 rupees or more.
SELECT id, customer, total_paise
FROM orders
WHERE total_paise >= 100000;

-- Every order that was not cancelled.
SELECT id, status
FROM orders
WHERE status <> 'cancelled';

┌─────┬──────────┬─────────────┐
│ id  │ customer │ total_paise │
├─────┼──────────┼─────────────┤
│ 102 │ Revathi  │ 125000      │
│ 104 │ Ananya   │ 210000      │
│ 107 │ Arjun    │ 150000      │
└─────┴──────────┴─────────────┘
┌─────┬───────────┐
│ id  │  status   │
├─────┼───────────┤
│ 101 │ paid      │
│ 102 │ shipped   │
│ 103 │ shipped   │
│ 105 │ paid      │
│ 106 │ refunded  │
│ 107 │ paid      │
│ 108 │ delivered │
└─────┴───────────┘

The first query keeps the three orders worth 1,000 rupees or more; the second keeps everything except the cancelled order. Rows come back here in the order they were inserted, but that is a habit of this small table, not a promise: only ORDER BY, later in this module, fixes the order of a result.

Text values go in single quotes, numbers do not. Comparing text with < and > works too: SQLite compares the two strings from the left, character by character, by their character codes, which is why a later section stores dates in a particular way.

Text comparisons and letter case

Is 'Pune' equal to 'pune'? It depends on the engine, and this is one of the first places where the three engines in this track disagree:

CREATE TABLE orders (id INTEGER PRIMARY KEY, city TEXT NOT NULL);
INSERT INTO orders VALUES
  (101, 'Pune'), (102, 'Chennai'), (103, 'Pune'), (104, 'Kolkata'),
  (105, 'Pune'), (106, 'Chennai'), (107, 'Indore'), (108, 'Pune');

-- count(*) counts the rows that pass the WHERE clause.
SELECT count(*) AS same_case FROM orders WHERE city = 'Pune';
SELECT count(*) AS lower_case FROM orders WHERE city = 'pune';
SELECT count(*) AS both_lowered FROM orders WHERE lower(city) = 'pune';

┌───────────┐
│ same_case │
├───────────┤
│ 4         │
└───────────┘
┌────────────┐
│ lower_case │
├────────────┤
│ 0          │
└────────────┘
┌──────────────┐
│ both_lowered │
├──────────────┤
│ 4            │
└──────────────┘

• SQLite compares text with its default BINARY collation, byte for byte, so 'pune' matches nothing here.
• PostgreSQL behaves the same way with its default collations, which treat two strings as equal only when they are made of the same bytes.
• MySQL uses the collation utf8mb4_0900_ai_ci unless told otherwise. The ci stands for case-insensitive (and ai for accent-insensitive), so in MySQL city = 'pune' would find all four Pune orders.

To get the same answer everywhere, compare a lowered copy of the column, as the third query does with lower(city). In SQLite lower() only changes the 26 letters of the English alphabet, which is enough for these city names but not for accented letters. Collations get a lesson of their own in the text-search module.

Combining conditions with AND, OR and NOT

One comparison is rarely enough. Three logical operators join them:

• a AND b is true only when both sides are true.
• a OR b is true when at least one side is true.
• NOT a turns true into false and false into true.

When a condition mixes them, SQL does not read it from left to right. NOT is applied first, then AND, and OR last, in PostgreSQL, MySQL and SQLite alike. So a OR b AND c means a OR (b AND c), in the same way that 1 + 2 * 3 means 1 + (2 * 3).

That rule causes a classic bug. Suppose you want the paid or shipped orders from Pune and write the three conditions in the order you think of them:

CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO orders VALUES
  (101, 'paid', 'Pune'),      (102, 'shipped', 'Chennai'),
  (103, 'shipped', 'Pune'),   (104, 'cancelled', 'Kolkata'),
  (105, 'paid', 'Pune'),      (106, 'refunded', 'Chennai'),
  (107, 'paid', 'Indore'),    (108, 'delivered', 'Pune');

-- Wanted: paid or shipped orders from Pune.
-- Without parentheses, AND is applied first.
SELECT id, status, city
FROM orders
WHERE status = 'paid' OR status = 'shipped' AND city = 'Pune';

-- With parentheses the OR is decided first.
SELECT id, status, city
FROM orders
WHERE (status = 'paid' OR status = 'shipped') AND city = 'Pune';

┌─────┬─────────┬────────┐
│ id  │ status  │  city  │
├─────┼─────────┼────────┤
│ 101 │ paid    │ Pune   │
│ 103 │ shipped │ Pune   │
│ 105 │ paid    │ Pune   │
│ 107 │ paid    │ Indore │
└─────┴─────────┴────────┘
┌─────┬─────────┬──────┐
│ id  │ status  │ city │
├─────┼─────────┼──────┤
│ 101 │ paid    │ Pune │
│ 103 │ shipped │ Pune │
│ 105 │ paid    │ Pune │
└─────┴─────────┴──────┘

The first query returns order 107 from Indore. Because AND was grouped first, the query asked for “paid orders anywhere, or shipped orders from Pune”. The brackets in the second query say what was meant:

Whenever a condition contains both AND and OR, write the brackets, even where the grouping happens to be right without them. The next person who reads the query should not have to remember the precedence table.

Ranges with BETWEEN, lists with IN

Two shorthands save you from long chains of comparisons:

• x BETWEEN low AND high means x >= low AND x <= high. Both ends belong to the range, and the smaller value must come first. NOT BETWEEN keeps the values outside it.
• x IN (v1, v2, v3) means x = v1 OR x = v2 OR x = v3, and x NOT IN (…) means that x differs from every value in the list.

CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT NOT NULL, total_paise INTEGER NOT NULL);
INSERT INTO orders VALUES
  (101, 'paid', 84000),      (102, 'shipped', 125000),
  (103, 'shipped', 46000),   (104, 'cancelled', 210000),
  (105, 'paid', 99000),      (106, 'refunded', 38000),
  (107, 'paid', 150000),     (108, 'delivered', 72000);

-- Both ends are included: 46000 and 99000 match.
SELECT id, total_paise
FROM orders
WHERE total_paise BETWEEN 46000 AND 99000;

-- The smaller value must come first, or nothing matches.
SELECT count(*) AS reversed_bounds
FROM orders
WHERE total_paise BETWEEN 99000 AND 46000;

-- A list of values instead of a chain of ORs.
SELECT id, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

┌─────┬─────────────┐
│ id  │ total_paise │
├─────┼─────────────┤
│ 101 │ 84000       │
│ 103 │ 46000       │
│ 105 │ 99000       │
│ 108 │ 72000       │
└─────┴─────────────┘
┌─────────────────┐
│ reversed_bounds │
├─────────────────┤
│ 0               │
└─────────────────┘
┌─────┬───────────┐
│ id  │  status   │
├─────┼───────────┤
│ 101 │ paid      │
│ 102 │ shipped   │
│ 103 │ shipped   │
│ 105 │ paid      │
│ 107 │ paid      │
│ 108 │ delivered │
└─────┴───────────┘

Orders 103 and 105 sit exactly on the two ends of the range and are included. The second query is the edge case worth remembering: with the bounds reversed, no value can be at least 99000 and at most 46000 at the same time, so nothing matches and there is no error to warn you. PostgreSQL also offers BETWEEN SYMMETRIC, which swaps the two ends when they are the wrong way round. The quickest check of the rules is a query without any table:

SELECT 5 BETWEEN 1 AND 5     AS edge,
       5 BETWEEN 5 AND 1     AS swap,
       5 NOT BETWEEN 1 AND 4 AS out;

┌──────┬──────┬─────┐
│ edge │ swap │ out │
├──────┼──────┼─────┤
│ 1    │ 0    │ 1   │
└──────┴──────┴─────┘

SQLite shows true as 1 and false as 0; the last section explains why.

NOT IN has a trap of its own when the list contains a missing value (NULL): it then returns no rows at all. The next lessons explain NULL and why that happens.

Dates stored as text

SQLite has no separate date type. The usual practice, which this track follows, is to store a date or a time as text in the ISO 8601 order of year, month, day, hour and minute, such as '2026-03-09 18:05'. Text in that order sorts and compares in time order, because the most significant part comes first. Day-first text does not:

-- Text is compared character by character, from the left.
SELECT '2026-03-05' < '2026-12-24' AS year_first,
       '05-03-2026' < '24-12-2025' AS day_first;

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT NOT NULL);
INSERT INTO orders VALUES
  (101, '2026-02-27 09:15'), (102, '2026-03-01 11:40'),
  (103, '2026-03-09 18:05'), (104, '2026-03-14 07:30'),
  (105, '2026-03-20 21:10'), (106, '2026-03-27 13:00'),
  (107, '2026-03-31 19:45'), (108, '2026-04-02 10:20');

-- Orders placed in March, two ways.
SELECT count(*) AS with_between
FROM orders
WHERE placed_at BETWEEN '2026-03-01' AND '2026-03-31';

SELECT count(*) AS half_open
FROM orders
WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01';

┌────────────┬───────────┐
│ year_first │ day_first │
├────────────┼───────────┤
│ 1          │ 1         │
└────────────┴───────────┘
┌──────────────┐
│ with_between │
├──────────────┤
│ 5            │
└──────────────┘
┌───────────┐
│ half_open │
├───────────┤
│ 6         │
└───────────┘

The first row says that both comparisons are true. For the day-first strings that is wrong: the first date is more than two months later than the second, yet it compares as smaller, only because the character 0 sorts before 2.

The two counts show the other mistake. Six orders were placed in March, but BETWEEN '2026-03-01' AND '2026-03-31' finds five. The upper end, '2026-03-31', is shorter than '2026-03-31 19:45' and therefore sorts before it, so the evening order of the last day falls outside the range. A half-open range avoids the problem in every engine and for every type of date or timestamp: from the first moment of the period (>=) up to, but not including, the first moment of the next one (<).

PostgreSQL and MySQL have real date and timestamp types, and the dates module shows them. The half-open habit is worth keeping there too.

True and false in each engine

A condition does not have to be a comparison: any expression that is true or false will do, such as a column that records whether a product is in stock.

-- SQLite has no separate boolean type: true is 1 and false is 0.
CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT    NOT NULL,
  in_stock INTEGER NOT NULL  -- 1 = in stock, 0 = sold out
);
INSERT INTO products VALUES
  (1, 'Basmati rice 5 kg', 1),
  (2, 'Filter coffee 500 g', 0),
  (3, 'Toor dal 1 kg', 1);

SELECT name FROM products WHERE in_stock;
SELECT name FROM products WHERE NOT in_stock;
SELECT TRUE AS true_value, FALSE AS false_value, 2 > 1 AS comparison;

┌───────────────────┐
│       name        │
├───────────────────┤
│ Basmati rice 5 kg │
│ Toor dal 1 kg     │
└───────────────────┘
┌─────────────────────┐
│        name         │
├─────────────────────┤
│ Filter coffee 500 g │
└─────────────────────┘
┌────────────┬─────────────┬────────────┐
│ true_value │ false_value │ comparison │
├────────────┼─────────────┼────────────┤
│ 1          │ 0           │ 1          │
└────────────┴─────────────┴────────────┘

• SQLite has no boolean storage class. True is stored as 1 and false as 0, and the keywords TRUE and FALSE are other ways to write those two numbers. In WHERE, any number other than zero counts as true.
• MySQL works the same way: its BOOLEAN type is another name for TINYINT(1), zero is false and every other number is true.
• PostgreSQL has a real boolean type, and the condition of a WHERE must be of that type. WHERE in_stock works when in_stock is a boolean column; on an integer column PostgreSQL stops with an error, and you write in_stock = 1 or in_stock <> 0 instead.

Key takeaways

• WHERE checks its condition against every row and keeps the rows for which it is true.
• NOT is applied before AND, and AND before OR: write brackets whenever AND and OR are mixed.
• BETWEEN includes both ends and needs the smaller one first; IN and NOT IN compare with a list of values.
• Text comparisons are case-sensitive in SQLite and PostgreSQL but not under MySQL’s default collation.
• Store dates as ISO text in SQLite and select periods with half-open ranges (>= start, < next start).