9. Equality and Inequality

You've mastered simple select statements using conditional logic with and and or, and you've gotten a handle on =. Now we will start slowly migrating toward the deep end of SQL operators, starting with equality and inequality. Here, we're concerned with these relationships:

• Equal to, using the = operator
• Not equal to, using the <> operator (some databases also support != but we'll be sticking with <> in this course and suggest you do, too)
• Greater than/less than, using the > and < operators
• Greater than or equal to/less than or equal to, using the >= and <= operators

If you're having flashbacks to Algebra I, don't worry, this will be much easier.

Equality

We've already seen several examples of the = operator in action in the last section. For example, we found everyone whose first name was Mary like this:

select
    account_id,
    first_name,
    last_name
from accounts
where first_name = 'Mary'

The =operator behaves exactly as you'd expect it to if you were to read the where clause out loud, "Where first name equals Mary." There is one nuance involving the NULL value, but we'll cover that in a later chapter.

Not Equal To

The <> operator means "not equal to". You can use this operator to express the opposite of equality. For example, if we wanted to find all accounts where the first name is not Mary, we could write:

select
    account_id,
    first_name,
    last_name
from accounts
where first_name <> 'Mary'

Try it out in the console below.

Exercise 1: Not equal to

Write an SQL query to find all accounts where the last name is not "Jones" and also not "Miller".

Greater Than/Less Than

For the greater than/less than relationship, SQL uses the > and < operators, respectively. For example, let's say we want to find all transactions where the amount is greater than $100. We could write:

select
    *
from transactions
where amount > 100

We could do something similar to find all transactions where the amount is less than $25:

select
    *
from transactions
where amount < 25

We can also use these operators to compare dates! For example, what if we wanted to find all transactions that came in after January 15, 2020 (2020-01-15 in ANSI SQL)? That would be:

select *
from transactions
where transaction_date > '2020-01-15'

Exercise 2: Greater than with numbers

Write an SQL query to find all transactions where the amount is greater than $1,000.

Exercise 3: Less than with numbers

Write an SQL query to find all transactions where the amount is less than $460.

Exercise 4: Comparing dates

Write an SQL query to find all transactions where the transaction date is after April 9, 2023 and before October 5, 2023.

Inequality

Inequality refers to relationships like "greater than or equal to" and "less than or equal to." In SQL, these relationships are implemented by the >= and <= operators, respectively. By now, you've probably got a pretty good idea of how these are used – it's exactly like the operators we've already discussed. For example, if we want to find all transactions that are $100 or more, we could write:

select *
from transactions
where amount >= 100

Exercise 5: Basic Inequality

Write an SQL query to find all transactions with a transaction date of October 2, 2021 or later.

Exercise 6: Complex inequality

Write an SQL query to find all transactions of $1,000 or more that came in on or before December 31, 2022.