SELECT, FROM, Aliases, and Query Result Sets

Learning outcomes

SELECT is SQL’s primary data-retrieval statement. It does not open a table and display it unchanged. It evaluates a query and returns a new result with its own columns, names, order, and values.

The smallest useful retrieval query

SELECT    customer_id,    full_name,    cityFROM customer;

The statement has two central parts:

The comma-separated expressions after SELECT form the select list. In this example, every expression is a column reference.

A result is not the source table

A table is a persistent database object governed by a schema and constraints. A result set is produced when the DBMS evaluates a statement. It may expose fewer columns, rename them, repeat rows, compute new values, or combine multiple sources.

Build the Chapter 4 practice database

Use a fresh SQLite database so every Chapter 4 example begins from the same small sales dataset.

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer (    customer_id  INTEGER PRIMARY KEY,    full_name    TEXT NOT NULL,    city         TEXT,    segment      TEXT NOT NULL                 CHECK (segment IN ('consumer', 'business'))) STRICT;CREATE TABLE product (    product_id   INTEGER PRIMARY KEY,    sku          TEXT NOT NULL UNIQUE,    product_name TEXT NOT NULL,    category     TEXT NOT NULL,    unit_price   REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale (    sale_id      INTEGER PRIMARY KEY,    customer_id  INTEGER NOT NULL                 REFERENCES customer(customer_id),    product_id   INTEGER NOT NULL                 REFERENCES product(product_id),    quantity     INTEGER NOT NULL CHECK (quantity > 0),    discount_rate REAL NOT NULL DEFAULT 0                  CHECK (discount_rate BETWEEN 0 AND 1),    sold_at      TEXT NOT NULL) STRICT;INSERT INTO customer    (customer_id, full_name, city, segment)VALUES    (1, 'Nadia Rahimi', 'Tehran', 'consumer'),    (2, 'Omar Haddad', 'Berlin', 'business'),    (3, 'Lina Chen', NULL, 'consumer'),    (4, 'Ava Morgan', 'Berlin', 'consumer');INSERT INTO product    (product_id, sku, product_name, category, unit_price)VALUES    (10, 'DB-101', 'Database Foundations', 'course', 49.00),    (11, 'SQL-201', 'SQL Query Practice', 'course', 69.00),    (12, 'REF-001', 'SQL Reference Card', 'book', 15.00),    (13, 'LAB-001', 'SQLite Lab Bundle', 'lab', 29.00);INSERT INTO sale    (sale_id, customer_id, product_id, quantity, discount_rate, sold_at)VALUES    (100, 1, 10, 1, 0.00, '2026-08-01 09:15:00'),    (101, 2, 11, 3, 0.10, '2026-08-01 10:45:00'),    (102, 1, 12, 2, 0.00, '2026-08-02 11:30:00'),    (103, 4, 10, 1, 0.15, '2026-08-03 13:05:00'),    (104, 2, 13, 2, 0.05, '2026-08-03 15:20:00'),    (105, 3, 12, 1, 0.00, '2026-08-04 08:00:00');

The schema has three tables: customers, products, and sales. Chapter 4 primarily reads those facts; later chapters will add filtering, joining, grouping, and changes.

Projection: choosing the output columns

In relational terminology, choosing attributes is called projection. SQL’s select list is more general than pure relational projection because it may contain expressions and may preserve duplicate rows, but the core idea is the same: control what the query returns.

SELECT customer_id, full_nameFROM customer;SELECT product_name, unit_priceFROM product;

The first result has two customer columns. The second has two product columns. Neither query changes the underlying tables.

Column aliases name the result

An alias changes the output label without renaming the stored column.

SELECT    customer_id AS id,    full_name   AS customer_name,    city        AS customer_cityFROM customer;

AS is optional in many SQL dialects for column aliases, but writing it explicitly makes the intent visible and avoids confusion with missing commas.

Table aliases shorten and disambiguate references

A table alias gives a source a query-local name. It becomes particularly important when multiple tables contain columns such as customer_id or product_id.

SELECT    c.customer_id,    c.full_name,    c.segmentFROM customer AS c;

Once customer AS c is declared, use c consistently in that query block. Qualification makes the origin of each column obvious and prepares the query for joins.

What the DBMS conceptually does

SQL is declarative: the query states the desired result rather than a row-by-row algorithm. A useful conceptual model for this simple query is:

Do not treat the written order of clauses as a guarantee of physical execution. Query planning and optimization are introduced later in the course.

Lab: inspect basic result sets

-- Result 1: customer identity and labels.SELECT    c.customer_id AS customer_id,    c.full_name   AS customer_name,    c.city        AS cityFROM customer AS c;-- Result 2: product catalogue projection.SELECT    p.sku          AS sku,    p.product_name AS product_name,    p.unit_price   AS unit_priceFROM product AS p;-- Result 3: raw sale facts.SELECT    s.sale_id,    s.customer_id,    s.product_id,    s.quantity,    s.sold_atFROM sale AS s;

Compare the number of source columns with the number returned by each statement. Notice that an alias changes only the result header.

Common mistakes

Assuming output order

The database may return rows in any order unless a later ORDER BY clause defines it.

Using ambiguous names

As queries grow, unqualified columns can become unclear or invalid. Use meaningful table aliases.

Confusing aliases with schema changes

An alias is temporary output metadata, not a rename operation.

Thinking SELECT means all columns

SELECT introduces an output list. The author must decide what belongs in that list.

Checkpoint

Summary and references

• FROM identifies source relations.
• The select list defines the result columns.
• A query result is separate from its source table.
• Column aliases label output; table aliases label sources.
• Qualified names improve clarity and become essential in multi-table queries.

References

• SQLite: SELECT
• SQLite: Generation of the set of result rows
• PostgreSQL: Select Lists