SELECT, WHERE, ORDER BY, TOP/OFFSET-FETCH

Overview

SELECT, WHERE, ORDER BY, TOP, and OFFSET-FETCH are foundational SQL clauses used to retrieve, filter, sort, and limit data from relational databases. In SQL Server and Azure SQL, these clauses appear in almost every read query, from simple lookup screens to complex reporting, API pagination, dashboard queries, and data validation scripts.

At a high level:

• SELECT defines which columns or expressions are returned.
• WHERE filters rows before they are returned.
• ORDER BY sorts the final result set.
• TOP limits the number or percentage of rows returned.
• OFFSET-FETCH skips a number of sorted rows and fetches the next page of rows.

Example:

SELECT TOP (10)
    c.CustomerId,
    c.FullName,
    c.Email,
    c.CreatedAt
FROM dbo.Customers AS c
WHERE c.IsActive = 1
ORDER BY c.CreatedAt DESC, c.CustomerId DESC;

Example with pagination:

DECLARE @PageNumber int = 2;
DECLARE @PageSize int = 20;

SELECT
    o.OrderId,
    o.CustomerId,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Orders AS o
WHERE o.Status = 'Completed'
ORDER BY o.OrderDate DESC, o.OrderId DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

These clauses matter because even simple SQL queries can become incorrect, slow, or nondeterministic if written carelessly. For example, using TOP without ORDER BY can return unpredictable rows. Filtering with non-sargable expressions can prevent index seeks. Pagination with OFFSET-FETCH can become slow for deep pages. SELECT * can return unnecessary data and make APIs fragile when table schemas change.

This topic is important for interviews because it tests whether a developer understands SQL basics at a practical level. Interviewers often ask candidates to write queries, explain query behavior, reason about sorting and filtering, and discuss performance implications. A strong answer should cover not only syntax but also correctness, determinism, index usage, pagination trade-offs, and common mistakes.

Core Concepts

Basic SELECT Syntax

The SELECT statement retrieves rows from one or more tables, views, common table expressions, derived tables, or table-valued functions.

Basic syntax:

SELECT column_list
FROM table_name;

Example:

SELECT
    CustomerId,
    FullName,
    Email
FROM dbo.Customers;

The SELECT list can contain:

• Column names.
• Aliases.
• Expressions.
• Function calls.
• Constants.
• Calculated values.
• Scalar subqueries.

Example:

SELECT
    o.OrderId,
    o.TotalAmount,
    o.TotalAmount * 0.10 AS EstimatedTax,
    'Completed Order' AS OrderType
FROM dbo.Orders AS o
WHERE o.Status = 'Completed';

Best practices:

• Select only the columns needed.
• Use clear aliases for calculated columns.
• Use table aliases when querying multiple tables.
• Avoid SELECT * in production application queries.
• Keep presentation formatting mostly outside SQL unless the query is specifically for reporting.

SELECT * and Why It Is Usually Avoided

SELECT * returns all columns from the source table or joined sources.

Example:

SELECT *
FROM dbo.Customers;

This is convenient for quick exploration but risky in production code.

Problems with SELECT *:

• Returns unnecessary data.
• Increases network traffic.
• Can prevent covering-index usage.
• Makes API contracts fragile.
• Can break code when columns are added, removed, or reordered.
• Can expose sensitive columns accidentally.
• Makes query intent less clear.

Better:

SELECT
    CustomerId,
    FullName,
    Email
FROM dbo.Customers;

Use SELECT * mainly for quick ad-hoc investigation, not stable application code.

Column Aliases

An alias gives a result column a readable name.

SELECT
    c.CustomerId,
    c.FirstName + ' ' + c.LastName AS FullName,
    c.CreatedAt AS RegisteredAt
FROM dbo.Customers AS c;

In SQL Server, ORDER BY can reference a select-list alias:

SELECT
    c.FirstName + ' ' + c.LastName AS FullName
FROM dbo.Customers AS c
ORDER BY FullName;

But WHERE generally cannot reference a select-list alias because WHERE is logically evaluated before the SELECT list.

Invalid:

SELECT
    c.FirstName + ' ' + c.LastName AS FullName
FROM dbo.Customers AS c
WHERE FullName LIKE 'A%';

Better:

SELECT
    c.FirstName + ' ' + c.LastName AS FullName
FROM dbo.Customers AS c
WHERE c.FirstName + ' ' + c.LastName LIKE 'A%';

Or use a derived table or CTE when the expression should be named first.

