WHERE: AND, OR, IN, BETWEEN, LIKE

WHERE keeps the rows for which a condition is true. Conditions combine with AND and OR, and there are shortcuts for the common shapes:

The test case

Left-handed specialist batters and wicketkeepers (roles BAT and WK) who play for Chennai or Leeds.

Expected result

Way 1: IN for both lists

select p.player_name, t.team_code, p.role
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  p.batting_hand = 'L'
and    p.role in ('BAT', 'WK')
and    t.team_code in ('CHN', 'LDS')
order  by t.team_code, p.player_name;

The JOIN brings in the team code (Part 2 covers joins properly). Then three conditions, all of which must be true.

Way 2: OR, with parentheses

select p.player_name, t.team_code, p.role
from   cr_players p
join   cr_teams   t on t.team_id = p.team_id
where  p.batting_hand = 'L'
and    (p.role = 'BAT' or p.role = 'WK')
and    (t.team_code = 'CHN' or t.team_code = 'LDS')
order  by t.team_code, p.player_name;

The parentheses are not decoration. AND binds tighter than OR, just as × binds tighter than +. Without them:

where  p.batting_hand = 'L'
and    p.role = 'BAT' or p.role = 'WK'
and    t.team_code = 'CHN' or t.team_code = 'LDS'
-- Oracle reads it as:
--   (left-handed AND batter) OR (keeper AND Chennai) OR (Leeds)

That returns left-handed batters from every team, Chennai's keeper whichever hand he bats with, and the whole Leeds squad. When a WHERE mixes AND and OR, always add the brackets, even when you're sure. Especially then.

19c and 26ai

The same on both.

Gotchas

• AND before OR. Bracket every OR group.
• BETWEEN includes both ends, and needs the smaller value first: between 20 and 17 is never true.
• LIKE is case-sensitive by default: like 's%' doesn't find "Sipho". Use upper(name) like 'S%'.
• Comparing with null is never true: where winner_id = null returns nothing. Next page.