SQL Server / Basic / Lesson 3

SELECT, predicates, NULL, and deterministic order

Turn a business question into an explicit result shape. Learn SQL Server TOP, three-valued logic, date ranges, aliases, and a complete ordering contract.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Write a projection and predicate with predictable rows.
Handle NULL without confusing it with a value.
Use TOP together with a stable ORDER BY.

Define the result before writing syntax

Begin with the grain of the answer: one row per open order, with identifier, amount, and promised date. SELECT defines the displayed expressions; FROM identifies the source; WHERE filters individual rows. Give calculated expressions meaningful aliases and request only necessary columns. SELECT * makes application contracts vulnerable to unrelated schema changes and can move large text columns unnecessarily.

Logical query processing helps explain alias rules. WHERE is logically evaluated before SELECT, so a new SELECT alias is generally unavailable to WHERE in that same query block. ORDER BY can use a SELECT alias. This logical model explains meaning; it does not prescribe the physical order chosen by the optimizer. Separate semantic reasoning from a graphical plan.

Choose rows with TOP and ORDER BY

TOP (2) limits the returned rows, but without ORDER BY it does not mean the two newest or largest orders. A clustered index or a familiar output order is not an ordering contract. The example requests the largest amounts and breaks equal amounts by OrderId. That unique tiebreaker makes the requested two rows predictable for unchanged input.

TOP WITH TIES has a different purpose: it can return more than the nominal count when rows tie on every ordering expression at the boundary. Include a unique identifier only when ties should be broken, not when the business asks to retain all equal scores. Final presentation still needs an outer ORDER BY; ordering inside a view is not inherited automatically.

sql Two largest orders with a tiebreaker

SELECT TOP (2) OrderId, Amount
FROM (VALUES (10,40.00),(11,90.00),(12,90.00))
 AS o(OrderId,Amount)
ORDER BY Amount DESC, OrderId ASC;

Expected output

OrderId Amount
11      90.00
12      90.00

Descending amount selects both 90.00 rows; ascending OrderId defines their displayed order. TOP without ORDER BY would not express this question.

Reason about unknown comparisons

Comparisons involving NULL normally evaluate to UNKNOWN, and WHERE retains only TRUE. Therefore ShippedOn = NULL does not locate unshipped orders; use IS NULL. The expression Amount <> 20 also excludes unknown amounts, because unknown is neither true nor false. If the requirement includes missing values, express that inclusion separately with OR and parentheses.

COALESCE can supply a display value, but replacing unknown data in a filter changes the question. A missing credit limit may mean a review is required, not an unlimited balance or a zero balance. NOT IN is especially dangerous when its list or subquery contains NULL: an apparently absent value can still produce UNKNOWN. Prefer a clearly correlated NOT EXISTS for absence tests.

Use explicit ranges and parentheses

The half-open interval includes its beginning and excludes its end. For a datetime2 column, testing >= January 1 and < February 1 includes every January timestamp without guessing the last fractional second. BETWEEN includes both endpoints, which often makes a midnight end date incorrect for a whole-month report. Keep comparison values typed consistently with the column.

AND binds more tightly than OR. When selecting open orders for either of two customers, group the customer alternatives explicitly. The example keeps only unshipped January rows; row 2 is excluded by the end boundary and row 3 by its non-null shipping date. Predicting each row independently is a powerful way to detect logic mistakes before considering performance.

sql An unshipped January order

DECLARE @o TABLE (OrderId int, CreatedAt datetime2(0), ShippedOn date NULL);
INSERT @o VALUES
 (1,'2026-01-31T23:59:59',NULL),
 (2,'2026-02-01T00:00:00',NULL),
 (3,'2026-01-10T09:00:00','20260112');
SELECT OrderId FROM @o
WHERE CreatedAt >= '20260101' AND CreatedAt < '20260201'
  AND ShippedOn IS NULL
ORDER BY OrderId;

Expected output

OrderId
1

Both the date predicate and NULL predicate must be true. February midnight is outside the half-open interval.

Keep values separate from SQL structure

Application inputs should reach predicates through typed parameters. They are data, even if they contain apostrophes or text resembling SQL. String concatenation confuses a value with command structure and creates injection risk. A local DECLARE illustrates a type but is not, by itself, proof that an application used driver parameters. Review the actual command construction at the application boundary.

Validate ranges and lengths as business rules, then bind values with suitable SQL Server types. Identifiers and sort-direction keywords cannot be replaced by ordinary value parameters; choose those from a fixed allowlist. This separation also helps performance because stable command text can be reused, although plan reuse and data skew require careful analysis in later lessons.

Common errors and fixes

No rows returned by = NULL.

Why it happens: NULL comparison produces UNKNOWN.
How to fix it: Use IS NULL or IS NOT NULL.
Prevent it next time: Include missing-value cases in expected results.

TOP returns changing rows.

Why it happens: There is no complete ordering contract.
How to fix it: Add ORDER BY expressing the business ranking and a unique tiebreaker when needed.
Prevent it next time: Review ties and ordering as part of query correctness.

Invalid column name.

Why it happens: WHERE refers to an alias created later in SELECT.
How to fix it: Repeat the expression or introduce a derived table.
Prevent it next time: Use logical processing order to review alias scope.

Check your understanding

1. Amounts are 10, NULL, and 30. Which survive Amount <> 10?

Show worked solution

Only 30 survives. NULL yields UNKNOWN and is filtered out. To include it, write Amount <> 10 OR Amount IS NULL, using parentheses when combining with another condition.

2. Return the newest three events when timestamps can tie.