SELECT, Filtering, TOP & OFFSET-FETCH

The SELECT statement is the workhorse of T-SQL. Beyond the ANSI core, T-SQL adds TOP n [PERCENT] [WITH TIES] and the ANSI-standard OFFSET ... FETCH for pagination.

Anatomy of a SELECT

SELECT  [DISTINCT | TOP n [PERCENT] [WITH TIES]] columns
FROM    table_source
WHERE   row_filter
GROUP BY columns
HAVING  group_filter
ORDER BY columns [OFFSET n ROWS FETCH NEXT m ROWS ONLY];

Logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → OFFSET/FETCH (or TOP).

TOP — Microsoft's Original Limiter

-- Top 5 highest-paid employees
SELECT TOP 5 employee_id, first_name, last_name, salary
FROM   employees
ORDER BY salary DESC;

-- TOP without ORDER BY is non-deterministic — DON'T do this in production
SELECT TOP 5 * FROM employees;

-- WITH TIES includes every row that ties with the last one (requires ORDER BY)
SELECT TOP 3 WITH TIES first_name, last_name, salary
FROM   employees
ORDER BY salary DESC;

TOP n PERCENT returns the top n% (rounded up):

-- Top 10% of customers by total order amount
SELECT TOP 10 PERCENT customer_id, SUM(total) AS spend
FROM   orders
GROUP BY customer_id
ORDER BY spend DESC;

OFFSET-FETCH — ANSI Pagination

OFFSET ... FETCH is the ANSI-standard pagination clause and the right choice for new code. It requires ORDER BY.

DECLARE @page_size INT = 25, @page INT = 3;

SELECT employee_id, first_name, last_name, hire_date
FROM   employees
ORDER BY hire_date DESC, employee_id
OFFSET (@page - 1) * @page_size ROWS
FETCH NEXT  @page_size           ROWS ONLY;

WHERE — Row Filtering

-- Comparison and logical operators
SELECT *
FROM   employees
WHERE  salary >= 5000
  AND  department_id IN (50, 60, 80)
  AND (job_id LIKE 'IT_%' OR commission_pct IS NOT NULL);

-- BETWEEN is inclusive on both ends
SELECT *
FROM   orders
WHERE  order_date BETWEEN '2024-01-01' AND '2024-12-31';

LIKE Wildcards

SELECT first_name
FROM   employees
WHERE  first_name LIKE 'J%n'                -- Jan, John, Joaquín
  AND  email      LIKE '%@%.com'
  AND  phone_number LIKE '[5-7]%';         -- starts 5, 6, or 7

NULL Handling

NULL is "unknown" — comparisons with = or <> always return UNKNOWN. Use IS NULL and IS NOT NULL.

-- WRONG: returns no rows because salary <> NULL is UNKNOWN, not TRUE
SELECT * FROM employees WHERE commission_pct <> NULL;

-- RIGHT
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

ISNULL vs COALESCE

-- ISNULL: T-SQL specific, takes exactly two arguments, returns the type of the first
SELECT ISNULL(commission_pct, 0) AS pct FROM employees;

-- COALESCE: ANSI, n arguments, returns the highest-precedence type
SELECT COALESCE(phone_number, mobile_number, 'no phone') AS contact
FROM   employees;

HAVING — Filtering Groups

HAVING filters after aggregation; WHERE filters before.

-- Departments with more than 5 employees and avg salary above 8000
SELECT department_id, COUNT(*) AS staff, AVG(salary) AS avg_sal
FROM   employees
WHERE  hire_date < '2020-01-01'        -- per-row filter
GROUP BY department_id
HAVING COUNT(*) > 5                    -- group filter
   AND AVG(salary) > 8000
ORDER BY avg_sal DESC;

Best Practices

• Always pair TOP and OFFSET-FETCH with ORDER BY — otherwise the rows you get back are unspecified.
• Use OFFSET-FETCH for new code; reserve TOP for "show me top N" use cases.
• Never compare to NULL with = — use IS NULL.
• Watch ISNULL length truncation; prefer COALESCE when the fallback is wider than the column.

Summary

• TOP n [WITH TIES] [PERCENT] is T-SQL's row limiter; use OFFSET ... FETCH for paginated results.
• WHERE filters rows; HAVING filters aggregated groups.
• LIKE supports %, _, character classes, and ESCAPE.
• NULL is unknown — use IS NULL/IS NOT NULL and pick ISNULL (fast, 2 args) or COALESCE (ANSI, n args) accordingly.