Comparison operators (<, >)

Concepts covered: Filtering

Equality answers whether a value is one specific value. Most questions about numbers ask something else: who earns more than 80000, say, or which orders came in under 10 dollars. A pod that has restarted more than 5 times is the same kind of question. Those are threshold questions. < and > handle them, along with <= and >=, and each compares the stored value to the literal and passes the row when the value sits on the named side of the line.

Rachel's compensation review starts at the lower end of the pay scale: everyone earning under 75000. The condition is salary < 75000, and the run below tests it against all 8 rows, comparing each stored salary to the literal as a number.

SELECT emp_name, salary
FROM employees
WHERE salary < 75000

4 rows pass: Osei at 72000 and Novak at 70000, plus Tanaka at 64000 and Adams at 59000. The 3 Engineering salaries and Ruiz's 80000 sit above the line and fail. The comparison is numeric, so 95000 is correctly judged larger than 75000. If salary were stored as text, the database would compare strings character by character, and '95000' would sort after '100000' because '9' comes after '1'. The column's type decides which kind of comparison runs, which is one reason amounts are stored as numbers rather than text.

The failure is worth seeing on real values, because it returns rows rather than an error. Suppose a loaded file landed salary in a text column and the filter became salary > '80000', the literal quoted to match. Text comparison stops at the first character that differs, so most values are judged by their leading digit against the 8 of '80000'.

2 of the 4 rows get the wrong answer: the 105000 earner vanishes and the 9000 earner appears. In a result of thousands of rows neither mistake stands out, which is why the fix belongs in the table's design, a numeric column, rather than in a habit of eyeballing results. Where the column cannot change yet, a CAST of salary to a number inside the condition makes the comparison numeric again.

The row on the line

Every threshold has a boundary, and whether the boundary row passes is the whole difference between > and >=. Ruiz earns exactly 80000. Ask for salary > 80000 and the result is Chen and Patel, 2 rows. Ask for salary >= 80000 and Ruiz joins them, 3 rows. Neither answer is wrong; they answer 2 different questions, and the question comes from the person who asked. A request phrased as more than 80000 wants >. One phrased as 80000 and up, or at least 80000, wants >=. When a request is ambiguous, asking which side of the line the exact value belongs on takes 10 seconds and prevents a wrong report.

SELECT emp_name, department, salary
FROM employees
WHERE salary > 80000

Change > to >= and watch Ruiz appear. Then try salary <= 80000: the other 6 rows come back. > and <= are complements, so every row with a salary passes exactly one of them. Pairing a condition with its complement is a quick check that a threshold sits where it was meant to, since the 2 row counts have to add up to the table's 8.

Dates and text have an order too

The same 4 operators work on any column with a natural order. hire_date is stored as text in the form YYYY-MM-DD, and that form was chosen because it sorts correctly as text: year first, then month, then day, each zero padded. '2022-11-01' and '2022-01-01' agree through '2022-', and the first character that differs, '1' against '0' in the month, puts November after January. hire_date > '2022-01-01' returns Kim and Tanaka along with Adams, the 3 people hired after the start of 2022. Names compare alphabetically, so emp_name < 'M' keeps the 3 names that sort earliest, from Adams through Chen to Kim. Fencing a value between 2 thresholds at once has an operator of its own, BETWEEN, which the next tier of this track covers.

About this lesson

This section is part of the Filtering: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.