Fundamental Data Retrieval

Introduction

SQL is a declarative language: a developer specifies the required result, while the database management system determines how to retrieve it. The central command for retrieval is SELECT. A query produces a result set, but a result set has no guaranteed sequence unless the query includes an ORDER BY clause. This distinction is essential in application development, reporting, testing, and pagination.

Fundamental data retrieval involves choosing columns, creating derived values, assigning meaningful labels, restricting rows, controlling order, limiting output, and handling duplicates. These operations form the foundation for more advanced topics such as joins, aggregation, subqueries, views, and database-backed APIs.

Learning Objectives

By the end of this chapter, you should be able to:

• construct SELECT queries using projection and expressions;
• assign aliases to columns and derived values;
• distinguish row filtering from result ordering;
• use comparison, logical, range, membership, and pattern predicates;
• sort results predictably, including results containing NULL;
• implement pagination and explain its consistency limitations;
• remove duplicates deliberately with DISTINCT;
• write readable, portable, and maintainable SQL;
• evaluate retrieval queries in a realistic application context.

A Working Relation and Sample Data

The examples use a simplified products table:

The exact Boolean syntax differs between database products. PostgreSQL supports TRUE and FALSE; some systems use numeric or character representations. SQL examples should therefore be adapted to the target DBMS.

The Logical Idea of a SELECT Query

A simple query is:

SELECT product_name, unit_price
FROM products;

FROM identifies the source table, and the select list identifies the attributes to return. In relational terminology, selecting columns is called projection. Selecting rows is called restriction or selection, although SQL uses the keyword WHERE for the latter.

A useful conceptual processing order is:

1. FROM identifies rows from source tables.
2. WHERE removes rows that do not satisfy a predicate.
3. SELECT produces requested columns and expressions.
4. DISTINCT removes duplicate result rows when specified.
5. ORDER BY defines presentation order.
6. OFFSET and FETCH or LIMIT restrict the returned page.

This is a logical model, not necessarily the physical execution order. The optimizer may rearrange operations while preserving the query's declared meaning.

Selecting All Columns

SELECT *
FROM products;

The asterisk is convenient for exploration but poor practice in production interfaces. It can return unnecessary data, expose newly added sensitive columns, increase network traffic, and make application code unstable when a schema changes. Prefer an explicit select list in application queries and reports.

Expressions in the Select List

A select list may contain expressions:

SELECT
    product_name,
    unit_price,
    unit_price * 1.20 AS price_including_tax
FROM products;

Here, 1.20 represents an assumed 20% tax multiplier. The expression is meaningful only if the tax assumption applies to every row and if unit_price and the resulting value use compatible numeric types. In real systems, tax rates may vary by jurisdiction and should normally come from governed data rather than a hard-coded constant.

Other examples include:

SELECT
    product_name,
    stock_quantity,
    stock_quantity * unit_price AS inventory_value
FROM products;

This estimates the value of currently stocked units. It is not necessarily an accounting valuation because accounting may require cost, impairment, currency, and valuation-policy information.

Expressions can use arithmetic operators, string functions, date functions, conditional expressions, and database-specific functions. NULL requires particular care: in ordinary SQL arithmetic, an expression involving NULL usually evaluates to NULL, because the value is unknown.

Aliases and Result Naming

An alias gives a table or output column a temporary query-level name:

SELECT
    product_name AS name,
    unit_price AS price
FROM products;

The alias improves readability for users and provides a stable label for client applications. The AS keyword is recommended for column aliases, although some systems permit its omission.

Aliases may label expressions:

SELECT
    product_name,
    unit_price * stock_quantity AS inventory_value
FROM products;

A column alias generally cannot be used in the same query block's WHERE clause because filtering logically occurs before the select-list alias is created. Use a repeated expression, a subquery, or a common table expression when necessary:

SELECT name, price
FROM (
    SELECT product_name AS name, unit_price AS price
    FROM products
) AS p
WHERE price < 50;

Use simple, descriptive aliases. Quoted aliases preserve spaces and capitalization but often make later references cumbersome. For example, AS "Retail Price" may be appropriate for a presentation report, but AS retail_price is usually better for application-facing output.

Filtering Rows with WHERE

A WHERE clause retains only rows for which its predicate evaluates to TRUE:

SELECT product_name, unit_price
FROM products
WHERE unit_price < 50;

Rows for which the predicate is FALSE or UNKNOWN are excluded. This three-valued logic matters when columns contain NULL.

Comparison Predicates

Common comparison operators are:

Example:

SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity = 0;

Logical Operators

AND requires both conditions to be true; OR requires at least one; NOT reverses a predicate.

