Comparison Operators

Comparison operators are like a scale that checks whether two things are equal, different, bigger or smaller, and answers with 1 for yes and 0 for no.

In this page:

Equal and Not Equal Operators
Greater Than Operators
Less Than Operators
The BETWEEN Operator
The IN Operator

Syntax

sql

WHERE column_name = value
WHERE column_name <> value
WHERE column_name > value   -- also <, >=, <=

Equal and Not Equal Operators

Use comparison operators to check if values are equal (=) or different (<> or !=). MySQL outputs one for true and zero for false, which is why you can even use a comparison directly as a numeric value in some expressions.

Example: Equal and Not Equal Operators

sql

CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 50), (2, 75), (3, 50);
SELECT * FROM products WHERE price = 50;
SELECT * FROM products WHERE price <> 50;

Greater Than Operators

The greater than operator (>) checks if the left value is bigger than the right. Use greater than or equal (>=) to check if it is at least equal, such as filtering orders above a minimum total.

Example: Greater Than Operators

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 40), (2, 120), (3, 200);
SELECT * FROM orders WHERE total > 100;
SELECT * FROM orders WHERE total >= 120;

Less Than Operators

The less than operator (<) checks if the left value is smaller than the right. Use less than or equal (<=) to check if it does not exceed a limit, such as a maximum allowed quantity.

Example: Less Than Operators

sql

CREATE TABLE orders (id INT, quantity INT);
INSERT INTO orders VALUES (1, 3), (2, 10), (3, 15);
SELECT * FROM orders WHERE quantity < 10;
SELECT * FROM orders WHERE quantity <= 10;

The BETWEEN Operator

The BETWEEN operator checks if a value is within an inclusive range in one concise expression. This is perfect for date limits or numeric bounds, avoiding two separate >= and <= conditions.

Example: The BETWEEN Operator

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 40), (2, 120), (3, 200);
SELECT * FROM orders WHERE total BETWEEN 50 AND 150;

The IN Operator

The IN operator checks if a value matches any option in a list. This replaces writing multiple equal conditions joined by OR, making multi-value filters much shorter to write and read.

Example: The IN Operator

sql

CREATE TABLE products (id INT, category TEXT);
INSERT INTO products VALUES (1, 'Books'), (2, 'Toys'), (3, 'Food');
SELECT * FROM products WHERE category IN ('Books', 'Toys');

Chapter Quiz — Complete all 5 topics to unlock

0/5 topics done

Complete these topics first:

← Previous Lesson Arithmetic Operators Next Lesson → Logical Operators