SQL Comparison Operators

In SQL, comparison operators are used to compare two expressions, often as part of WHERE clauses or conditional expressions. They help filter data, create conditions, and retrieve specific records that meet the given criteria.

Here’s a detailed look at the most common SQL comparison operators:

1. Equal To (`=`)

Used to compare if one value is equal to another.

Syntax:

SELECT * FROM table_name WHERE column_name = value;

Example:

SELECT * FROM employees WHERE department = 'Sales';

This retrieves all employees who work in the "Sales" department.

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

Check if the two values are not equal. The symbols != and <> are interchangeable, though <> is more ANSI-compliant.

Syntax:

SELECT * FROM table_name WHERE column_name != value;

Example:

SELECT * FROM employees WHERE salary <> 50000;

This retrieves all employees whose salary is not 50,000.

3. Greater Than (`>`)

Compares if the value on the left is greater than the value on the right.

Syntax:

SELECT * FROM table_name WHERE column_name > value;

Example:

SELECT * FROM products WHERE price > 100;

This retrieves products priced above $100.

4. Less Than (`<`)

Checks if the value on the left is less than the value on the right.

Syntax:

SELECT * FROM table_name WHERE column_name < value;

Example:

SELECT * FROM products WHERE stock < 50;

This retrieves products with stock levels below 50.

5. Greater Than or Equal To (`>=`)

Check if the value on the left is greater than or equal to the value on the right.

Syntax:

SELECT * FROM table_name WHERE column_name >= value;

Example:

SELECT * FROM orders WHERE quantity >= 10;

This retrieves orders with a quantity of 10 or more.

6. Less Than or Equal To (`<=`)

Checks if the value on the left is less than or equal to the value on the right.

Syntax:

SELECT * FROM table_name WHERE column_name <= value;

Example:

SELECT * FROM employees WHERE age <= 30;

This retrieves employees aged 30 or younger.

7. IN Operator

Checks if a value matches any value in a specified list.

Syntax:

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);

Example:

SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'UK');

This retrieves customers from the USA, Canada, or the UK.

8. BETWEEN Operator

Checks if a value is within a specified range (inclusive).

Syntax:

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT * FROM products WHERE price BETWEEN 50 AND 150;

This retrieves products priced between $50 and $150.

9. LIKE Operator

Used for pattern matching, often with wildcards (% and _).

Syntax:

SELECT * FROM table_name WHERE column_name LIKE pattern;

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

This retrieves employees whose names start with "J."

10. IS NULL / IS NOT NULL

Checks if a value is NULL (no value) or not.

Syntax:

SELECT * FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;

Example:

SELECT * FROM orders WHERE delivery_date IS NULL;

This retrieves orders with no delivery date specified.

Combining Comparison Operators

Comparison operators can be combined with logical operators (AND, OR, NOT) for more complex queries.

Example:

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 60000;

This retrieves employees in the "Sales" department with salaries greater than 60,000.

Conclusion

SQL comparison operators are essential tools for querying databases. By mastering them, you can create powerful queries to extract meaningful insights from your data. Whether you're filtering records, setting conditions, or performing pattern matching, these operators form the backbone of effective database management.

Souy Soeng

SQL Comparison Operators