WITH CustomerNames AS
(
    SELECT
        c.CustomerId,
        c.FirstName + ' ' + c.LastName AS FullName
    FROM dbo.Customers AS c
)
SELECT
    CustomerId,
    FullName
FROM CustomerNames
WHERE FullName LIKE 'A%';

Logical Query Processing Order

SQL is declarative. You describe the result you want, and the optimizer decides how to execute it. However, understanding logical query processing helps avoid many mistakes.

A simplified logical order for a basic query is:

FROM
WHERE
SELECT
ORDER BY
TOP / OFFSET-FETCH result limiting

This explains why:

• WHERE filters rows before final projection.
• WHERE usually cannot use a SELECT alias.
• ORDER BY can sort by selected expressions or aliases.
• TOP is only predictable when applied with a deterministic ORDER BY.
• OFFSET-FETCH requires an ORDER BY because paging only makes sense over a defined order.

Important: this is logical reasoning, not necessarily the physical execution order. SQL Server's optimizer can reorder operations internally when it preserves the same result.

WHERE Clause

The WHERE clause filters rows based on a search condition.

Basic syntax:

SELECT column_list
FROM table_name
WHERE search_condition;

Example:

SELECT
    CustomerId,
    FullName,
    Email
FROM dbo.Customers
WHERE IsActive = 1;

Common predicates:

-- Equality
WHERE Status = 'Completed'

-- Inequality
WHERE Status <> 'Cancelled'

-- Range
WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt <  '2026-02-01'

-- IN list
WHERE Status IN ('Completed', 'Shipped')

-- Pattern matching
WHERE Email LIKE '%@example.com'

-- NULL check
WHERE DeletedAt IS NULL

-- Multiple conditions
WHERE IsActive = 1
  AND CreatedAt >= '2026-01-01'

WHERE is one of the most important clauses for performance because it determines how many rows are read, filtered, joined, sorted, and returned.

Boolean Logic in WHERE

SQL uses Boolean logic with AND, OR, and NOT.

SELECT
    ProductId,
    Name,
    Price
FROM dbo.Products
WHERE CategoryId = 5
  AND Price >= 100
  AND IsActive = 1;

AND requires all conditions to be true.

SELECT
    ProductId,
    Name,
    Price
FROM dbo.Products
WHERE CategoryId = 5
   OR CategoryId = 8;

OR requires at least one condition to be true.

Use parentheses to make intent explicit:

SELECT
    ProductId,
    Name,
    Price
FROM dbo.Products
WHERE IsActive = 1
  AND (CategoryId = 5 OR CategoryId = 8);

Without parentheses, operator precedence can produce unexpected results.

NULL Handling in WHERE

NULL means unknown or missing value. It is not equal to anything, including another NULL.

Incorrect:

SELECT
    CustomerId,
    FullName
FROM dbo.Customers
WHERE DeletedAt = NULL;

Correct:

SELECT
    CustomerId,
    FullName
FROM dbo.Customers
WHERE DeletedAt IS NULL;

To find non-null values:

SELECT
    CustomerId,
    FullName
FROM dbo.Customers
WHERE DeletedAt IS NOT NULL;

Important behavior:

WHERE MiddleName <> 'John'

This does not return rows where MiddleName is NULL, because the comparison is unknown, not true.

If you want rows that are either not John or missing:

WHERE MiddleName <> 'John'
   OR MiddleName IS NULL;

Interview point: SQL uses three-valued logic: true, false, and unknown.

BETWEEN, IN, and LIKE

BETWEEN checks an inclusive range.

SELECT
    OrderId,
    OrderDate
FROM dbo.Orders
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-01-31';

For datetime columns, this can be risky because it includes only rows exactly up to midnight at the end date if the end value is interpreted as 2026-01-31 00:00:00.

Safer date range:

SELECT
    OrderId,
    OrderDate
FROM dbo.Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate <  '2026-02-01';

IN checks membership in a list.

SELECT
    OrderId,
    Status
FROM dbo.Orders
WHERE Status IN ('Completed', 'Shipped', 'Delivered');

LIKE performs pattern matching.

SELECT
    CustomerId,
    Email
FROM dbo.Customers
WHERE Email LIKE 'admin%';

Common wildcards:

Performance note:

WHERE Email LIKE 'admin%'

can often use an index more effectively than:

WHERE Email LIKE '%admin%'

A leading wildcard usually prevents a normal index seek.

Sargability

Sargability means a predicate can effectively use an index seek. A sargable predicate allows SQL Server to search directly for matching rows instead of scanning many rows.

Sargable:

WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt <  '2026-02-01'