SELECT product_name, category, unit_price
FROM products
WHERE category = 'Accessories'
  AND unit_price < 50;

Use parentheses when conditions are mixed. AND has higher precedence than OR, but relying on precedence reduces readability:

SELECT product_name
FROM products
WHERE (category = 'Accessories' AND unit_price < 50)
   OR discontinued = TRUE;

Range and Membership Predicates

BETWEEN is inclusive at both ends:

SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 40 AND 100;

This includes prices exactly equal to 40 and 100. IN provides a readable alternative to repeated equality comparisons:

SELECT product_name, category
FROM products
WHERE category IN ('Accessories', 'Displays');

NOT IN can produce unexpected results if its list contains NULL; use it only when the membership set is known not to contain null values, or use a carefully designed NOT EXISTS condition in more advanced queries.

Pattern Matching

LIKE supports simple pattern matching. % represents zero or more characters, and _ represents exactly one character.

SELECT product_name
FROM products
WHERE product_name LIKE '%Monitor%';

Case sensitivity depends on the DBMS, collation, and configuration. For portable behavior, do not assume that LIKE is either case-sensitive or case-insensitive; verify the platform's rules or use an explicit case-normalization strategy.

NULL and Unknown Values

NULL means missing, unknown, or not applicable; it is not equal to zero, an empty string, or FALSE. The correct test is:

SELECT product_name
FROM products
WHERE discontinued IS NULL;

and not:

WHERE discontinued = NULL

The latter evaluates to UNKNOWN, not TRUE. Use IS NOT NULL to find known values. When displaying or calculating with missing values, functions such as COALESCE may provide a replacement:

SELECT product_name, COALESCE(stock_quantity, 0) AS available_units
FROM products;

This replacement is appropriate only when a missing quantity should semantically mean zero. It must not conceal data-quality problems.

Sorting with ORDER BY

Without ORDER BY, the database is free to return rows in any order. An apparent order caused by an index, insertion sequence, or execution plan is not a contract. To define order, write:

SELECT product_name, unit_price
FROM products
ORDER BY unit_price ASC;

ASC is the default; DESC reverses the direction. Multiple keys are applied from left to right:

SELECT product_name, unit_price, product_id
FROM products
ORDER BY unit_price DESC, product_name ASC, product_id ASC;

The final unique key is a tie-breaker. It makes ordering deterministic when earlier values are equal. A query that orders only by unit_price does not specify the relative order of products with identical prices.

Most systems allow ordering by a select-list alias:

SELECT product_name, unit_price * stock_quantity AS inventory_value
FROM products
ORDER BY inventory_value DESC;

The treatment of NULL in ordering varies. Some systems place nulls first in ascending order and others place them last. Where supported, specify NULLS FIRST or NULLS LAST; otherwise use an explicit expression appropriate to the DBMS.

Pagination and Result Size

Applications rarely display thousands of rows at once. Pagination returns a subset of an ordered result. Standard SQL syntax is:

SELECT product_id, product_name, unit_price
FROM products
ORDER BY product_name ASC, product_id ASC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

Many systems instead use:

SELECT product_id, product_name, unit_price
FROM products
ORDER BY product_name ASC, product_id ASC
LIMIT 3 OFFSET 0;

The syntax is not universally portable, so consult the target DBMS documentation.

Pagination must include a stable order. Without ORDER BY, “page 2” has no reliable meaning. Even with ordering, offset pagination can become inconsistent when rows are inserted or deleted between requests. It can also become slower at large offsets because the engine may need to locate and discard many preceding rows.

For large, changing datasets, keyset pagination is often preferable:

SELECT product_id, product_name, unit_price
FROM products
WHERE (product_name, product_id) > (:last_name, :last_id)
ORDER BY product_name ASC, product_id ASC
FETCH FIRST 3 ROWS ONLY;

The exact row-value comparison syntax varies by DBMS. The application supplies the last row's ordering key from the previous page. Keyset pagination requires an indexed, consistently ordered key and does not naturally support jumping directly to an arbitrary page number.

Duplicate Handling with DISTINCT

DISTINCT removes duplicate combinations of all selected expressions:

SELECT DISTINCT category
FROM products;

If several columns are selected, uniqueness applies to the whole row:

SELECT DISTINCT category, discontinued
FROM products;

This does not mean “choose one arbitrary product per category.” That requirement needs a defined rule, often involving window functions or aggregation, which are beyond the basic scope of this chapter.

DISTINCT can require sorting or hashing and may be expensive on large results. Do not add it merely to hide duplicates caused by an incorrect join or flawed data model. First identify why duplicates occur. If the business question genuinely asks for unique values, then DISTINCT expresses that intention clearly.

Readable SQL Style

