SQLFiltering Databeginner45 min read

Comparison Operators in SQL

Master SQL comparison operators, inclusive and exclusive boundaries, range queries with BETWEEN, set membership with IN and NOT IN, and the effects of NULL on comparisons.

On This Page (Table of Contents)

In this lesson, you will learn

✓Use comparison operators =, <>, !=, <, <=, >, and >=
✓Explain the difference between inclusive and exclusive boundaries
✓Filter numeric values and dates using BETWEEN
✓Match values using IN and NOT IN
✓Understand how NULL affects comparison expressions
✓Explain why NOT IN can behave unexpectedly when its list contains NULL
✓Use parentheses to make combined conditions clear
✓Understand why index usage depends on the query plan

Overview of SQL Comparison Operators

Comparison operators compare values, such as a column and a constant, and produce TRUE, FALSE, or UNKNOWN. UNKNOWN commonly occurs when a comparison involves NULL. These operators are essential for filtering rows with WHERE and can also be used in other SQL expressions.

Compare Product Prices

sql

1
2
3
4
5
SELECT product_id, title, price
FROM products
WHERE price >= 50
  AND price < 100
ORDER BY price;

Output

Products priced at least 50 but strictly below 100, ordered by price.

The lower boundary is inclusive because of >=. The upper boundary is exclusive because of <.

SQL Comparison Operators

Operator	Meaning	Example
=	Equal to	department = 'Engineering'
<>	Not equal to	status <> 'archived'
!=	Not equal to in PostgreSQL	status != 'archived'
>	Greater than	price > 100
>=	Greater than or equal to	stock_quantity >= 10
<	Less than	rating < 4
<=	Less than or equal to	age <= 65
BETWEEN	Inclusive range	salary BETWEEN 60000 AND 90000
IN	Membership in a set	country IN ('US', 'CA', 'GB')

Equality and Inequality

Use = to test equality. Use <> or != to test inequality in PostgreSQL. The <> spelling is the standard SQL form and is generally the safer choice when portability matters. SQL equality is not written as ==.

Filter Orders by Status

sql

1
2
3
4
5
6
7
8
9
-- Find pending orders
SELECT order_id, status
FROM orders
WHERE status = 'pending';
 
-- Exclude cancelled orders
SELECT order_id, status
FROM orders
WHERE status <> 'cancelled';

Output

The first query returns pending orders. The second returns rows with a non-NULL status other than cancelled.

Rows with a NULL status do not pass the inequality condition because status <> 'cancelled' evaluates to UNKNOWN for those rows.

Greater Than and Less Than

The operators >, >=, <, and <= define numeric boundaries. The difference between strict and inclusive operators matters when a value exactly equals the boundary.

Filter Stock Quantities

sql

1
2
3
4
5
6
7
8
9
-- More than 10 items
SELECT product_id, stock_quantity
FROM products
WHERE stock_quantity > 10;
 
-- At least 10 items
SELECT product_id, stock_quantity
FROM products
WHERE stock_quantity >= 10;

Output

The first query excludes stock_quantity = 10. The second includes it.

Use > or < when the boundary itself should be excluded. Use >= or <= when it should be included.

Understanding Inclusive and Exclusive Boundaries

A boundary is inclusive when a value equal to it is allowed. It is exclusive when equality is not allowed. Always translate the requirement into an explicit condition before choosing the operator.

Filter an Exact Range

sql

1
2
3
4
SELECT product_id, price
FROM products
WHERE price >= 100
  AND price <= 200;

Output

Products priced from 100 through 200, including both endpoints.

This range is equivalent to price BETWEEN 100 AND 200 for ordinary non-NULL numeric values.

Boundary Examples

Requirement	Predicate	Is 100 included?
More than 100	price > 100	No
At least 100	price >= 100	Yes
Less than 100	price < 100	No
At most 100	price <= 100	Yes
From 100 through 200	price BETWEEN 100 AND 200	Yes

Using BETWEEN for Numeric Ranges

BETWEEN tests whether a value lies within an inclusive range. Both endpoints are included. For non-NULL values, value BETWEEN lower_bound AND upper_bound is equivalent to value >= lower_bound AND value <= upper_bound.

