SQL Comparison Operator

In SQL, comparison operators are used to compare two values in a query. They are most commonly used in the WHERE clause to filter records that meet certain conditions.

Think of them as a way to ask the database questions like:

• “Give me all employees whose salary is greater than $50,000.”
• “Show me all orders that are exactly $100.”

List of SQL Comparison Operators

| Operator     | Meaning                     | Example                          |
|--------------|----------------------------|----------------------------------|
| `=`          | Equal to                   | `salary = 50000`                 |
| `!=` or `<>` | Not equal to               | `age <> 30`                      |
| `>`          | Greater than               | `salary > 50000`                 |
| `<`          | Less than                  | `age < 40`                       |
| `>=`         | Greater than or equal to   | `salary >= 40000`                |
| `<=`         | Less than or equal to      | `age <= 50`                      |
| `BETWEEN`    | Between a range (inclusive)| `salary BETWEEN 40000 AND 60000` |
| `LIKE`       | Pattern matching           | `name LIKE 'J%'`                 |
| `IN`         | Matches any value in a list| `department IN ('HR', 'Sales')`  |

Tip for beginners: != and <> both mean “not equal”, but <> is the standard SQL operator.

Examples With Code

Let's imagine we have an Employees table like this:

| id | name    | department | salary | age |
|----|---------|------------|--------|-----|
| 1  | Alice   | HR         | 50000  | 30  |
| 2  | Bob     | Sales      | 60000  | 45  |
| 3  | Charlie | IT         | 55000  | 35  |
| 4  | David   | HR         | 45000  | 28  |
| 5  | Eve     | Sales      | 70000  | 50  |

1. Equal To (=)

SELECT * 
FROM Employees
WHERE department = 'HR';

Explanation:

Returns employees whose department is HR.

2. Not Equal To (!= or <>)

SELECT * 
FROM Employees
WHERE age <> 30;

Explanation:

Returns all employees except the ones who are 30 years old.

3. Greater Than (>)

SELECT * 
FROM Employees
WHERE salary > 50000;

Explanation:

Returns employees whose salary is more than 50,000.

4. Less Than (<)

SELECT * 
FROM Employees
WHERE age < 40;

Explanation:

Returns employees younger than 40.

5. Greater Than or Equal To (>=)

SELECT * 
FROM Employees
WHERE salary >= 60000;

Explanation:

Returns employees whose salary is 60,000 or more.

6. Less Than or Equal To (<=)

SELECT * 
FROM Employees
WHERE age <= 35;

Explanation:

Returns employees who are 35 or younger.

7. Between

SELECT * 
FROM Employees
WHERE salary BETWEEN 45000 AND 60000;

Explanation:

Returns employees whose salary is between 45,000 and 60,000, inclusive.

8. LIKE

SELECT * 
FROM Employees
WHERE name LIKE 'A%';

Explanation:

Returns employees whose names start with 'A'.

• % = any sequence of characters
• _ = any single character

9. IN

SELECT * 
FROM Employees
WHERE department IN ('HR', 'IT');

Explanation:

Returns employees who work in HR or IT departments.