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.