Where to look: a finder

A question, and the clause that answers it

You now know the main parts of a query. The last lesson is a finder: when you have a question about a table, which clause answers it?

A question, and the clause

Match the question to the clause. Which rows do I want? Where. How many, or how high? An aggregate such as count or max. One answer for each clinic? Group by. In what order? Order by, naming every column that matters. Do I need rows from two tables? A join. And is the value missing? Is null.

• Which rows do I want? · WHERE · search · navy
• How many? How high? · An aggregate: count, max · chart · navy
• One answer per group? · GROUP BY · layers · amber
• In what order? · ORDER BY, every column · calendar · slate
• Rows from two tables? · JOIN · link · slate
• Is the value missing? · IS NULL · warning · slate

Build a query in four steps

A habit that works for almost any question. First, name the table, or tables, you need. Second, say which rows you want. Third, say which columns, or which aggregate, you want back. Fourth, say how to group and sort the answer. Then run it, and read the message if it fails.

• Step 1 · Which tables? · FROM, and JOIN if two. · box
• Step 2 · Which rows? · WHERE; IS NULL for missing. · search
• Step 3 · Which columns? · The select list or an aggregate. · tag
• Step 4 · Group and sort · GROUP BY, ORDER BY. · calendar

Check yourself

Pause and think. You need one answer for each clinic. Which clause groups the rows? The answer: group by.

• GROUP BY
• WHERE
• ORDER BY
• JOIN

Keep these in mind

Match the question to the clause. Name the tables, then the rows, then the columns, then group and sort. Missing values need is null. And read the error message, because it points at the problem.

• Match the question to the clause
• Name tables, then rows, then columns, then group and sort
• Missing values need IS NULL
• Read the error message: it points at the problem