Comparison Operators & Three-Valued Logic

basic

An HR team needs several different employee reports: high earners, recently hired staff, terminated employees, and employees without a manager. This example shows the full comparison operator set and the critical NULL distinction.

-- Setup: employees table

-- id | name | department | salary | hire_date | manager_id | status

-- ---+---------------+---------------+--------+-------------+------------+-----------

-- 1 | Alice Chen | Engineering | 95000 | 2019-03-15 | NULL | active

-- 2 | Bob Martinez | Sales | 72000 | 2021-07-01 | 1 | active

-- 3 | Carol Davis | Engineering | 110000 | 2017-11-20 | 1 | active

-- 4 | David Kim | Marketing | 68000 | 2023-01-10 | 2 | active

-- 5 | Eve Johnson | Sales | 85000 | 2020-05-18 | 1 | terminated

-- 6 | Frank Lee | Engineering | 92000 | 2022-09-05 | 3 | active

-- 1. Basic equality

SELECT name, department, salary

FROM employees

WHERE department = 'Engineering';

-- Returns: Alice, Carol, Frank

-- 2. Greater-than — high earners

SELECT name, salary

FROM employees

WHERE salary > 90000;

-- Returns: Alice (95000), Carol (110000), Frank (92000)

-- 3. Compound comparison

SELECT name, hire_date, salary

FROM employees

WHERE hire_date >= '2022-01-01'

AND salary < 90000;

-- Returns: David Kim (hired 2023, salary 68000)

-- 4. Not-equals with AND

SELECT name, department

FROM employees

WHERE department <> 'Marketing'

AND status = 'active';

-- Returns: Alice, Bob, Carol, Frank

-- 5. NULL check — the CRITICAL difference

-- WRONG: returns 0 rows

SELECT name FROM employees WHERE manager_id = NULL;

-- CORRECT: returns Alice Chen

SELECT name FROM employees WHERE manager_id IS NULL;

-- 6. Combine IS NULL with OR