Comparison Operators

Use comparison operators (=, >, <, >=, <=, !=) to filter data.

14 min read
Beginner

Introduction to Comparison Operators

You've already learned the basic comparison operators (=, !=, >, <, >=, <=) in the WHERE clause lesson. In this lesson, we'll go deeper and learn about special comparison operators that make filtering data much easier and more powerful.

These special operators help you:

  • Check if a value falls within a range (BETWEEN)
  • Check if a value matches any in a list (IN)
  • Search for patterns in text (LIKE)
  • Handle NULL values properly (IS NULL)

Let's work with a products table:

products
id
name
category
price
stock
supplier
1LaptopElectronics999.9915TechCorp
2MouseElectronics25.50150TechCorp
3DeskFurniture299.0025OfficePro
4ChairFurniture199.0040OfficePro
5MonitorElectronics350.0030ViewMax
6KeyboardElectronics75.00100TechCorp
7LampFurniture45.0060NULL
8WebcamElectronics89.9920ViewMax

BETWEEN Operator

The BETWEEN operator checks if a value falls within a range (inclusive of both endpoints).

Checks if a value is within a range. BETWEEN x AND y is equivalent to >= x AND <= y. Both boundaries are included.

Syntax:

WHERE column BETWEEN value1 AND value2
sql
-- Find products priced between $50 and $300
SELECT name, price
FROM products
WHERE price BETWEEN 50 AND 300;

BETWEEN is equivalent to this:

sql
-- Same result using >= and <=
SELECT name, price
FROM products
WHERE price >= 50 AND price <= 300;

BETWEEN is much cleaner and easier to read!

NOT BETWEEN:

sql
-- Find products outside the $50-$300 range
SELECT name, price
FROM products
WHERE price NOT BETWEEN 50 AND 300;

BETWEEN with dates:

sql
-- Find orders from January 2024
SELECT order_id, order_date, total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Tip: BETWEEN is inclusive - it includes both boundary values. If you want to exclude boundaries, use > AND < instead.

IN Operator

The IN operator checks if a value matches any value in a list. It's a shortcut for multiple OR conditions!

Checks if a value matches any value in a list. IN (value1, value2, value3) is equivalent to = value1 OR = value2 OR = value3.

Syntax:

WHERE column IN (value1, value2, value3, ...)
sql
-- Find products from specific categories
SELECT name, category, price
FROM products
WHERE category IN ('Electronics', 'Furniture');

IN is equivalent to this:

sql
-- Same result using OR
SELECT name, category, price
FROM products
WHERE category = 'Electronics' OR category = 'Furniture';

Much cleaner with IN!

NOT IN:

sql
-- Find products NOT from TechCorp or OfficePro
SELECT name, supplier
FROM products
WHERE supplier NOT IN ('TechCorp', 'OfficePro');

IN with numbers:

sql
-- Find specific products by ID
SELECT id, name, price
FROM products
WHERE id IN (1, 3, 5, 7);

Important: NULL values are NOT matched by IN or NOT IN. If a column contains NULL and you use NOT IN, that row won't be included. Always handle NULLs separately if needed!

LIKE Operator for Pattern Matching

The LIKE operator searches for patterns in text using wildcards. Perfect for partial matches!

Performs pattern matching on strings using wildcards: % (matches any sequence of characters) and _ (matches exactly one character).

LIKE Wildcards
Wildcard
Meaning
Example
Matches
%Any sequence (0+ chars)'Tech%'Tech, TechCorp, Technology
_Exactly one character'M_use'Mouse, Mause (but not Moose)
%pattern%Contains pattern'%Pro%'Pro, OfficePro, Professional
pattern%Starts with pattern'View%'ViewMax, Viewer
%patternEnds with pattern'%Corp'TechCorp, MegaCorp

Starts with:

sql
-- Find products whose names start with 'M'
SELECT name, category
FROM products
WHERE name LIKE 'M%';

Ends with:

sql
-- Find suppliers ending with 'Corp'
SELECT DISTINCT supplier
FROM products
WHERE supplier LIKE '%Corp';

Contains:

sql
-- Find products containing 'o' in their name
SELECT name, price
FROM products
WHERE name LIKE '%o%';

Underscore for single character:

sql
-- Find products with 4-letter names ending in 'amp'
SELECT name
FROM products
WHERE name LIKE '_amp';

NOT LIKE:

sql
-- Find products NOT starting with 'M'
SELECT name
FROM products
WHERE name NOT LIKE 'M%';

