SQL / SELECTING ROWS
Comparison operators and the boundaries they draw
Choose deliberately between =, <>, <, <=, >, >= knowing exactly which boundary row each keeps and how type and collation shift that boundary.
What you will learn
- Decide < vs <= by asking whether the boundary value itself belongs in the result
- Know that <> excludes NULL rows, so = and <> together never cover a whole table
- Filter timestamps with < the next midnight instead of <= the last date
- Read string comparisons as collation order: 'apex' can fall on either side of 'B'
Understanding Comparison operators and the boundaries they draw
The six comparison operators are =, <> (also spelled !=), <, <=, > and >=. Each takes two values and answers true, false or unknown, and WHERE keeps only the rows that answered true. For the four ordering operators it helps to picture the column's values laid out on a line and the literal as a cut across that line: < and > leave the cut point on the discarded side, while <= and >= pull it onto the kept side. The whole difference between price < 50 and price <= 50 is one row, the row where price is exactly 50, and that is usually the row someone checks first.
Comparison needs an order, and the order comes from the type rather than from intuition. Numbers compare by magnitude; ISO-8601 dates and timestamps compare chronologically, which is why '2026-03-31' still sorts correctly even when stored as text; strings compare by their collation, a rule set that decides whether 'apex' falls before or after 'B'. When the two sides have different types the engine coerces one of them first, and that decision moves rows in and out of your result: digits kept in a TEXT column are compared character by character, so '10' lands before '9'.
The third answer, unknown, is what makes comparisons feel leaky. Any comparison against NULL is unknown, so a NULL row satisfies neither status = 'open' nor status <> 'open'; the boundary is drawn through the known values only and NULL rows sit outside the picture entirely. Equality is also only trustworthy on values the type stores exactly, so 0.1 * 3 = 0.3 is false in binary floating point for reasons unrelated to your data. For continuous values such as money or timestamps, a bound written as >= on the low end and < on the high end covers the range once, with no gap and no double count.
placeholder
CREATE TABLE reading (
station TEXT,
celsius REAL
);
INSERT INTO reading (station, celsius) VALUES
('north', 18.0),
('east', 19.9),
('south', 20.0),
('west', 20.1);
SELECT station,
celsius,
CASE WHEN celsius > 20.0 THEN 'yes' ELSE 'no' END AS above,
CASE WHEN celsius >= 20.0 THEN 'yes' ELSE 'no' END AS at_or_above
FROM reading
ORDER BY celsius;Every comparison operator draws one cut through an ordered set of values, and the only thing separating < from <= is which side the cut point itself lands on.
Worked examples
= and <> leave a gap
Shows that the two equality operators do not split a table into two halves when NULL is present.
CREATE TABLE task (id INTEGER, owner TEXT);
INSERT INTO task (id, owner) VALUES
(1, 'ana'),
(2, 'raj'),
(3, NULL);
SELECT id, owner FROM task WHERE owner = 'ana';
SELECT id, owner FROM task WHERE owner <> 'ana';Example explained
Line 1owner = 'ana' is true for row 1 and false for row 2.
Line 2owner <> 'ana' is true for row 2, but for row 3 the comparison is unknown rather than true, so WHERE drops it.
Line 3The two result sets hold 2 of the 3 rows: an operator can only place a boundary among values it can order, and NULL is not one of them.
Line 4Row 3 is reachable only with owner IS NULL, which is a test for absence, not a comparison.
A boundary in the middle of the alphabet
Shows that a text bound lands wherever the active collation says it lands.
CREATE TABLE city (name TEXT);
INSERT INTO city (name) VALUES
('Austin'), ('Boston'), ('Zurich'), ('apex');
SELECT name FROM city WHERE name >= 'B' ORDER BY name;Example explained
Line 1'Boston' >= 'B' because the strings agree on the first character and then 'B' runs out; a prefix is always the smaller value.
Line 2'Austin' fails because 'A' precedes 'B', so a single-letter bound behaves like everything from B onward.
Line 3'apex' passes under SQLite's default byte-order collation, where every lowercase letter sorts after every uppercase letter.
Line 4Under a case-insensitive collation, such as MySQL's default, 'apex' compares as less than 'B' and disappears: same operator, same literal, different boundary.
The last day of the month
Shows why an upper bound of <= the final date silently discards that entire day.
CREATE TABLE event (id INTEGER, occurred_at TEXT);
INSERT INTO event (id, occurred_at) VALUES
(1, '2026-03-30 09:00:00'),
(2, '2026-03-31 00:00:00'),
(3, '2026-03-31 23:15:00'),
(4, '2026-04-01 00:00:00');
SELECT id, occurred_at FROM event WHERE occurred_at <= '2026-03-31';
SELECT id, occurred_at FROM event WHERE occurred_at < '2026-04-01';Example explained
Line 1Row 2 fails <= '2026-03-31': the first ten characters match, then the stored value continues with ' 00:00:00', and a longer string with a matching prefix is the greater value.
Line 2Row 1 passes because at the tenth character '0' precedes '1', so the comparison is decided before any time is reached.
Line 3< '2026-04-01' moves the cut to the next midnight, which puts all of the 31st on the keeping side while still excluding row 4.
Line 4A real TIMESTAMP column behaves identically, because a date literal with no time means midnight at the start of that day.
Important notes
<> and != are the same operator; <> is the standard spelling and is accepted everywhere, while != is a widely supported extension.
Avoid = on computed floating-point values: 0.1 * 3 evaluates to 0.30000000000000004, so compare against a small tolerance or store the value in an exact decimal type.
Common mistakes
Filtering a date-and-time column with <= '2026-03-31' and losing every row after midnight on the 31st, which quietly removes a full day from the report.
Keeping digits in a TEXT column and then writing qty > '9': the comparison runs character by character, so '10' and '11' count as smaller than '9' and never appear.
Treating <> as the exact complement of =, then finding the two row counts do not add up to the table total because NULL rows answer unknown to both.
Try it yourself
Change, predict, then run
Build a five-row orders table with a total column where one row is exactly 100, then run total > 100 and total >= 100 and confirm the results differ by that single row. Add a sixth order with a NULL total and check which of the two queries returns it.
Open the SQL workspaceCheck your understanding
A created_at column stores a date and a time. WHERE created_at <= '2026-05-31' returns plenty of May rows but almost nothing dated the 31st. What is going on?
- <= quietly behaves as < whenever the value on the right has no time part.
- Characters past the tenth are ignored, so every row on the 31st compares equal to the literal and is kept.
- The literal means the first instant of the 31st, so 09:14 on the 31st is greater than the boundary and fails the test.
- Date-and-time columns must be filtered with BETWEEN, the only construct that spans a whole day.
Show answer
A date literal carries no time, so it stands for midnight at the start of the 31st; anything stamped later that day is a larger value and falls outside <=. Option 3 is tempting but wrong: an upper edge of '2026-05-31' cuts at exactly the same instant however it is written, so the fix is to move the boundary to created_at < '2026-06-01'.