Often non-sargable:

WHERE YEAR(CreatedAt) = 2026
  AND MONTH(CreatedAt) = 1

The second query applies functions to the column, which can make it harder for SQL Server to use an index on CreatedAt efficiently.

Better:

WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt <  '2026-02-01'

Other common non-sargable patterns:

WHERE LOWER(Email) = '[email protected]'
WHERE ISNULL(Status, '') = 'Completed'
WHERE Price + 10 > 100
WHERE CAST(CreatedAt AS date) = '2026-01-01'

Better patterns:

WHERE Email = '[email protected]'
WHERE Status = 'Completed'
WHERE Price > 90
WHERE CreatedAt >= '2026-01-01'
  AND CreatedAt <  '2026-01-02'

Sargability is a frequent interview topic because it connects basic SQL syntax to real performance.

ORDER BY Clause

ORDER BY sorts the result set.

Basic syntax:

SELECT column_list
FROM table_name
ORDER BY column_name [ASC | DESC];

Example:

SELECT
    ProductId,
    Name,
    Price
FROM dbo.Products
ORDER BY Price DESC;

ASC means ascending order and is the default. DESC means descending order.

Multiple columns:

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC;

This sorts newest orders first. If two orders have the same OrderDate, OrderId breaks the tie.

Best practices:

• Use ORDER BY whenever result order matters.
• Include a unique tie-breaker for deterministic pagination.
• Avoid ordinal positions like ORDER BY 2 in production code.
• Be aware that sorting large result sets can be expensive.
• Use indexes that support common sort patterns when performance matters.

Result Order Is Not Guaranteed Without ORDER BY

SQL tables represent unordered sets of rows. Without ORDER BY, SQL Server does not guarantee result order.

Unreliable:

SELECT
    CustomerId,
    FullName
FROM dbo.Customers;

Even if rows appear ordered during testing, that order can change because of:

• Different execution plans.
• Index changes.
• Parallelism.
• Statistics updates.
• New rows.
• Page splits.
• SQL Server version changes.

Reliable:

SELECT
    CustomerId,
    FullName
FROM dbo.Customers
ORDER BY FullName ASC, CustomerId ASC;

Interview point: if order matters, always use ORDER BY.

Deterministic Ordering

A deterministic order means rows are returned in a stable, predictable sequence. This is especially important for TOP and pagination.

Potentially nondeterministic:

SELECT TOP (10)
    OrderId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;

If many rows share the same OrderDate, SQL Server can return any 10 among tied rows.

More deterministic:

SELECT TOP (10)
    OrderId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC;

OrderId acts as a tie-breaker.

For pagination, deterministic ordering is critical. Without a unique tie-breaker, rows can appear on multiple pages or be skipped when ties exist.

ORDER BY and Indexes

Sorting can be expensive. SQL Server may need a Sort operator if no useful index supports the order.

Query:

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC, OrderId DESC;

Helpful index:

CREATE INDEX IX_Orders_CustomerId_OrderDate_OrderId
ON dbo.Orders (CustomerId, OrderDate DESC, OrderId DESC)
INCLUDE (TotalAmount);

This index helps because it supports both the filter and the sort.

Important trade-off:

• Indexes improve reads.
• Indexes add storage cost.
• Indexes slow writes because they must be maintained.
• Too many indexes can hurt insert/update/delete performance.

Interview point: WHERE and ORDER BY should be considered together when designing indexes.

TOP Clause

TOP limits the number or percentage of rows returned by a query.

Syntax:

SELECT TOP (expression) column_list
FROM table_name
ORDER BY column_name;

Example:

SELECT TOP (5)
    ProductId,
    Name,
    Price
FROM dbo.Products
ORDER BY Price DESC;

This returns the five most expensive products.

TOP can also use variables:

DECLARE @Limit int = 10;

SELECT TOP (@Limit)
    OrderId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC;

Best practice: use parentheses around the TOP expression.

TOP Without ORDER BY

TOP without ORDER BY returns an arbitrary set of rows. It does not mean the first rows inserted, newest rows, or lowest primary keys unless you explicitly sort.

Unreliable:

SELECT TOP (10)
    OrderId,
    OrderDate
FROM dbo.Orders;

Reliable newest orders:

SELECT TOP (10)
    OrderId,
    OrderDate
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC;

Interview point: TOP should almost always be paired with ORDER BY in SELECT queries when the specific rows matter.

TOP WITH TIES

WITH TIES returns additional rows that tie with the last row based on the ORDER BY expression.

Example:

SELECT TOP (3) WITH TIES
    EmployeeId,
    FullName,
    SalesAmount
FROM dbo.EmployeeSales
ORDER BY SalesAmount DESC;

If the third-highest sales amount is shared by multiple employees, all tied employees are returned. This means the result can contain more than 3 rows.

Use WITH TIES when business logic requires all rows tied at the cutoff.

Example use cases:

• Top-scoring students.
• Highest-selling employees.
• Top products by rating.
• Shared ranking cutoff.

TOP PERCENT

TOP PERCENT returns a percentage of rows.

SELECT TOP (10) PERCENT
    ProductId,
    Name,
    Price
FROM dbo.Products
ORDER BY Price DESC;

This returns approximately the top 10 percent of products by price.

In application development, TOP (n) is more common than TOP PERCENT because APIs and pages usually need a fixed number of rows.

OFFSET-FETCH

OFFSET-FETCH is part of the ORDER BY clause and is used for pagination.

Syntax:

SELECT column_list
FROM table_name
ORDER BY sort_column
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

Example:

DECLARE @PageNumber int = 3;
DECLARE @PageSize int = 25;

SELECT
    ProductId,
    Name,
    Price
FROM dbo.Products
ORDER BY Name ASC, ProductId ASC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

This skips rows from earlier pages and returns the next page.

Important rules:

• OFFSET-FETCH requires ORDER BY.
• OFFSET can be used without FETCH to skip rows.
• FETCH requires OFFSET.
• Deterministic ordering is important.
• Deep pagination can become slow.

TOP vs OFFSET-FETCH

TOP and OFFSET-FETCH both limit rows, but they are used for different scenarios.

Use TOP for:

SELECT TOP (10) ... ORDER BY CreatedAt DESC;

Use OFFSET-FETCH for:

ORDER BY CreatedAt DESC, Id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

Pagination with OFFSET-FETCH

Typical pagination query:

DECLARE @PageNumber int = 1;
DECLARE @PageSize int = 20;

SELECT
    c.CustomerId,
    c.FullName,
    c.Email,
    c.CreatedAt
FROM dbo.Customers AS c
WHERE c.IsActive = 1
ORDER BY c.CreatedAt DESC, c.CustomerId DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Important details:

• Always use a stable ORDER BY.
• Add a unique tie-breaker such as primary key.
• Validate page number and page size.
• Set a maximum page size.
• Add indexes for common filter and sort patterns.
• Avoid deep pagination when performance matters.

Example validation rule in application code:

PageNumber must be >= 1.
PageSize must be between 1 and 100.

Deep Pagination Problem

OFFSET-FETCH can become slow for deep pages because SQL Server still has to process and skip many rows.

Example:

SELECT
    OrderId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC
OFFSET 100000 ROWS
FETCH NEXT 50 ROWS ONLY;

This asks SQL Server to skip 100,000 rows and return 50. Even with indexes, deep offsets can be expensive.

Possible alternatives:

• Keyset pagination.
• Seek method pagination.
• Search-after pagination.
• Restrict maximum page depth.
• Use filters to reduce the result set.
• Use cursor-like continuation tokens.

Keyset Pagination

Keyset pagination uses the last seen sort key instead of an offset. It is often faster and more stable for large tables.

First page:

SELECT TOP (@PageSize)
    OrderId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE Status = 'Completed'
ORDER BY OrderDate DESC, OrderId DESC;

Next page using the last row from the previous page:

DECLARE @LastOrderDate datetime2 = '2026-05-01T10:30:00';
DECLARE @LastOrderId bigint = 12345;
DECLARE @PageSize int = 20;

SELECT TOP (@PageSize)
    OrderId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE Status = 'Completed'
  AND
  (
      OrderDate < @LastOrderDate
      OR (OrderDate = @LastOrderDate AND OrderId < @LastOrderId)
  )
ORDER BY OrderDate DESC, OrderId DESC;

Benefits:

• Avoids skipping large numbers of rows.
• More efficient for deep browsing.
• More stable when rows are inserted between page requests.

Trade-offs:

• Harder to jump directly to page 50.
• Requires stable sort keys.
• Client must remember the last key.
• More complex than OFFSET-FETCH.

Use keyset pagination for infinite scroll, activity feeds, large order lists, and high-scale APIs.

Filtering Before Sorting and Paging

A typical query filters first, then sorts and limits the matching result set.

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
  AND Status = 'Completed'
ORDER BY OrderDate DESC, OrderId DESC
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;

The index should often support both filtering and sorting.

Possible index:

CREATE INDEX IX_Orders_Customer_Status_Date_Id
ON dbo.Orders (CustomerId, Status, OrderDate DESC, OrderId DESC)
INCLUDE (TotalAmount);

This can reduce the need to scan, sort, and lookup extra data.

Common Query Patterns

Latest N Records

SELECT TOP (10)
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderId DESC;

Active Records Only

SELECT
    ProductId,
    Name,
    Price
FROM dbo.Products
WHERE IsActive = 1
ORDER BY Name ASC;

Search by Prefix

SELECT
    CustomerId,
    FullName,
    Email
FROM dbo.Customers
WHERE FullName LIKE @SearchText + '%'
ORDER BY FullName ASC, CustomerId ASC;

API Pagination

SELECT
    TicketId,
    Title,
    Status,
    CreatedAt
FROM dbo.SupportTickets
WHERE Status = @Status
ORDER BY CreatedAt DESC, TicketId DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

Top Customers by Revenue

SELECT TOP (10)
    c.CustomerId,
    c.FullName,
    SUM(o.TotalAmount) AS TotalRevenue
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
    ON o.CustomerId = c.CustomerId
WHERE o.Status = 'Completed'
GROUP BY
    c.CustomerId,
    c.FullName
ORDER BY TotalRevenue DESC, c.CustomerId ASC;

Performance Considerations

Important performance points:

• WHERE predicates should be sargable when possible.
• Use indexes that match common filters and sort orders.
• Avoid returning unnecessary columns.
• Avoid sorting huge result sets without indexes.
• Use TOP with ORDER BY for predictable limited results.
• Use OFFSET-FETCH for simple pagination.
• Avoid deep OFFSET pagination for large tables.
• Use keyset pagination for large or high-traffic lists.
• Watch for implicit conversions that prevent index usage.
• Avoid functions on indexed columns in predicates.
• Use query execution plans to validate assumptions.
• Parameterize queries from application code.

Implicit Conversion Problems

Implicit conversions can hurt performance when SQL Server must convert a column value before comparison.

Example problem:

-- CustomerId is int, but @CustomerId is nvarchar
WHERE CustomerId = @CustomerId

If data types do not match, SQL Server may convert values and reduce index effectiveness.

Better:

-- @CustomerId should be int
WHERE CustomerId = @CustomerId

Best practices:

• Match parameter types to column types.
• Avoid comparing strings to numeric columns.
• Avoid comparing different date/time types carelessly.
• Use correct parameter sizes for strings.
• Watch execution plans for implicit conversion warnings.

SQL Injection and Parameterized Queries

WHERE clauses are often built from user input. Never concatenate raw user input into SQL strings.

Unsafe:

var sql = "SELECT * FROM dbo.Customers WHERE Email = '" + email + "'";

Safe with parameters:

var sql = "SELECT CustomerId, FullName, Email FROM dbo.Customers WHERE Email = @Email";

In application code, use parameterized queries, stored procedures with parameters, or an ORM that parameterizes values correctly.

Parameterized SQL protects against SQL injection and helps query plan reuse.

Common Mistakes

Common mistakes include:

• Using SELECT * in application queries.
• Using TOP without ORDER BY when specific rows matter.
• Using ORDER BY only during testing and assuming natural order in production.
• Using OFFSET-FETCH without a unique tie-breaker.
• Allowing very large page sizes.
• Using deep offset pagination on large tables.
• Applying functions to indexed columns in WHERE.
• Using BETWEEN incorrectly with datetime ranges.
• Comparing to NULL with = instead of IS NULL.
• Forgetting parentheses around mixed AND and OR logic.
• Creating indexes for every query without considering write cost.
• Ignoring implicit conversions.
• Sorting by ordinal position such as ORDER BY 1 in production code.
• Building SQL with string concatenation from user input.

Best Practices

Select only needed columns.

Use explicit column names instead of SELECT * in production queries.

Use WHERE to reduce rows as early as possible logically.

Write sargable predicates.

Use IS NULL and IS NOT NULL for null checks.

Use half-open date ranges for datetime filtering.

Use ORDER BY whenever result order matters.

Add a unique tie-breaker to ORDER BY for deterministic results.

Use TOP with ORDER BY when returning the first N rows.

Use OFFSET-FETCH for simple page-number pagination.

Use keyset pagination for large, deep, or high-traffic pagination.

Validate page size and page number in application code.

Match parameter data types to column data types.

Use parameterized queries to avoid SQL injection.

Design indexes based on common WHERE and ORDER BY patterns.

Read execution plans when performance matters.