Comparison Operators in SQL

Comparison operators in SQL are symbols used to compare two values or expressions. They return a Boolean result of TRUE, FALSE, or UNKNOWN, which can be used to filter rows in a result set.

For example, you often need to find specific records based on conditions such as:

• Find employees whose salary is greater than ₹50,000.
• Find students who scored 80 marks.
• Find products costing less than ₹1,000.
• Find customers who are not from Dhanbad.

SQL allows you to perform these comparisons using comparison operators.

Types of Comparison Operators in SQL

SQL provides several comparison operators that allow you to compare values and define conditions. The primary comparison operators are:

Create a Sample Database in MySQL

Let’s understand SQL comparison operators with practical examples. First, we will create a sample database.

Run the following SQL commands to create and select a database named student_db:

CREATE DATABASE student_db;
USE student_db;

In this snippet:

• CREATE DATABASE student_db; creates a new database named student_db.
• USE student_db; tells MySQL to make student_db the active database for all subsequent queries.

Create the Sample Table in the Database

Now, let’s create a table named science to store student records. Run the following SQL statement to create the table with three columns: ROLL_NUMBER, S_NAME, and MARKS:

CREATE TABLE science (
    ROLL_NUMBER INT,
    S_NAME VARCHAR(20),
    MARKS INT
);

In this snippet:

• ROLL_NUMBER INT: Stores the student’s roll number as an integer (whole number).
• S_NAME VARCHAR(20): Stores the student’s name as variable-length text (up to 20 characters).
• MARKS INT: Stores the student’s marks as an integer.

Table Structure

Our table structure looks like this:

Insert Sample Data in Table

Now, let’s insert 10 student records into the science table:

INSERT INTO science (ROLL_NUMBER, S_NAME, MARKS)
VALUES
    (1, 'ABHI', 70),
    (2, 'RAVI', 80),
    (3, 'ARJUN', 90),
    (4, 'SAM', 100),
    (5, 'MOHAN', 50),
    (6, 'ROHAN', 10),
    (7, 'ROCKY', 20),
    (8, 'AYUSH', 40),
    (9, 'NEHA', 30),
    (10, 'KRITI', 60);

The INSERT INTO statement adds new rows (records) to the table. Each set of values inside parentheses matches the columns specified in (ROLL_NUMBER, S_NAME, MARKS). For example:

1. (1, ‘ABHI’, 70) sets:
   • ROLL_NUMBER = 1
   • S_NAME = ‘ABHI’
   • MARKS = 70
2. (4, ‘SAM’, 100) sets:
   • ROLL_NUMBER = 4
   • S_NAME = ‘SAM’
   • MARKS = 100

Display the Complete Table

Let’s verify the records we inserted by retrieving all data from the table:

SELECT * FROM science;

Expected Output:

We will use this table for all the comparison operator examples below.

SQL Comparison Operator Examples

Let’s understand different comparison operators in SQL with the help of different examples.

Example 1: Equal to (=) Operator

The equal to (=) operator returns rows where the value in a column is exactly equal to a specified value or expression. Suppose we want to find the student who scored exactly 50 marks. Run the following SQL query:

SELECT *
FROM science
WHERE MARKS = 50;

Expected Output:

In this example:

• The MySQL engine inspects each row in the science table and evaluates the condition MARKS = 50.
• Only the row with student MOHAN (Roll Number 5, Marks 50) evaluates to TRUE.
• All other rows evaluate to FALSE and are filtered out of the final result set.

Example 2: Greater Than (>) Operator

The greater than (>) operator returns rows where the value in a column is strictly greater than the specified value. For example, suppose we want to find students who scored more than 60 marks.

Run the following SQL query:

SELECT *
FROM science
WHERE MARKS > 60;

Expected Output:

In this query:

• The MySQL engine inspects each row in the science table and evaluates whether MARKS > 60.
• Only records where the marks are strictly above 60 (70, 80, 90, and 100) evaluate to TRUE.
• Notice that KRITI, who scored exactly 60, is not included, because 60 > 60 evaluates to FALSE.

Example 3: Less Than (<) Operator

The less than (<) operator returns rows where the value in a column is strictly less than the specified value. Suppose we want to find students who scored fewer than 40 marks. Executes the following SQL query in a MySQL environment:

SELECT *
FROM science
WHERE MARKS < 40;

Expected Output:

In this SQL query:

• The MySQL engine scans each record in the science table and evaluates the condition MARKS < 40.
• Only records where the marks are strictly below 40 (10, 20, and 30) evaluate to TRUE.
• Notice that AYUSH (who scored exactly 40) is not included, because 40 < 40 evaluates to FALSE.

Example 4: Greater Than or Equal To (>=) Operator

The greater than or equal to (>=) operator returns rows where the value in a column is either greater than or equal to the specified value. For example, suppose we want to find students who scored 80 marks or more.

Executes the following SQL query:

SELECT *
FROM science
WHERE MARKS >= 80;

Expected Output:

In this example:

• The MySQL engine checks each record in the science table to test if MARKS >= 60.
• Records with marks greater than 60 (70, 80, 90, 100) evaluate to TRUE.
• Unlike the > operator, KRITI, who scored exactly 60, is included here because the condition 60 >= 60 evaluates to TRUE.

Example 5: Less Than or Equal To (<=) Operator

The less than or equal to (<=) operator returns rows where the value in a column is either smaller than or equal to the specified value. Suppose we want to find students who scored 30 marks or fewer. Executes the following SQL query in a MySQL environment:

SELECT *
FROM science
WHERE MARKS <= 30;

Expected Output:

In this example query:

• The MySQL engine inspects each row in the science table to evaluate the condition MARKS <= 30.
• Records with marks strictly below 30 (10 for ROHAN and 20 for ROCKY) evaluate to TRUE.
• NEHA, who scored exactly 30, is also included because the condition 30 <= 30 evaluates to TRUE.
• All students with marks greater than 30 are excluded from the result set.

Example 6: Not Equal To (<> and !=) Operators

The not equal to operators return rows where the value in a column is different from the specified value. SQL supports two symbols for this:

• <>: The standard ANSI SQL operator (recommended for universal database compatibility).
• !=: A widely supported alternative in MySQL, PostgreSQL, Oracle, and SQL Server.

Let’s take an example in which we want to find all students except the one who scored 50 marks.

Query using <>:

SELECT *
FROM science
WHERE MARKS <> 50;

You can also write this as WHERE MARKS != 50; — both yield the exact same result in MySQL.

Expected Output:

In this example:

• The MySQL engine inspects each row in the science table to evaluate whether MARKS is not equal to 50.
• For every student whose score is anything other than 50, the condition evaluates to TRUE.
• Only MOHAN (Roll Number 5, who scored exactly 50) evaluates to FALSE (50 <> 50 is FALSE) and is excluded from the output.

Using SQL Comparison Operators with Text (Strings)

Comparison operators in SQL are not limited to numbers; they can also be used to compare text (character data).

When you compare texts, keep the following points in mind:

• Always enclose text values inside single quotes ('...').
• In MySQL, string comparisons are typically case-insensitive by default (depending on your database collation), meaning ‘sam’ and ‘SAM’ match the same rows.
• Operators like <, >, <=, and >= compare text alphabetically (lexicographically) according to dictionary order.

Example 7: Finding an Exact Name (=)

Suppose we want to find the record for the student whose name is SAM:

SELECT *
FROM science
WHERE S_NAME = 'SAM';

Expected Output:

In this example, the MySQL engine inspects the S_NAME column for each row. Only row 4 in the table matches the string ‘SAM’ and evaluates to TRUE.

Example 8: Excluding a Specific Name (<> or !=)

Suppose we want to find all students except ROHAN:

SELECT *
FROM science
WHERE S_NAME <> 'ROHAN';

Expected Output:

In this example, every row where S_NAME is not equal to ‘ROHAN’ evaluates to TRUE. Only the row with ROHAN evaluates to FALSE and is removed from the result set.

Example 9: Alphabetical Comparisons (<, >)

When you use < or > on text, SQL checks alphabetical order:

• ‘A’ comes before ‘B’, so ‘A’ < ‘B’ evaluates to TRUE.

Suppose we want to find all students whose names come alphabetically before the letter ‘M’:

SELECT *
FROM science
WHERE S_NAME < 'M';

Expected Output:

In this example:

• Names starting with letters before ‘M’ (A, B, C, … K, L) evaluate to TRUE.
• Names like MOHAN, NEHA, RAVI, ROCKY, ROHAN, and SAM evaluate to FALSE.

Comparison Operators and NULL Values

One of the most common pitfalls for beginners in SQL is trying to compare column values with NULL using standard comparison operators like =, <>, or !=.

In SQL, NULL does not mean zero (0), an empty string (”), or a blank space. It represents missing, unknown, or unavailable data.

Since NULL is unknown, SQL cannot determine whether it is equal to, greater than, or less than any other value—even another NULL.

The NULL Comparison Trap

Consider what happens if you run this query trying to find records where marks are missing:

-- This will return 0 rows, even if rows have NULL marks!
SELECT *
FROM science
WHERE MARKS = NULL;

Why does this return empty?

• Any standard comparison against NULL evaluates to UNKNOWN (SQL’s Three-Valued Logic: TRUE, FALSE, UNKNOWN).
• A WHERE clause only outputs rows where the condition evaluates to TRUE.
• Since UNKNOWN is not TRUE, the query discards all rows.
• Even WHERE MARKS <> NULL will return zero rows for the exact same reason.

The Correct Way: IS NULL and IS NOT NULL

To check for missing or unknown values, SQL provides two dedicated operators:

• IS NULL: Evaluates to TRUE if the value is missing/NULL.
• IS NOT NULL: Evaluates to TRUE if the value has valid, non-NULL data.

Practical Demonstration

Let’s insert a record with a missing score into the science table:

INSERT INTO science (ROLL_NUMBER, S_NAME, MARKS)
VALUES (11, 'KAPIL', NULL);

Example 10: Finding rows with missing marks

SELECT *
FROM science
WHERE MARKS IS NULL;

Expected Output:

Example 11: Finding rows with valid (non-missing) marks

SELECT *
FROM science
WHERE MARKS IS NOT NULL;

In this example:

• The MySQL inspects each row in the science table.
• Rows 1 through 10 have known numeric values, so MARKS IS NOT NULL evaluates to TRUE.
• Row 11 (KAPIL) has a NULL value, so it evaluates to FALSE and is excluded from the result.

Key Takeaway Table

Key Takeaways

The easiest way to remember SQL comparison operators is:

• = → Equal to
• <> or != → Not equal to
• > → Greater than
• < → Less than
• >= → Greater than or equal to
• <= → Less than or equal to

These operators are mainly used in WHERE clauses to create conditions that filter and retrieve only the records you need. Remember: never use = or != with NULL—always use IS NULL or IS NOT NULL to test for missing values.