Operators

So far we've written comparisons using the = operator. There are many more operators for making other comparisons. SQL includes the well-known inequalities:

How can we use this to narrow down our possible dates in the daters table? For example, we can search for people shorter than 170cm.

SELECT *
FROM daters
WHERE height_cm < 170;

Inequalities work on text as well. For text, "greater than" means it comes after it alphabetically.

SELECT *
FROM daters
WHERE name >= 'F';

Boolean Operators

There are also logical operators AND and OR, which allow you to combine multiple comparisons together. These do exactly as you would expect from plain English.

SELECT *
FROM daters
WHERE eye_color = 'green' OR eye_color = 'blue';

SELECT *
FROM daters
WHERE height_cm < 175 AND height_cm > 160;

Imagine that AND combines a bunch of requirements that all must be true. Suppose you are shopping for a blouse that has to have bright colors AND be under a certain price AND be a certain size. For a dating site, a dater with really high standards might need a lot of ANDs to find their one true love.

Operator Precedence

Be aware of the operator precedence (order of operators). Remember learning about the order of multiplication and addition in math class (maybe you learned PEMDAS)?

SELECT
  (2 + 4) * 3 AS addition_first,
  2 + (4 * 3) AS multiplication_first,
  2 + 4 * 3 AS who_knows;

Well, the same concept exists for boolean operations! You may have memorized the precedence of math operators in school. You can try to do this for boolean operators, or you can just use parentheses to be explicit. Parentheses are generally recommended because most people do not have these memorized. Notice the difference when the order of operations is changed.

SELECT *
FROM daters
WHERE (eye_color = 'blue' OR eye_color = 'green') AND height_cm >= 170;

SELECT *
FROM daters
WHERE eye_color = 'blue' OR (eye_color = 'green' AND height_cm >= 170);