Lesson 2: WHERE, ORDER BY, TOP, DISTINCT, and NULL Handling (DBA Accuracy Skills)

Section 3 — Lesson 2: WHERE, ORDER BY, TOP, DISTINCT, and NULL Handling (DBA Accuracy Skills)

Welcome back. In Lesson 1, you learned SELECT fundamentals: how to choose columns (projection),
how to filter rows (WHERE), and how to sort results (ORDER BY).

In this lesson, we go deeper into the exact skills DBAs use constantly:
getting the right rows, the right order, the right amount of output,
and handling one of the biggest sources of mistakes in SQL: NULL.

---

Lesson description: You will learn professional filtering patterns with WHERE (including operators and date ranges),
safe ordering practices with ORDER BY, how to limit output using TOP (with and without ORDER BY),
how DISTINCT works (and why it can be expensive), and correct NULL handling patterns (IS NULL, IS NOT NULL, COALESCE).
You will also learn “DBA-safe query” habits that prevent production mistakes.

---

1) WHERE: Filtering With Precision (Not Guessing)

The WHERE clause decides which rows are returned. In production work, one wrong WHERE clause can cause
incorrect reporting, misleading troubleshooting, or—if used in UPDATE/DELETE later—real damage.

1A) Core comparison operators

• = equal
• <> not equal
• >, >=, <, <= comparisons

-- Bookings with totals above $100
SELECT BookingID, ConfirmationCode, TotalAmountUSD
FROM dbo.Booking
WHERE TotalAmountUSD > 100.00;

1B) AND / OR with parentheses (critical correctness rule)

SQL evaluates AND before OR. If you don’t use parentheses, you can return the wrong rows.

-- Correct: confirmed bookings for a specific customer
SELECT BookingID, ConfirmationCode, Status, PickupAt
FROM dbo.Booking
WHERE CustomerID = 1
  AND Status = 'CONFIRMED';

-- Correct: bookings that are NEW or CONFIRMED for a specific customer
SELECT BookingID, ConfirmationCode, Status, PickupAt
FROM dbo.Booking
WHERE CustomerID = 1
  AND (Status = 'NEW' OR Status = 'CONFIRMED');

DBA habit: anytime you use OR, consider parentheses by default.

1C) IN and NOT IN

IN is cleaner than many ORs. It is common for status filters.

SELECT BookingID, ConfirmationCode, Status
FROM dbo.Booking
WHERE Status IN ('NEW','CONFIRMED');

DBA warning: NOT IN behaves badly when NULL is involved (it can produce surprising “no rows” behavior).
You will learn safer patterns in the NULL section below.

1D) BETWEEN (use carefully)

BETWEEN is inclusive on both ends. That can be dangerous with datetime values.

-- BETWEEN is inclusive; this is usually OK for numeric ranges
SELECT ServiceID, ServiceName, BasePriceUSD
FROM dbo.Service
WHERE BasePriceUSD BETWEEN 50.00 AND 100.00;

DBA rule for dates: Use a start time inclusive and end time exclusive pattern (shown next).

1E) Date/time filtering: start inclusive, end exclusive (best practice)

This pattern avoids missing rows and avoids double-counting when time is involved.

-- Bookings scheduled today (server time)
DECLARE @StartOfDay DATETIME2(0) = DATEFROMPARTS(YEAR(SYSDATETIME()), MONTH(SYSDATETIME()), DAY(SYSDATETIME()));
DECLARE @StartOfNextDay DATETIME2(0) = DATEADD(DAY, 1, @StartOfDay);

SELECT BookingID, ConfirmationCode, PickupAt, Status
FROM dbo.Booking
WHERE PickupAt >= @StartOfDay
  AND PickupAt <  @StartOfNextDay
ORDER BY PickupAt;

Why DBAs use this: It is precise. It works regardless of seconds/milliseconds. It avoids time boundary bugs.

---

2) ORDER BY: Correct Ordering (And Why It Matters)

Without ORDER BY, SQL Server can return rows in any order. If you care about the order, you must specify it.

2A) ORDER BY is applied last (conceptually)

SQL logically filters first, then sorts the final results.

2B) Order by multiple columns

SELECT BookingID, ConfirmationCode, Status, PickupAt
FROM dbo.Booking
WHERE Status IN ('NEW','CONFIRMED')
ORDER BY Status ASC, PickupAt ASC;

2C) ORDER BY and performance

Sorting can be expensive on large result sets because SQL Server may need a sort operator.
Indexes can help when the ORDER BY matches the index key order.

DBA habit: If you’re troubleshooting a production incident, avoid ordering huge result sets unless necessary.
Filter first.

---

3) TOP: Limiting Output (DBA “Safe Query” Tool)

TOP is used to limit results. DBAs use TOP constantly to safely inspect data without pulling millions of rows.

3A) TOP without ORDER BY (not deterministic)

If you use TOP without ORDER BY, you get “any 10 rows.” That might be fine for a quick peek, but it is not reliable.