Readable SQL reduces defects and lowers maintenance cost. Recommended practices include:

• use one select-list item per line for nontrivial queries;
• use consistent indentation and capitalization;
• qualify columns when multiple tables may later be introduced;
• use explicit column lists rather than * in production code;
• use meaningful aliases;
• place each major clause on a separate line;
• use parentheses for mixed Boolean logic;
• make ordering explicit whenever presentation or pagination matters;
• parameterize user-supplied values rather than concatenating strings;
• document assumptions about currency, time zone, tax, and nullability.

A well-formatted query might be:

SELECT
    p.product_id,
    p.product_name,
    p.unit_price,
    p.stock_quantity,
    p.unit_price * p.stock_quantity AS inventory_value
FROM products AS p
WHERE p.discontinued = FALSE
  AND p.stock_quantity > 0
ORDER BY inventory_value DESC, p.product_id ASC
FETCH FIRST 10 ROWS ONLY;

The output is limited to active, stocked products and is ordered by derived inventory value, with a unique identifier as a tie-breaker.

Worked Application: Product Catalogue Search

Background

Consider an online retailer whose catalogue page must show active products in a selected category. Users can specify a maximum price, choose a page size, and request the next page.

Problem

The implementation must avoid exposing discontinued products, must return only products in stock, and must remain stable when two products have the same name or price.

Approach

A parameterized query is preferable:

SELECT
    p.product_id,
    p.product_name,
    p.unit_price,
    p.stock_quantity
FROM products AS p
WHERE p.category = :category
  AND p.unit_price <= :maximum_price
  AND p.stock_quantity > 0
  AND p.discontinued = FALSE
ORDER BY p.product_name ASC, p.product_id ASC
OFFSET :offset_value ROWS
FETCH NEXT :page_size ROWS ONLY;

:category, :maximum_price, and the pagination parameters are bind parameters supplied by the application. They should not be inserted through string concatenation. Ordering by name makes the catalogue intuitive; ordering by the unique product_id resolves ties.

Outcome and Evaluation

The query expresses projection, filtering, deterministic sorting, and pagination in one readable statement. An index beginning with frequently filtered or ordered columns may improve performance, but indexing decisions require workload analysis and execution-plan inspection. The application should also validate page size limits and reject negative or unreasonable offsets.

Learning Points

A correct catalogue query is more than a syntactically valid SELECT. It must define the business population, return only required attributes, protect against injection, specify a stable order, and acknowledge changes that may occur while users move between pages.

Limitations, Security, and Governance

Retrieval queries can expose personal, confidential, or regulated information. Least privilege should ensure that an application account can read only the columns and rows it needs. Views, row-level security, masking, and auditing may be appropriate for sensitive systems.

Parameterization protects values from SQL injection, but it does not automatically make dynamically chosen table names, column names, or sort directions safe. Such identifiers should be selected from a server-side allowlist. Query logs may contain sensitive parameters and therefore require retention and access controls.

Correctness also includes temporal and cultural issues. Currency values need a defined currency and rounding policy; timestamps need a time-zone policy; text comparison depends on collation; and case behavior differs among systems. A query that is logically valid may still be operationally wrong if these assumptions are undocumented.

Contemporary Practice and AI-Assisted SQL

Modern development tools can generate SQL from natural-language prompts or application metadata. Such systems can accelerate exploration but may invent column names, omit authorization predicates, mishandle NULL, or produce unstable pagination. Generated SQL must be reviewed against the schema, tested with boundary cases, checked for injection risks, and evaluated with an execution plan. Human responsibility for privacy, correctness, and access control remains essential.

Chapter Summary

SELECT retrieves data through projection, expressions, aliases, filtering, ordering, pagination, and duplicate handling. A result set has no guaranteed order unless ORDER BY specifies one. WHERE uses predicates whose results may be TRUE, FALSE, or UNKNOWN, making correct NULL handling essential. Expressions and aliases improve the usefulness and readability of output, while DISTINCT should be used only when uniqueness is part of the requirement. Pagination requires a stable order and may benefit from keyset techniques for large, changing datasets. Professional SQL uses explicit columns, meaningful aliases, parameterized values, clear formatting, deterministic tie-breakers, and careful attention to security, data quality, and DBMS-specific behavior.

Key Terms

Projection: Selecting columns from a relation.

Restriction: Selecting rows that satisfy a predicate.

Result set: The rows and columns produced by a query.

Predicate: A condition evaluated as true, false, or unknown.

Alias: A temporary name for a table or output expression.

Expression: A computation or value in a SQL statement.

WHERE: The clause that filters rows.

ORDER BY: The clause that defines result ordering.

Tie-breaker: A later ordering key used to resolve equal earlier values.