Find Orders within a Price Range

sql

1
2
3
4
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount BETWEEN 50.00 AND 150.00
ORDER BY total_amount;

Output

Orders with total_amount greater than or equal to 50.00 and less than or equal to 150.00.

An order worth exactly 50.00 or 150.00 qualifies. An order with a NULL total_amount does not pass the filter.

Using BETWEEN with Dates and Timestamps

BETWEEN is inclusive, which is important when the column stores timestamps rather than dates. If a timestamp is compared with a date-like upper boundary interpreted as midnight, later times on that final date may be excluded. For calendar periods, a half-open interval is often safer: include the start and exclude the start of the next period.

Find Orders in October 2026

sql

1
2
3
4
5
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= TIMESTAMP '2026-10-01 00:00:00'
  AND order_date <  TIMESTAMP '2026-11-01 00:00:00'
ORDER BY order_date;

Output

Orders with timestamps from the start of October 1 up to, but not including, the start of November 1.

This includes the entire October period without relying on the maximum fractional-second precision of the timestamp type. For TIMESTAMPTZ, use boundaries appropriate to the intended time zone.

Using IN to Match a List of Values

IN checks whether a value matches at least one item in a list. It is a concise alternative to several equality comparisons joined with OR. It can also be used with a subquery that returns a single column.

Filter Orders by Several Statuses

sql

1
2
3
4
SELECT order_id, customer_id, status
FROM orders
WHERE status IN ('shipped', 'delivered', 'in_transit')
ORDER BY order_id;

Output

Orders whose status is shipped, delivered, or in_transit.

The condition is logically similar to status = 'shipped' OR status = 'delivered' OR status = 'in_transit'. PostgreSQL decides which execution strategy is appropriate.

Using IN with a Subquery

IN can compare a value with the result of a subquery. The subquery must return one column for this form. This is useful when the allowed values come from another table rather than a manually written list.

Find Orders from Preferred Customers

sql

1
2
3
4
5
6
7
SELECT order_id, customer_id
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE is_preferred = TRUE
);

Output

Orders belonging to customers marked as preferred.

The inner query produces customer IDs. The outer query returns orders whose customer_id matches one of those IDs. The actual column names must exist in your schema.

Using NOT IN

NOT IN checks that a value does not match any value in the specified set. It is useful for excluding a known list of values, but its behavior with NULL requires care. If the set contains NULL and no ordinary value matches, the result can be UNKNOWN rather than TRUE.

Exclude Selected Order Statuses

sql

1
2
3
SELECT order_id, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

Output

Orders whose non-NULL status is neither cancelled nor refunded.

Rows whose status is NULL are not returned because the NOT IN condition evaluates to UNKNOWN for them.

The NOT IN and NULL Trap

Suppose a NOT IN list or subquery contains NULL. For a value that matches none of the ordinary list items, the expression can evaluate to UNKNOWN. WHERE excludes that row. This is especially dangerous with NOT IN (subquery) when the subquery can return NULL. If the intention is to find rows with no matching record, NOT EXISTS is often clearer and safer.

Avoid a NULL-Sensitive Exclusion Subquery

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Potentially surprising if blocked_customer_ids contains NULL
SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id NOT IN (
  SELECT b.customer_id
  FROM blocked_customer_ids AS b
);
 
-- NULL-safe anti-match for non-null customer IDs
SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM blocked_customer_ids AS b
    WHERE b.customer_id = c.customer_id
  );

Output

The NOT EXISTS version returns non-NULL customer IDs for which no equal ID exists in blocked_customer_ids.

NOT EXISTS tests for the absence of a matching row. The explicit outer IS NOT NULL condition is included because the example intentionally excludes NULL customer IDs; remove it if your requirements differ.

Check NULL Values in Exclusion Lists

A NULL in a NOT IN list can prevent non-matching values from passing the condition. Inspect the source data or use NOT EXISTS when checking for the absence of matching rows.

Comparison Operators and NULL