Case sensitivity: In MySQL, LIKE is case-insensitive by default. In PostgreSQL and others, it's case-sensitive. Use ILIKE (PostgreSQL) or LOWER() for case-insensitive matching:

sql
-- Case-insensitive search (works everywhere)
SELECT name
FROM products
WHERE LOWER(name) LIKE LOWER('%DESK%');

Performance Tip: LIKE with a leading wildcard ('%pattern') is slow on large tables because it can't use indexes. Prefer 'pattern%' when possible!

IS NULL and IS NOT NULL

You've seen these before, but let's review why they're special comparison operators:

sql
-- Find products with no supplier
SELECT name, category, supplier
FROM products
WHERE supplier IS NULL;
sql
-- Find products that have a supplier
SELECT name, supplier
FROM products
WHERE supplier IS NOT NULL;

Never use = NULL or != NULL! These don't work. Always use IS NULL or IS NOT NULL.

Wrong: WHERE supplier = NULL Right: WHERE supplier IS NULL

Combining Comparison Operators

You can combine these operators for powerful filtering:

sql
-- Find Electronics products priced between $50 and $400
-- from specific suppliers
SELECT name, price, supplier
FROM products
WHERE category = 'Electronics'
  AND price BETWEEN 50 AND 400
  AND supplier IN ('TechCorp', 'ViewMax');
sql
-- Find products whose names contain 'e' or 'o'
-- with low stock (less than 30)
SELECT name, stock, price
FROM products
WHERE (name LIKE '%e%' OR name LIKE '%o%')
  AND stock < 30;

Practical Examples

Example 1: E-commerce price range filter

sql
-- Products between $20 and $100
SELECT name, price, category
FROM products
WHERE price BETWEEN 20 AND 100
ORDER BY price;

Example 2: Search functionality

sql
-- Search for products matching user input "key"
SELECT name, category, price
FROM products
WHERE name LIKE '%key%';

Example 3: Data quality check

sql
-- Find incomplete product records (missing supplier)
SELECT id, name, supplier
FROM products
WHERE supplier IS NULL;

Comparison Operators Summary

All Comparison Operators
Operator
Purpose
Example
=Equals`price = 100`
!= or <>Not equals`category != 'Books'`
>Greater than`stock > 50`
<Less than`price < 200`
>=Greater than or equal`price >= 100`
<=Less than or equal`stock <= 20`
BETWEENRange (inclusive)`price BETWEEN 50 AND 100`
INMatches list`category IN ('A', 'B')`
LIKEPattern matching`name LIKE '%book%'`
IS NULLChecks for NULL`supplier IS NULL`
IS NOT NULLChecks for non-NULL`email IS NOT NULL`

Common Mistakes to Avoid

Don't make these mistakes:

  1. Using = instead of LIKE for partial matches: WHERE name = '%book%' won't work!

    • Correct: WHERE name LIKE '%book%'

  2. Forgetting wildcards in LIKE: WHERE name LIKE 'Laptop' is just equality

    • Use: WHERE name LIKE '%Laptop%' for contains

  3. BETWEEN boundaries backwards: BETWEEN 100 AND 50 won't work!

    • Correct: BETWEEN 50 AND 100 (smaller value first)

  4. Using = NULL: Never works!

    • Correct: IS NULL

  5. Leading wildcard on large tables: LIKE '%end' is very slow

    • Prefer: LIKE 'start%' when possible

  6. Forgetting quotes in IN: IN (TechCorp, ViewMax) will error

    • Correct: IN ('TechCorp', 'ViewMax')

Key Takeaways

What you learned:

✅ BETWEEN checks if a value is in a range (inclusive) ✅ IN checks if a value matches any in a list (cleaner than OR) ✅ LIKE searches for text patterns using % and _ wildcards ✅ IS NULL / IS NOT NULL properly handle NULL values ✅ These operators make queries cleaner and more readable ✅ You can combine operators for powerful filtering ✅ Leading wildcards (%) are slow on large tables

Coming up next: We'll learn about logical operators (AND, OR, NOT) to combine multiple conditions and create even more sophisticated queries!

Practice Exercise: Try these queries:

  1. Find products priced between 25and25 and 100
  2. Find products from TechCorp or ViewMax
  3. Find products whose names start with 'L'
  4. Find products with no supplier (IS NULL)
  5. Combine: Electronics products under $100 from TechCorp
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.