Hands on: Taking a structured approach

OR is more
What if you wanted to see the rows for more than one person? It’s an oddity of the human language that we can express this requirement in two ways.

Show me all the rows for Bill AND Ted.
Show me all the rows for either Bill OR Ted.

In SQL we have only one option: we always use the word OR in this case. You’ll fnd the dbcxmas07.mbd database here.

We use AND when we want multiple conditions to be true for the same row.

As a general rule, AND queries reduce the number of rows returned (we no longer want all ‘Bill’s, we want only the ‘Bill Preston’s), while OR queries increase the number (all the ‘Bill’ plus all the ‘Ted’). ‘OR is more’ makes a handy mnemonic.

How a query works
It’s convenient to talk about queries somewhat anthropomorphically. Take our ‘find the Bill rows’ query above.

We’d say the query looks at each row, decides whether it contains Bill or not, and adds all the Bill rows to the answer table. Because most SELECT queries examine the contents of fields, this description is a good fit for the behaviour we see.

This way of thinking about the query is fine for most cases, but there are occasions when a more technical understanding can be useful.

WHERE is a reserved word (it has a specific meaning in SQL) and is always followed by an expression that can be evaluated to one of two states, true or false. When a query is run, the expression is evaluated for the truth or falsehood, for each row in the table.

The answer table contains only the rows where it evaluates to true. So the WHERE clause:
WHERE FirstName=”Bill”;
evaluates to true wherever the string “Bill” is found in the FirstName column, and these rows are included in the answer table. The remaining rows are not. By way of proof of this concept, take a look at the WHERE clause below:

SELECT FirstName, LastName
FROM People
WHERE (3<4);

The expression (3<4) always evaluates to true because three is always smaller than four and always will be. When the query is run, this expression is evaluated for every row and always, inevitably, returns true.

The answer table therefore will contain every row. The two following examples demonstrate how an understanding of query evaluation for truth can be turned to our advantage.