SQL Comparison Operators: =, !=, <, >, <=, >= Explained

Q: Why does = NULL not work in SQL?

NULL represents an unknown value. Any comparison with NULL using = or != returns NULL, not TRUE or FALSE. Use IS NULL or IS NOT NULL instead.

Apply every comparison operator correctly so your WHERE conditions return exactly the rows you intend.

Every filter you write in SQL comes down to a comparison. Is this order above a certain value? Did this product go inactive? Was this payment made before a specific date? These questions all translate directly into one of the six comparison operators. Knowing exactly how each one behaves on numbers, text, and dates prevents the kind of subtle filtering errors that are hard to spot in a large result set.

Equal To: =

= returns rows where the column value exactly matches the specified value. It is the most common operator in any WHERE clause.

-- Orders with exactly the 'Delivered' status
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.status = 'Delivered'
ORDER BY o.order_date DESC;

-- Products in a specific category
SELECT p.product_name, p.price, p.stock_qty
FROM products p
WHERE p.category_id = 3;

-- Payments made by a specific method
SELECT pay.payment_id, pay.order_id, pay.amount, pay.status
FROM payments pay
WHERE pay.method = 'PayPal'
ORDER BY pay.amount DESC;

Text values must be wrapped in single quotes. Number and boolean values do not need quotes.

Not Equal To: !=

!= returns rows where the value does not match. Use it to exclude a specific value rather than include it.

-- All orders that are not cancelled
SELECT o.order_id, o.customer_id, o.order_date, o.status
FROM orders o
WHERE o.status != 'Cancelled'
ORDER BY o.order_date DESC;

-- Products that are not in category 1
SELECT p.product_name, p.price, p.brand
FROM products p
WHERE p.category_id != 1
  AND p.is_active = 1;

-- Payments that did not use Credit Card
SELECT pay.payment_id, pay.order_id, pay.method, pay.amount
FROM payments pay
WHERE pay.method != 'Credit Card'
  AND pay.status = 'Completed';

Be careful with != on nullable columns. Rows where the column is NULL are not returned because NULL != value evaluates to NULL, not TRUE.

Greater Than and Less Than: > and

> returns rows where the column value is strictly greater than the specified value. < returns rows where it is strictly less than. Neither includes the boundary value itself.

-- Products priced above 100
SELECT p.product_name, p.price, p.brand
FROM products p
WHERE p.price > 100
  AND p.is_active = 1
ORDER BY p.price ASC;

-- Orders with a total below 50
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.total_amount < 50
ORDER BY o.total_amount ASC;

-- Employees hired before 2020
SELECT e.first_name, e.last_name, e.role, e.hire_date
FROM employees e
WHERE e.hire_date < '2020-01-01'
ORDER BY e.hire_date ASC;

Key idea: When comparing dates stored as ISO text in SQLite, > and < work correctly because ISO format sorts chronologically. '2024-06-01' > '2023-12-31' evaluates to TRUE. Always store and compare dates in YYYY-MM-DD format to rely on this behaviour.

Greater Than or Equal To and Less Than or Equal To: >= and <=

>= includes the boundary value. <= includes the boundary value on the other end. These are the right choice when you want to include rows that match the threshold exactly.

-- Products with stock at or below 10 (low stock alert)
SELECT p.product_name, p.stock_qty, p.sku
FROM products p
WHERE p.stock_qty <= 10
  AND p.is_active = 1
ORDER BY p.stock_qty ASC;

-- Orders with total amount between 100 and 500 inclusive
SELECT o.order_id, o.order_date, o.total_amount, o.status
FROM orders o
WHERE o.total_amount >= 100
  AND o.total_amount <= 500
ORDER BY o.total_amount;

-- Employees with salary at or above 60000
SELECT e.first_name, e.last_name, e.department, e.salary
FROM employees e
WHERE e.salary >= 60000
ORDER BY e.salary DESC;

Comparing Columns to Other Columns

You can compare one column to another column in the same row. This is useful for detecting data quality issues and business rule violations.

-- Orders where shipped_date is before order_date (data quality check)
SELECT o.order_id, o.order_date, o.shipped_date
FROM orders o
WHERE o.shipped_date IS NOT NULL
  AND o.shipped_date < o.order_date;

