SQL Comparison Operators

SQL comparison operators are used to compare column values with specific values or expressions in a query. These operators return results based on whether the comparison evaluates to true, false, or unknown.

List of SQL Comparison Operators

 

Key Features of Comparison Operators

1. Equality Check (=)

The = operator checks if two values are equal.

SELECT name, salary  
FROM employees  
WHERE salary = 5000;

• Retrieves employees with a salary equal to 5000.

2. Not Equal (<> or !=)

The <> or != operator checks if two values are not equal.

SELECT name, salary  
FROM employees  
WHERE salary <> 5000;

• Retrieves employees whose salary is not equal to 5000.

3. Greater Than (>)

The > operator checks if a value is greater than another value.

SELECT name, salary  
FROM employees  
WHERE salary > 5000;

• Retrieves employees with a salary greater than 5000.

4. Less Than (<)

The < operator checks if a value is less than another value.

SELECT name, salary  
FROM employees  
WHERE salary < 5000;

• Retrieves employees with a salary less than 5000.

5. Greater Than or Equal To (>=)

The >= operator checks if a value is greater than or equal to another value.

SELECT name, salary  
FROM employees  
WHERE salary >= 5000;

• Retrieves employees with a salary of 5000 or more.

6. Less Than or Equal To (<=)

The <= operator checks if a value is less than or equal to another value.

SELECT name, salary  
FROM employees  
WHERE salary <= 5000;

• Retrieves employees with a salary of 5000 or less.

 

Examples of Comparison Operators

1. Filter Rows Based on a Condition

SELECT name, age  
FROM users  
WHERE age > 30;

• Retrieves users older than 30.

2. Combine Multiple Comparisons Using AND

SELECT name, salary  
FROM employees  
WHERE salary >= 3000 AND salary <= 6000;

• Retrieves employees with salaries between 3000 and 6000.

3. Using NOT with Comparison Operators

SELECT product_name, price  
FROM products  
WHERE NOT price = 100;

• Retrieves products where the price is not equal to 100.

4. Handle NULL Values with Comparison

• Important: Use IS NULL or IS NOT NULL to compare NULL values.

SELECT name  
FROM employees  
WHERE salary IS NULL;

• Retrieves employees where the salary is missing.

 

Summary

SQL comparison operators allow you to compare values in queries. They include =, <>, !=, >, <, >=, and <=. These operators are essential for filtering data based on conditions. Always handle NULL values properly using IS NULL instead of =.