Comparison Operators in SQL0%

Comparison Operators in SQL

Beginner12 min read•Updated: 2026-09-12

Comparison Operators in SQL: Exact and Relational Checks

To filter rows effectively in a WHERE clause, you evaluate expressions using Comparison Operators. These operators compare two expressions and evaluate to TRUE, FALSE, or UNKNOWN.

1. Standard SQL Comparison Operators

Operator	Meaning	Example	Result for Match
`=`	Equal to	`status = 'Shipped'`	TRUE if values match.
`!=` or `<>`	Not equal to	`department != 'HR'`	TRUE if values differ.
`<`	Less than	`price < 500.00`	TRUE if left is strictly smaller.
`<=`	Less than or equal to	`age <= 60`	TRUE if left is smaller or equal.
`>`	Greater than	`salary > 50000.00`	TRUE if left is strictly larger.
`>=`	Greater than or equal to	`rating >= 4.5`	TRUE if left is larger or equal.

1-- Find all products priced at ₹1,000 or higher:
2SELECT product_name, price 
3FROM products 
4WHERE price >= 1000.00;
5 
6-- Find all orders NOT marked as 'Delivered' (Using ANSI standard <>):
7SELECT order_id, customer_id, order_status 
8FROM orders 
9WHERE order_status <> 'Delivered';

2. `!=` vs `<>`: Which is Preferred?

In MySQL, both != and <> perform the exact same "not equal to" comparison.

<> is the official ANSI SQL standard operator, compatible across all database vendors (Oracle, PostgreSQL, SQL Server, SQLite).
!= is a popular vendor convenience operator.
Best Practice: Use <> for maximum cross-database portability.

3. The "Not Equal" NULL Trap!

One of the most common bugs in SQL query writing involves combining not-equal operators with NULL values:

1-- Suppose our employees table has 5 employees:
2-- 3 in 'Engineering', 1 in 'HR', and 1 with department = NULL.
3 
4SELECT * FROM employees WHERE department <> 'HR';

Question: Will the employee with department = NULL be returned? Answer: NO! Because NULL <> 'HR' evaluates to UNKNOWN, the row is silently discarded! If you want to include employees without a department, you must write:

1SELECT * FROM employees 
2WHERE department <> 'HR' OR department IS NULL;

4. The MySQL Spaceship Operator (`<=>`)

To solve the NULL comparison problem, MySQL provides a unique operator: the NULL-Safe Equal Operator (<=>), affectionately called the Spaceship Operator:

1-- Standard equality:
2SELECT 1 = 1;     -- Returns 1 (TRUE)
3SELECT NULL = NULL; -- Returns NULL (UNKNOWN)
4 
5-- NULL-Safe equality (<=>):
6SELECT 1 <=> 1;       -- Returns 1 (TRUE)
7SELECT NULL <=> NULL; -- Returns 1 (TRUE!)
8SELECT 1 <=> NULL;    -- Returns 0 (FALSE)

You can use the spaceship operator to safely compare columns that might contain NULL without writing cumbersome OR col IS NULL clauses:

1-- Matches rows where discount_code matches the user's input, even if both are NULL:
2SELECT * FROM orders WHERE discount_code <=> NULL;

5. Best Practices & Common Pitfalls

Do Not Use col = NULL: In SQL, writing WHERE status = NULL will never return any rows, because nothing can equal an unknown! Always use IS NULL or the spaceship operator <=>.
String Collation in Comparisons: Remember that comparisons like name = 'amit' will match 'Amit' and 'AMIT' if your collation is case-insensitive (_ci).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which operator is the official ANSI SQL standard for "not equal to"?

Question 2

What does the expression SELECT 5 <=> NULL; evaluate to in MySQL?

Question 3

Why will the query SELECT * FROM users WHERE middle_name = NULL; return 0 rows even if users have NULL middle names?

Question 4

What does the spaceship operator (<=>) return when comparing NULL <=> NULL?

Question 5

If a table contains 10 rows (7 with status = 'Active', 2 with status = 'Banned', and 1 with status = NULL), how many rows will WHERE status <> 'Banned' return?

Next Lesson

Logical Operators: AND, OR, NOT

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Filtering Rows with WHERE Clause	Logical Operators: AND, OR, NOT

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Logical Operators: AND, OR, NOT

Comparison Operators in SQL

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Comparison Operators in SQL: Exact and Relational Checks

To filter rows effectively in a WHERE clause, you evaluate expressions using Comparison Operators. These operators compare two expressions and evaluate to TRUE, FALSE, or UNKNOWN.

1. Standard SQL Comparison Operators

