Putting conditions together

01 / 05

One condition is not enough

"Traditional, and 180 or under" — what you want usually takes more than one condition.

And WHERE only takes one… except that it does not. Use a joining word and you can line up as many as you like.

02 / 05

condition A AND condition B gives back only the rows where both hold.

The more conditions you add, the fewer rows survive. Think of stacking sieves.

SELECT * FROM sweets  WHERE kind = 'traditional'  AND price <= 180;

Result

1 | strawberry mochi | 180 | traditional | 210
3 | syrup dumplings | 150 | traditional | 160

03 / 05

condition A OR condition B comes back if either one of them holds.

The opposite of AND: the more you add, the more rows you get.

SELECT name, price FROM sweets  WHERE price < 200  OR price > 400;

Result

strawberry mochi | 180
chocolate cake | 420
syrup dumplings | 150

04 / 05

A range like "200 or more and 300 or less" is made by joining two conditions on the same column with AND.

The trick is to write the bottom and the top as separate conditions. One condition cannot say it.

SELECT name, price FROM sweets  WHERE price >= 200  AND price <= 300;

Result

custard pudding | 260
red bean pancake | 200
cream puff | 240

05 / 05

When AND and OR end up in the same query, bracket the part you want gathered up first.

Without brackets they can be paired off in a way you did not mean. When in doubt, bracket. Let us write some.

SELECT name FROM sweets  WHERE (kind = 'traditional' OR kind = 'western')  AND price <= 200;

Result

strawberry mochi
syrup dumplings
red bean pancake