NULL: A marker for missing, unknown, or inapplicable information.

Three-valued logic: SQL logic involving true, false, and unknown.

BETWEEN: An inclusive range predicate.

IN: A membership predicate.

LIKE: A pattern-matching predicate.

DISTINCT: A modifier that removes duplicate result rows.

Pagination: Returning a limited portion of a larger result.

Offset pagination: Pagination that skips a specified number of rows.

Keyset pagination: Pagination based on the last returned ordering key.

Bind parameter: A separately supplied value used safely in a query.

Deterministic ordering: Ordering that fully specifies the relative position of rows.

Review Questions

Short Answer Questions

1. Why is ORDER BY necessary when an application depends on row sequence?
2. What is the difference between projection and restriction?
3. Why does column = NULL fail to identify null values?
4. What does DISTINCT apply to when several expressions appear in the select list?
5. State two reasons why explicit column lists are preferred to SELECT * in production queries.

Long Answer Questions

1. Explain the logical purpose of FROM, WHERE, SELECT, DISTINCT, ORDER BY, and pagination clauses.
2. Compare offset pagination with keyset pagination, including their stability and performance characteristics.
3. Explain SQL three-valued logic and illustrate how it affects comparisons involving NULL.
4. Develop a readable query for an inventory report and justify its expressions, filters, aliases, and ordering.
5. Discuss how parameterization, least privilege, and allowlisting contribute to secure data retrieval.

Analytical / Application Questions

1. A catalogue query orders only by unit_price and displays ten rows per page. Analyze the possible problems when several products have the same price and propose a correction.
2. A developer adds DISTINCT after observing duplicate products in a report. Analyze why this may conceal a design or join problem and describe a better diagnostic process.
3. Design a retrieval strategy for a frequently changing product catalogue with millions of rows. Justify the ordering keys, pagination method, indexing investigation, and consistency assumptions.

Multiple Choice Questions with Answers

Question 1

Which clause filters rows before the result is returned?

A. WHERE

B. ORDER BY

C. DISTINCT

D. AS

Correct Answer: A. WHERE

Question 2

What does SELECT DISTINCT category FROM products return?

A. Every product, sorted by category

B. One row for each different category value

C. Only categories with more than one product

D. The first product in each category

Correct Answer: B. One row for each different category value

Question 3

Which condition correctly tests whether stock_quantity is missing?

A. stock_quantity = NULL

B. stock_quantity == NULL

C. stock_quantity IS NULL

D. stock_quantity <> NULL

Correct Answer: C. stock_quantity IS NULL

Question 4

A query must order primarily by product_name ascending. product_id is declared unique and must resolve equal-name ties. Which clause gives a deterministic order?

A. ORDER BY product_name ASC

B. ORDER BY product_name ASC, product_id ASC

C. ORDER BY product_id DESC

D. ORDER BY product_name DESC, product_id ASC

Correct Answer: B. ORDER BY product_name ASC, product_id ASC

Question 5

What is the main purpose of an alias such as unit_price * stock_quantity AS inventory_value?

A. To permanently rename the database column

B. To filter rows automatically

C. To provide a readable name for the query output expression

D. To remove duplicate rows

Correct Answer: C. To provide a readable name for the query output expression

Question 6

Which statement about BETWEEN 40 AND 100 is correct?

A. It excludes both endpoints

B. It includes both endpoints

C. It includes 40 but excludes 100

D. It includes 100 but excludes 40

Correct Answer: B. It includes both endpoints

Question 7

Why should user-supplied values normally be passed as bind parameters?

A. They automatically create indexes

B. They prevent all database errors

C. They reduce SQL injection risk and separate data from query text

D. They guarantee faster execution in every DBMS

Correct Answer: C. They reduce SQL injection risk and separate data from query text

Question 8

What is a key requirement for reliable pagination?

A. Using SELECT *

B. Omitting ORDER BY

C. Using a stable, sufficiently specific ordering

D. Using DISTINCT in every query

Correct Answer: C. Using a stable, sufficiently specific ordering

Question 9

What does LIKE '%Monitor%' generally match?

A. Only values equal to Monitor

B. Values containing Monitor anywhere in the text

C. Values beginning with exactly one character followed by Monitor

D. Only null values

Correct Answer: B. Values containing Monitor anywhere in the text

Question 10

Which practice is most appropriate for a production retrieval query?

A. Concatenate raw user input into SQL text

B. Rely on insertion order for display order

C. Use explicit columns, parameters, clear formatting, and a defined order

D. Add DISTINCT whenever duplicate-looking rows appear

Correct Answer: C. Use explicit columns, parameters, clear formatting, and a defined order