Operator	Meaning	Example	Result for Match
`=`	Equal to	`status = 'Shipped'`	TRUE if values match.
`!=` or `<>`	Not equal to	`department != 'HR'`	TRUE if values differ.
`<`	Less than	`price < 500.00`	TRUE if left is strictly smaller.
`<=`	Less than or equal to	`age <= 60`	TRUE if left is smaller or equal.
`>`	Greater than	`salary > 50000.00`	TRUE if left is strictly larger.
`>=`	Greater than or equal to	`rating >= 4.5`	TRUE if left is larger or equal.

1-- Find all products priced at ₹1,000 or higher:
2SELECT product_name, price 
3FROM products 
4WHERE price >= 1000.00;
5 
6-- Find all orders NOT marked as 'Delivered' (Using ANSI standard <>):
7SELECT order_id, customer_id, order_status 
8FROM orders 
9WHERE order_status <> 'Delivered';

2. `!=` vs `<>`: Which is Preferred?

In MySQL, both != and <> perform the exact same "not equal to" comparison.

<> is the official ANSI SQL standard operator, compatible across all database vendors (Oracle, PostgreSQL, SQL Server, SQLite).
!= is a popular vendor convenience operator.
Best Practice: Use <> for maximum cross-database portability.

3. The "Not Equal" NULL Trap!

One of the most common bugs in SQL query writing involves combining not-equal operators with NULL values:

1-- Suppose our employees table has 5 employees:
2-- 3 in 'Engineering', 1 in 'HR', and 1 with department = NULL.
3 
4SELECT * FROM employees WHERE department <> 'HR';

1SELECT * FROM employees 
2WHERE department <> 'HR' OR department IS NULL;

4. The MySQL Spaceship Operator (`<=>`)

To solve the NULL comparison problem, MySQL provides a unique operator: the NULL-Safe Equal Operator (<=>), affectionately called the Spaceship Operator:

1-- Standard equality:
2SELECT 1 = 1;     -- Returns 1 (TRUE)
3SELECT NULL = NULL; -- Returns NULL (UNKNOWN)
4 
5-- NULL-Safe equality (<=>):
6SELECT 1 <=> 1;       -- Returns 1 (TRUE)
7SELECT NULL <=> NULL; -- Returns 1 (TRUE!)
8SELECT 1 <=> NULL;    -- Returns 0 (FALSE)

You can use the spaceship operator to safely compare columns that might contain NULL without writing cumbersome OR col IS NULL clauses:

1-- Matches rows where discount_code matches the user's input, even if both are NULL:
2SELECT * FROM orders WHERE discount_code <=> NULL;

5. Best Practices & Common Pitfalls

Do Not Use col = NULL: In SQL, writing WHERE status = NULL will never return any rows, because nothing can equal an unknown! Always use IS NULL or the spaceship operator <=>.
String Collation in Comparisons: Remember that comparisons like name = 'amit' will match 'Amit' and 'AMIT' if your collation is case-insensitive (_ci).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which operator is the official ANSI SQL standard for "not equal to"?

Question 2

What does the expression SELECT 5 <=> NULL; evaluate to in MySQL?

Question 3

Why will the query SELECT * FROM users WHERE middle_name = NULL; return 0 rows even if users have NULL middle names?

Question 4

What does the spaceship operator (<=>) return when comparing NULL <=> NULL?

Question 5

If a table contains 10 rows (7 with status = 'Active', 2 with status = 'Banned', and 1 with status = NULL), how many rows will WHERE status <> 'Banned' return?

Next Lesson

Logical Operators: AND, OR, NOT

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Filtering Rows with WHERE Clause	Logical Operators: AND, OR, NOT

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Comparison Operators in SQL: Exact and Relational Checks

1. Standard SQL Comparison Operators

2. != vs <>: Which is Preferred?

3. The "Not Equal" NULL Trap!

4. The MySQL Spaceship Operator (<=>)

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Logical Operators: AND, OR, NOT

Related Lessons

Practice Quiz

Loading Page Content...

Comparison Operators in SQL: Exact and Relational Checks

1. Standard SQL Comparison Operators

2. != vs <>: Which is Preferred?

3. The "Not Equal" NULL Trap!

4. The MySQL Spaceship Operator (<=>)

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Logical Operators: AND, OR, NOT

Related Lessons

Practice Quiz

2. `!=` vs `<>`: Which is Preferred?

4. The MySQL Spaceship Operator (`<=>`)

2. `!=` vs `<>`: Which is Preferred?

4. The MySQL Spaceship Operator (`<=>`)