-- Quick peek (not guaranteed which rows you get)
SELECT TOP (10) CustomerID, FullName, Email
FROM dbo.Customer;

3B) TOP with ORDER BY (DBA recommended)

This is the professional pattern: decide exactly which rows you want.

-- Most recently created customers
SELECT TOP (10) CustomerID, FullName, Email, CreatedAt
FROM dbo.Customer
ORDER BY CreatedAt DESC;

3C) TOP for incident response (real DBA usage)

When troubleshooting, you often want “latest 50” or “top 100” records to confirm a pattern quickly.

-- Latest bookings (quick operational check)
SELECT TOP (50) BookingID, ConfirmationCode, Status, PickupAt, TotalAmountUSD, CreatedAt
FROM dbo.Booking
ORDER BY CreatedAt DESC;

DBA safety rule: Start with TOP + WHERE. Then expand only if necessary.

---

4) DISTINCT: Removing Duplicates (And Why DBAs Use It Carefully)

DISTINCT removes duplicate rows from the output. It can be useful, but it can also hide data quality problems
and it can be expensive because SQL Server may need to sort or hash to remove duplicates.

4A) Basic DISTINCT example

-- Distinct booking statuses currently present in the table
SELECT DISTINCT Status
FROM dbo.Booking
ORDER BY Status;

4B) DISTINCT can hide problems

If your query returns duplicates because your join logic is wrong (later lesson), DISTINCT may “make it look correct”
while hiding the real bug. DBAs treat DISTINCT like a tool—not a bandage.

DBA habit: If you need DISTINCT, ask: “Why do duplicates exist?” Sometimes it’s valid; sometimes it’s a query mistake.

---

5) NULL Handling: The #1 Source of Beginner Mistakes

NULL means “unknown” or “not applicable.” It is not a value. This changes how comparisons behave.

5A) The rule: never use = NULL

This returns no rows:

-- WRONG (returns no rows)
SELECT CustomerID, FullName
FROM dbo.Customer
WHERE Phone = NULL;

Correct approach:

-- Correct
SELECT CustomerID, FullName
FROM dbo.Customer
WHERE Phone IS NULL;

-- Correct
SELECT CustomerID, FullName, Phone
FROM dbo.Customer
WHERE Phone IS NOT NULL;

5B) NULL and NOT IN (danger)

If the list in NOT IN contains NULL, the logic becomes “unknown” and may return zero rows unexpectedly.
This is why DBAs prefer NOT EXISTS for anti-joins (you will learn later).

For now, remember:
avoid NOT IN when NULL might be present.

5C) COALESCE: replace NULL for display or logic

COALESCE returns the first non-NULL value in the list. DBAs often use it for reporting output.

-- Display a placeholder when Phone is NULL
SELECT
    CustomerID,
    FullName,
    Email,
    COALESCE(Phone, 'N/A') AS PhoneDisplay
FROM dbo.Customer
ORDER BY CustomerID;

DBA note: COALESCE is great for display. For indexing and filtering performance, avoid wrapping indexed columns
inside functions in the WHERE clause unless you understand the impact (non-SARGable predicates).

---

6) DBA-Safe Query Habits (How Professionals Avoid Mistakes)

These habits are expected in real DBA work:

1. Start small: use TOP (10/50/100) when exploring.
2. Filter early: add WHERE before ORDER BY on large tables.
3. Be explicit: list columns; don’t depend on SELECT *.
4. Use date range best practice: start inclusive / end exclusive.
5. Handle NULL correctly: IS NULL / IS NOT NULL; use COALESCE for display.
6. Think about performance: ORDER BY and DISTINCT can add extra work.

---

7) Practice (Job-Ready Exercises)

Using your lab database, run and verify these queries:

1. Return the 10 most recent bookings (use TOP + ORDER BY CreatedAt DESC).
2. Return all bookings scheduled today using the start-of-day / start-of-next-day pattern.
3. Return all customers where Phone is NULL, and show Phone as N/A using COALESCE.
4. Return the list of distinct booking statuses present in the table.
5. Return bookings where Status is NEW or CONFIRMED and TotalAmountUSD is between 50 and 150, ordered by PickupAt.

DBA check: After each query, verify:
(1) results make sense, (2) you didn’t accidentally return too much data, (3) output is readable.

---

Lesson Summary

• WHERE controls correctness—use parentheses with OR and be precise with date ranges.
• ORDER BY is required for guaranteed ordering; it can be expensive on big results.
• TOP is a DBA safety tool; use it with ORDER BY for deterministic results.
• DISTINCT removes duplicates but can hide query mistakes and add cost.
• NULL is not a value: use IS NULL / IS NOT NULL, and COALESCE for display.

---

Next Lesson: GROUP BY, HAVING, and Aggregate Functions (Turning Rows into Answers)

Next, you will learn how to summarize data like a DBA:
totals, counts, averages, grouping by day, grouping by status, and filtering groups correctly using HAVING.
This is essential for reporting, monitoring, and operational decision-making.