P-6.2 Querying With SQL

What this is and why it exists

SQL is declarative. You describe the result you want, not the steps to produce it. For anyone arriving from ordinary programming that is genuinely a different way of thinking, and it takes a little while to stop fighting it.

One rule causes more wrong answers than everything else combined. A missing value is not equal to anything, including itself.

Learning that properly on the first day saves an unreasonable amount of confusion later, so it gets its own section below.

The vocabulary

• Declarative — you state the result, and the database chooses how.
• Projection — choosing which columns come back.
• Filter — choosing which rows come back.
• Missing value — a column with no value at all, which is not the same as zero or an empty string.
• Pattern match — filtering text by a shape rather than an exact value.
• Set membership — filtering by a listing of accepted values.

The mental model

Most everyday queries are two decisions: which columns, and which rows. Being specific about columns rather than asking for all of them is a habit worth forming on day one. It documents what you actually need, and it survives someone adding a column later.

Ordering deserves more care than it usually gets. Without an explicit ordering, a database may return rows in any order it likes. It will change its mind between runs, after an index is added, or as the data grows. Any query whose output order matters must say so. Code that relied on an accidental order is a defect waiting for a quiet Tuesday.

Now the missing value. A comparison against it returns neither true nor false. So rows silently drop out of results that should contain them. Filtering for a status that is *not* closed will quietly exclude every row where the status was never set. Nothing warns you. The result is a smaller number that looks entirely plausible. There is a separate test for absence, and you must use it. An equality comparison never works, because nothing equals a missing value, not even another one.

The remaining conditions cover most of what is left. Matching a text pattern, testing a numeric range, or checking membership in a listing of accepted values. Which of these can use an index is a question for later. It starts here, because the shape of the condition is what decides it.

Then build fluency by repetition. Load a dataset of a few thousand rows and answer ten real questions about it without looking anything up. This part is repetition rather than understanding, and it arrives quickly.

What you should now be able to explain or do

Select specific columns and filter rows, and say why naming columns is the better habit. Say what happens to row order when you do not specify one. Explain why a comparison against a missing value returns neither true nor false, and use the correct test for absence. Filter by pattern, by range and by membership. Answer ten real questions against a real dataset without help.

Check yourself