The case for ‘IN’

Recently I was doing a little SQL. Just straight SELECT * FROM… kind of SQL. I had a where clause that was working perfectly. It was:

WHERE field1='P' or field1='U'

I ran into a situation where I needed to get a particular row. I had the primary key value so I just threw that into the WHERE clause. Now, instead of getting a single row, I got 73! What?! Examining the WHERE clause shows the obvious problem.

WHERE field2=12345 AND field1='P' or field1='U'

Duh… Using the AND and OR together really screwed it up. It was an easy fix though. I could have solved the problem with some parentheses but decided to use an IN clause instead. It lends itself to be more readable and less confusing.

WHERE field2=12345 AND field1 IN ('P','U')