Comparison Operators (=, <>, <, >, <=, >=)

Now that you understand the purpose of the WHERE clause is to filter rows based on specific conditions, let's examine the most common tools for defining those conditions: comparison operators. These operators allow you to compare values in a column against a specific value or another column, forming the foundation of precise data filtering.

Think of these operators like questions you ask about your data. Does this value equal that value? Is this number greater than that number? SQL uses standard symbols for these comparisons.

Equality Operator (=)

The equals sign (=) checks if the value in a column is exactly equal to the value you specify. This is perhaps the most frequently used comparison.

Syntax: WHERE column_name = value

Example: Suppose we have a customers table and want to find all customers who live in the city of 'Boston'.

SELECT
    customer_id,
    first_name,
    last_name,
    city
FROM
    customers
WHERE
    city = 'Boston';

This query scans the city column in the customers table and returns only those rows where the city value is exactly 'Boston'. Note that text values (like 'Boston') are typically enclosed in single quotes.

Inequality Operator (<> or !=)

To find rows where a column's value does not equal a specific value, you use the inequality operator. Standard SQL uses <> (less than followed by greater than), but many database systems also accept != (exclamation mark followed by equals sign) for the same purpose. Both achieve the same result: filtering out rows that match the specified value.

Syntax: WHERE column_name <> value or WHERE column_name != value

Example: Let's find all customers who do not live in 'Boston'.

SELECT
    customer_id,
    first_name,
    last_name,
    city
FROM
    customers
WHERE
    city <> 'Boston'; -- Or use city != 'Boston'

This query returns all rows from the customers table except those where the city is 'Boston'.

Greater Than Operator (>)

The greater than operator (>) selects rows where the value in a column is numerically or alphabetically greater than the specified value.

Syntax: WHERE column_name > value

Example: Imagine a products table with a price column. To find all products costing more than $50.00:

SELECT
    product_name,
    price
FROM
    products
WHERE
    price > 50.00;

This retrieves products whose price is strictly greater than 50.00. It would not include a product priced at exactly $50.00.

Less Than Operator (<)

Conversely, the less than operator (<) finds rows where the column's value is less than the specified value.

Syntax: WHERE column_name < value

Example: To find products with fewer than 10 items in stock from a products table with a stock_quantity column:

SELECT
    product_name,
    stock_quantity
FROM
    products
WHERE
    stock_quantity < 10;

This returns products where the stock level is 9, 8, 7, and so on, but not 10.

Greater Than or Equal To Operator (>=)

Sometimes you need to include the boundary value in your comparison. The greater than or equal to operator (>=) selects rows where the column's value is greater than or exactly equal to the specified value.

Syntax: WHERE column_name >= value

Example: Find products with a price of $50.00 or more:

SELECT
    product_name,
    price
FROM
    products
WHERE
    price >= 50.00;

This query includes products priced at exactly $50.00, as well as those priced higher.

Less Than or Equal To Operator (<=)

Similarly, the less than or equal to operator (<=) selects rows where the column's value is less than or exactly equal to the specified value.

Syntax: WHERE column_name <= value

Example: Find customers who signed up on or before January 1st, 2023, using a signup_date column in the customers table:

SELECT
    customer_id,
    first_name,
    signup_date
FROM
    customers
WHERE
    signup_date <= '2023-01-01';

This query retrieves customers whose sign-up date is January 1st, 2023, or any date prior to it. Date values are also typically enclosed in single quotes, often in 'YYYY-MM-DD' format.

Comparisons with Different Data Types

These comparison operators work intuitively with different kinds of data:

• Numbers: Compared based on their numerical value (e.g., 100 > 50).
• Strings (Text): Compared alphabetically based on the character set used by the database (e.g., 'Apple' < 'Banana'). The comparison is usually character by character from left to right. Keep in mind that string comparisons might be case-sensitive or case-insensitive depending on the specific database system and its configuration ('boston' might not be equal to 'Boston').
• Dates/Times: Compared chronologically (e.g., '2023-01-15' > '2023-01-10').

These six comparison operators form the bedrock of filtering in SQL. By combining them with the WHERE clause, you can precisely target the specific rows you need for your analysis. In the next sections, we'll see how to combine multiple conditions and handle more complex filtering scenarios.