Ordinary comparisons with NULL evaluate to UNKNOWN. This applies to equality, inequality, and ordering comparisons. To test whether a value is missing, use IS NULL or IS NOT NULL. Remember that WHERE retains only TRUE results.

Find Orders without a Recorded Amount

sql

1
2
3
SELECT order_id
FROM orders
WHERE total_amount IS NULL;

Output

Orders whose total_amount value is NULL.

IS NULL is the correct test for missing values. Using total_amount = NULL does not find them.

Comparison Results with NULL

Expression	Result	Passes WHERE?
price = NULL	UNKNOWN	No
price <> NULL	UNKNOWN	No
price > NULL	UNKNOWN	No
price IS NULL	TRUE if price is NULL	Yes when NULL
price IS NOT NULL	TRUE if price is not NULL	Yes when not NULL

Combining Comparison Operators with AND and OR

Comparison operators are often combined with AND and OR to express more complex rules. Because AND has higher precedence than OR, use parentheses to make the intended grouping explicit.

Filter Active Products in Two Price Ranges

sql

1
2
3
4
5
6
7
8
SELECT product_id, title, price
FROM products
WHERE is_active = TRUE
  AND (
    price < 20
    OR price BETWEEN 100 AND 150
  )
ORDER BY price;

Output

Active products priced below 20 or between 100 and 150 inclusive.

The parentheses group the alternative price conditions. The active requirement applies to both alternatives.

How Comparison Predicates Affect Index Usage

Simple equality and range predicates can often be supported by a B-tree index on the compared column. IN may also be supported by index-based plans, but it does not guarantee a particular index lookup strategy. PostgreSQL chooses a plan using factors such as selectivity, table statistics, available indexes, and estimated costs.

Inspect a Range Query

sql

1
2
3
4
5
EXPLAIN
SELECT order_id, total_amount
FROM orders
WHERE total_amount >= 100
  AND total_amount < 200;

Output

A PostgreSQL query plan, which may include a sequential scan or an index scan depending on the schema and data.

EXPLAIN shows the planned strategy. An index can help when the query and data distribution make index access worthwhile, but a sequential scan can be more efficient when many rows qualify.

MCQ 1: Equality

Test your knowledge of SQL equality.

MCQ 2: Inequality

Review inequality operators.

MCQ 3: Inclusive Boundaries

Check how BETWEEN handles endpoints.

MCQ 4: IN

Review membership tests.

MCQ 5: NULL

Review comparison behavior with NULL.

MCQ 6: NOT IN

Check the NULL behavior of NOT IN.

MCQ 7: Timestamp Boundaries

Check date-range filtering.

MCQ 8: Indexes

Review query optimization.

Guided Practice: Filter Products

Assume products has product_id, title, category, price, and is_active. Write a query to return product_id, title, and price for active products that are either in the Books category and priced from 10 through 50, or in the Electronics category and priced below 100. Use parentheses so the category and price conditions are grouped correctly.

Practice Requirements

sql

1
2
3
4
5
-- Return product_id, title, price.
-- Require is_active = TRUE.
-- Books: price from 10 through 50 inclusive.
-- Electronics: price below 100.
-- Group the alternatives explicitly.

Output

Active books priced from 10 to 50, together with active electronics priced below 100.

Use two parenthesized alternatives joined by OR, and require is_active = TRUE for both.

Guided Practice Solution: Filter Products

Compare your answer with this solution. The parentheses ensure the active requirement applies to both category alternatives.

Product Filter Solution

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT product_id, title, price
FROM products
WHERE is_active = TRUE
  AND (
    (
      category = 'Books'
      AND price BETWEEN 10 AND 50
    )
    OR
    (
      category = 'Electronics'
      AND price < 100
    )
  )
ORDER BY category, price;

Output

Active books in the inclusive price range and active electronics priced below 100, sorted by category and price.

Each alternative contains its own category and price requirements. The outer AND applies the active filter to the whole grouped expression.

Guided Practice: Exclude Matching Records

Assume customers has customer_id and full_name, and blocked_customer_ids has customer_id, which may contain NULL. Write a query to return non-NULL customer IDs that have no matching entry in blocked_customer_ids.

