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

In this very short lesson, you are going to learn about the comparison operators.

As the name implies, they are used to compare things, but the name of the classification is not very important, as different people could want to make different classifications.

Most of these operators are very simple, so I’m going to mention them very quickly.

So, these are the operators we have in this group:

=             which means EQUAL to.

< >          which is equivalent to ¡= and means NOT EQUAL to or different from.

<, >        which are LESS THAN and GREATER THAN

<=, >=   which are LESS THAN OR EQUAL and GREATER THAN OR EQUAL

And there are two more operators that are some kind of complement to these ones, which are ANY and ALL, which compare a value with ANY value or ALL values in a list.

I’m going to show an example of how they are used.

If I write a query like this one:

SELECT *
FROM employee
WHERE SALARY > ANY (50000, 100000, 1);

It returns all of the rows because this condition evaluates to true when the salary is greater than ANY of the values in the list.  No employee has a salary greater than 50,000 or 100,000, but all of them have a salary greater than 1, and that is why this condition evaluates to true for every row.

Now if I change the condition to:

SELECT *
FROM employee
WHERE SALARY > ALL (50000, 100000, 1);

It doesn’t return any row, because for this condition to evaluate to true, the salary must be greater than 50,000 and greater than 100,000 and greater than 1.  It must be greater than ALL of the values in the list, and no row in our table complies with that condition.

I mentioned that ANY and ALL where complements, because as you see, to use them, you have to use some other operator, such as greater than, smaller than, or similar.

Let’s continue with the next group.