The SQL Basics Almost Everybody Skips

The SQL basics most likely to cause trouble are often the ones that look too simple to revisit. NULLs, grouping, and logical query order can change an answer without producing an error.

NULL Adds a Third Logical Outcome

SQL predicates can evaluate to TRUE, FALSE, or UNKNOWN. A comparison with NULL normally produces UNKNOWN because the value is missing or unknown. WHERE keeps rows for which its condition is TRUE.

WITH Samples AS
(
    SELECT Value FROM (VALUES (1), (2), (NULL)) AS v(Value)
)
SELECT Value,
       CASE WHEN Value = 1 THEN N'True'
            WHEN Value <> 1 THEN N'False'
            ELSE N'Unknown' END AS comparison_result
FROM Samples;

This is why Value <> 1 does not automatically include missing values. If missing values belong in the result, say so with IS NULL. Do not rely on ordinary equality to test for their presence.

A missing amount is also different from a known amount of zero. Replacing one with the other requires a business rule. The convenience of a shorter expression does not settle that rule.

Use Existence Tests With Care

NOT IN becomes surprising when the comparison set contains NULL. The resulting comparisons can leave candidate rows with UNKNOWN rather than TRUE. A correlated NOT EXISTS often expresses the intended absence test more clearly.

WITH Wanted AS
(
    SELECT Id FROM (VALUES (1), (2), (3)) AS v(Id)
), Excluded AS
(
    SELECT Id FROM (VALUES (2), (NULL)) AS v(Id)
)
SELECT w.Id
FROM Wanted AS w
WHERE NOT EXISTS
      (SELECT 1 FROM Excluded AS e WHERE e.Id = w.Id);

Still decide what a NULL in the outer key should mean. NOT EXISTS is not a replacement for understanding the data model. Constraints that prevent invalid keys make both the query and its interpretation easier.

Read the Logical Order

SELECT appears first in the written statement, but its aliases are not available everywhere in that query block. The useful logical sequence begins with FROM and WHERE. Grouping and group filtering occur before the final projection.

That explains why a SELECT alias normally works in ORDER BY but not in WHERE. Use a derived table or common table expression when you need the calculated name as an input. Do not confuse that scope boundary with a guaranteed stored intermediate result.

WITH Prices AS
(
    SELECT ItemId, Price * 2 AS DoublePrice
    FROM (VALUES (1, 10), (2, 30)) AS v(ItemId, Price)
)
SELECT ItemId, DoublePrice
FROM Prices
WHERE DoublePrice > 25
ORDER BY DoublePrice, ItemId;

Logical processing defines meaning, while the optimizer chooses a physical implementation. It can rearrange work when the required result is preserved. The written order of predicates is therefore not a reliable safety barrier for risky expressions.

Filter Rows and Groups Separately

WHERE filters the rows entering the grouping operation. HAVING filters the groups produced by that operation. Moving a condition between them can change the question being answered.

WITH Sales AS
(
    SELECT * FROM (VALUES (N'East', 10), (N'East', 30), (N'West', 5))
         AS v(Region, Amount)
)
SELECT Region, SUM(Amount) AS TotalAmount
FROM Sales
WHERE Amount >= 10
GROUP BY Region
HAVING SUM(Amount) >= 25;

The total in this example only includes rows that pass the amount filter. It is not the total of every sale followed by a filter on individual sales. Describe the input set before deciding whether the output is correct.

A condition on a grouping key may sometimes be expressible in either place. Prefer the location that clearly states whether you mean rows or groups. Let the optimizer handle legal transformations.

Know What You Are Counting

COUNT(*) counts rows. COUNT(column) counts rows where that expression is not NULL. Neither expression promises the number of distinct business entities unless the input already has that grain.

WITH Payments AS
(
    SELECT Amount FROM (VALUES (10), (NULL), (10)) AS v(Amount)
)
SELECT COUNT(*) AS row_count,
       COUNT(Amount) AS known_amount_count,
       COUNT(DISTINCT Amount) AS distinct_known_amount_count
FROM Payments;

A join can multiply the rows before counting begins. Check whether you are counting customers, invoices, or invoice lines. DISTINCT can hide duplication, but it cannot decide which business meaning you intended.

For very large counts, COUNT_BIG returns bigint instead of int. Choose the type deliberately when scale requires it. The function name is less important than knowing exactly which set reaches the aggregate.

Test the Awkward Rows First

Create examples with a NULL, a duplicate, an unmatched key, and a boundary value. Those rows expose assumptions that tidy sample data hides. You can often settle a disagreement with a tiny VALUES table.

Write down the expected meaning before running the query. If the result surprises you, trace the input, filter, grouping, and projection in order. SQL fundamentals become practical when they help explain a specific wrong answer.

A SQL fundamental is not beginner trivia, it is a rule behind the result.

This post was rewritten from scratch in September 2026. The original, published on 2007-08-19, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.