Practice Requirements

sql

1
2
3
4
-- Return customer_id from customers.
-- Exclude NULL customer IDs.
-- Exclude IDs with a matching blocked_customer_ids row.
-- Do not let NULLs in the subquery break the exclusion logic.

Output

Non-NULL customer IDs for which no matching blocked ID exists.

NOT EXISTS checks whether a matching row exists and avoids the NULL-sensitive behavior of NOT IN.

Guided Practice Solution: Exclude Matching Records

This solution explicitly excludes NULL customer IDs and checks for the absence of a matching blocked ID.

Safe Exclusion Query

sql

1
2
3
4
5
6
7
8
SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM blocked_customer_ids AS b
    WHERE b.customer_id = c.customer_id
  );

Output

Non-NULL customer IDs with no equal customer_id in blocked_customer_ids.

The subquery is correlated to the current customer. NOT EXISTS is TRUE when no matching blocked row exists.

Interview Questions and Answers

Use these questions to review comparison operators, boundaries, NULL behavior, and query-planning basics.

Lesson Summary

Comparison operators let you filter values using equality, inequality, and relational boundaries. BETWEEN includes both endpoints, while IN checks membership in a list or subquery. Be careful with NOT IN when NULL may appear in the set, and use IS NULL to test missing values. For timestamp periods, a half-open interval is usually safer than an inclusive date boundary. Parentheses clarify complex conditions, and EXPLAIN helps you inspect how PostgreSQL plans to execute a query.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using == instead of =

Why it happens: Programmers carry equality syntax from languages such as C++, JavaScript, and Python into SQL.

Incorrect Approach

sql

SELECT order_id FROM orders WHERE status == 'pending';

Correct Solution

sql

SELECT order_id FROM orders WHERE status = 'pending';

Key takeaway: Use a single equals sign for SQL equality comparisons.

#2 Assuming BETWEEN excludes its endpoints

Why it happens: The range operator may be confused with strict comparison operators.

Incorrect Approach

sql

SELECT product_id FROM products WHERE price BETWEEN 10 AND 20; -- expected to exclude 20

Correct Solution

sql

SELECT product_id
FROM products
WHERE price > 10 AND price < 20;

Key takeaway: BETWEEN includes both endpoints. Use strict comparisons when the boundaries must be excluded.

#3 Using NOT IN when a subquery can return NULL

Why it happens: The query is expected to behave like a simple list of values, even though SQL uses three-valued logic.

Incorrect Approach

sql

SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM blocked_customer_ids);

Correct Solution

sql

SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM blocked_customer_ids AS b
    WHERE b.customer_id = c.customer_id
  );

Key takeaway: NOT EXISTS avoids the NULL-sensitive membership result and explicitly excludes NULL outer IDs in this example.

#4 Using = NULL to find missing values

Why it happens: NULL is mistakenly treated as an ordinary value.

Incorrect Approach

sql

SELECT order_id FROM orders WHERE total_amount = NULL;

Correct Solution

sql

SELECT order_id FROM orders WHERE total_amount IS NULL;

Key takeaway: Use IS NULL or IS NOT NULL to test missing values.

#5 Using an inclusive timestamp upper bound for a whole month

Why it happens: A date literal may be interpreted as midnight when compared with a timestamp.

Incorrect Approach

sql

WHERE order_date BETWEEN TIMESTAMP '2026-10-01 00:00:00' AND TIMESTAMP '2026-10-31 00:00:00'

Correct Solution

sql

WHERE order_date >= TIMESTAMP '2026-10-01 00:00:00'
  AND order_date < TIMESTAMP '2026-11-01 00:00:00'

Key takeaway: The half-open interval includes all timestamps within October. For timestamp-with-time-zone columns, use boundaries in the intended time zone.

#6 Assuming IN always performs index lookups

Why it happens: A concise membership expression is mistaken for a guarantee about the physical execution strategy.

Incorrect Approach

sql

Assuming WHERE customer_id IN (1, 2, 3) must use an index.

Correct Solution

sql