-- Products where cost_price is greater than or equal to price (no margin)
SELECT p.product_name, p.price, p.cost_price
FROM products p
WHERE p.cost_price >= p.price
  AND p.is_active = 1;

-- Payments where amount does not match order total
SELECT pay.payment_id, pay.order_id, pay.amount, o.total_amount
FROM payments pay
JOIN orders o ON pay.order_id = o.order_id
WHERE pay.amount != o.total_amount
  AND pay.status = 'Completed';

Common Mistakes

-- wrong
SELECT o.order_id, o.status
FROM orders o
WHERE o.status != NULL;

-- correct
SELECT o.order_id, o.status
FROM orders o
WHERE o.status IS NOT NULL;

!= NULL always evaluates to NULL, never TRUE. Use IS NOT NULL to filter out rows where the column has no value.

---

-- wrong
SELECT p.product_name, p.price
FROM products p
WHERE p.price => 100;

-- correct
SELECT p.product_name, p.price
FROM products p
WHERE p.price >= 100;

=> is not a valid SQL operator. The correct syntax for greater than or equal to is >= with the > first.

---

-- wrong
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_date > 2024-01-01;

-- correct
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_date > '2024-01-01';

Without quotes, 2024-01-01 is interpreted as an arithmetic expression: 2024 minus 1 minus 1 = 2022. The date string must be quoted to be treated as a date value.

Quick Recap

The six comparison operators are =, !=, >, <, >=, and <=. They work on numbers, text, and ISO-formatted dates.
> and < exclude the boundary value. >= and <= include it. Use the right one depending on whether the threshold row should be in or out of your results.
!= and all other comparison operators return no rows for NULL values. Use IS NULL and IS NOT NULL explicitly when you need to handle missing data.

Exercise 1 Easy

Write a query that returns product_name, price, and stock_qty from the products table for all active products where price is greater than or equal to 25 and less than or equal to 75. Sort by price ascending.

Hint: Use WHERE p.is_active = 1 AND p.price >= 25 AND p.price <= 75.

Solution

SELECT p.product_name, p.price, p.stock_qty FROM products p WHERE p.is_active = 1 AND p.price >= 25 AND p.price <= 75 ORDER BY p.price ASC;

Exercise 2 Medium

Write a query that returns order_id, order_date, status, and total_amount from the orders table for all orders where total_amount is greater than 300, the status is not 'Cancelled', and order_date is on or after '2024-01-01'. Sort by total_amount descending. Limit to 15 rows.

Hint: Combine three conditions with AND. Use != for the status exclusion and >= for the date boundary.

Solution

SELECT o.order_id, o.order_date, o.status, o.total_amount FROM orders o WHERE o.total_amount > 300 AND o.status != 'Cancelled' AND o.order_date >= '2024-01-01' ORDER BY o.total_amount DESC LIMIT 15;

Exercise 3 Hard

Find all products where the cost_price is greater than 50 percent of the price, meaning the margin is below 50 percent. For each such product, also show how many times it has been ordered by joining to order_items. Show product_name, price, cost_price, and times_ordered as the count of matching order item rows. Only include active products. Sort by times_ordered descending.

Hint: Join products and order_items on product_id. Filter with WHERE p.cost_price > p.price * 0.5 AND p.is_active = 1. Use GROUP BY on product columns and COUNT(oi.item_id) for the order count.

Solution

SELECT p.product_name, p.price, p.cost_price, COUNT(oi.item_id) AS times_ordered FROM products p JOIN order_items oi ON p.product_id = oi.product_id WHERE p.cost_price > p.price * 0.5 AND p.is_active = 1 GROUP BY p.product_id, p.product_name, p.price, p.cost_price ORDER BY times_ordered DESC;

Frequently Asked Questions

What are the comparison operators in SQL?

The six standard comparison operators are = (equal), != or <> (not equal), > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to).

What is the difference between != and <> in SQL?

They do the same thing. Both mean not equal to. Most databases support both, but != is more commonly used in modern SQL and is what SQLite supports.

Can you use comparison operators on text and dates in SQL?

Yes. Text comparisons use alphabetical ordering. Dates stored in ISO format like YYYY-MM-DD compare correctly using >, <, >= and <= because the format sorts chronologically.

Why does = NULL not work in SQL?

NULL represents an unknown value. Any comparison with NULL using = or != returns NULL, not TRUE or FALSE. Use IS NULL or IS NOT NULL instead.