EXPLAIN
SELECT customer_id
FROM orders
WHERE customer_id IN (1, 2, 3);

Key takeaway: The optimizer chooses an execution plan based on available indexes, estimated costs, selectivity, and other factors.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Comparison Operators in SQL Knowledge Check

Test your understanding with immediate feedback

Question 1 of 8

Which operator is the standard SQL equality operator?

Question 2 of 8

Which operator is the standard SQL spelling for not equal?

Question 3 of 8

Does price BETWEEN 10 AND 20 include price = 20?

Question 4 of 8

What does status IN ('pending', 'shipped') test?

Question 5 of 8

What does price = NULL evaluate to?

Question 6 of 8

What can happen when a NOT IN list contains NULL and the value matches no ordinary list item?

Question 7 of 8

Which is generally safer for filtering all timestamps in October?

Question 8 of 8

Does using IN guarantee that PostgreSQL will use an index?

Technical and placement interview questions

Frequently tested interview questions with detailed, full answers (click to expand)

Q1:What are SQL comparison operators?

Answer:

They compare values and produce TRUE, FALSE, or UNKNOWN. Examples include =, <>, >, >=, <, and <=.

Q2:What is the difference between = and <>?

Answer:

= tests equality. <> tests inequality.

Q3:What is the difference between <> and !=?

Answer:

Both mean not equal in PostgreSQL, but <> is the standard SQL spelling and is generally more portable.

Q4:What does BETWEEN mean?

Answer:

BETWEEN checks whether a value is within an inclusive range, including both endpoints.

Q5:When should you avoid BETWEEN for timestamp periods?

Answer:

When the upper date boundary represents midnight and the intention is to include the entire final calendar day. A half-open interval is usually safer.

Q6:What does IN do?

Answer:

IN tests whether an expression matches any value in a list or a single-column subquery result.

Q7:What is the difference between IN and OR?

Answer:

For equality comparisons against the same alternatives, IN is a more concise expression of the same membership requirement.

Q8:Why can NOT IN be dangerous with NULL?

Answer:

If its list or subquery contains NULL, a non-matching value can produce UNKNOWN instead of TRUE. WHERE excludes that row.

Q9:Why is NOT EXISTS often useful for exclusion?

Answer:

It checks for the absence of a matching row without allowing unrelated NULL values in the subquery to poison the result as NOT IN can.

Q10:How should you test for NULL?

Answer:

Use IS NULL or IS NOT NULL, not ordinary equality or inequality comparisons.

Q11:What is SQL three-valued logic?

Answer:

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN, commonly when a comparison involves NULL.

Q12:Does IN guarantee an index lookup?

Answer:

No. PostgreSQL chooses an execution plan based on the query, available indexes, estimated costs, and data distribution.

Q13:Why use parentheses with AND and OR?

Answer:

AND has higher precedence than OR. Parentheses make the intended grouping clear and prevent accidental matches.

Q14:What is a half-open interval?

Answer:

It includes the lower boundary and excludes the upper boundary, typically expressed as value >= start AND value < next_period_start.

Q15:How can you inspect a query's execution plan?

Answer:

Use EXPLAIN to inspect the planned execution strategy. EXPLAIN ANALYZE also executes the query and reports actual execution details.

Key takeaways

Both <> and != mean not equal in PostgreSQL; <> is the standard SQL spelling.
BETWEEN includes both endpoints and is equivalent to a lower-bound comparison joined to an upper-bound comparison.
IN checks whether a value matches an item in a list or the result of a subquery.
IN does not guarantee a particular index strategy; PostgreSQL chooses a plan based on the query and available information.
Comparisons involving NULL generally evaluate to UNKNOWN.
WHERE retains only rows whose complete condition evaluates to TRUE.
NOT IN can produce UNKNOWN for non-matching values when its list contains NULL.
Use NOT EXISTS when expressing an anti-match against a subquery that might return NULL values.
For timestamp periods, a half-open range using >= the start and < the next period's start is often safer than BETWEEN.
Use parentheses when combining conditions with AND and OR.

Was this lesson helpful?

Found an error? Report an issue