SELECT and Column Control

The Problem with SELECT *

Every analyst starts with SELECT * FROM table. It works, and it is wrong for production work. When you select every column, you pull data your analysis does not need across the network, materialize it in memory, and hand it to a report that will never display most of it. On a table with fifty columns and ten million rows, that is a real cost.

Column control means deciding exactly which fields the query returns. The projection — the list of columns between SELECT and FROM — defines the shape of your result set. Treating that shape as a deliberate decision, rather than an accident of typing *, is the difference between a query that serves a purpose and one that merely runs.

The Three Jobs of a Projection

A SELECT clause does three distinct things:

1. Selection — choose which source columns appear in the output.
2. Aliasing — rename those columns so the output is self-describing.
3. Computation — derive new values from existing columns using expressions.

Most beginners treat these as one step. They are not. Selection determines what data you pull. Aliasing determines how the consumer reads it. Computation determines what analytical value you add. Each deserves its own decision.

A Worked Example

Consider an orders table with columns order_id, customer_id, order_date, quantity, unit_price, and discount_pct. A revenue report needs the order identifier, the date, and the net revenue per line. The net revenue is not stored — it must be computed as quantity * unit_price * (1 - discount_pct / 100).

SELECT
    order_id,
    order_date,
    quantity * unit_price * (1 - discount_pct / 100) AS net_revenue
FROM orders;

This query does all three jobs: it selects two stored columns, computes a third, and aliases the computed column so the output header reads net_revenue instead of the raw expression. Anyone consuming this result — a BI tool, a Python script, a colleague — can understand it without seeing the query.

What Changes When You Control Columns

• Readability: Output headers match business language, not database naming conventions.
• Performance: Fewer columns means less data transferred and materialized.
• Stability: When a table adds a column, a controlled projection does not change shape.
• Intent: The query documents what the analysis needs, making it reviewable.

The rest of this topic builds each of these skills: selecting exact columns, aliasing them, computing derived fields, and using DISTINCT to control row uniqueness.

Explicit Column Lists

The syntax for selecting specific columns is straightforward: list them between SELECT and FROM, separated by commas.

SELECT first_name, last_name, department, salary
FROM employees;

This returns only the four named columns for every row in employees. The order of columns in the output matches the order in the SELECT list, not the order they are defined in the table.

Column Order Is a Presentation Decision

-- Same data, different presentation
SELECT department, first_name, last_name, salary FROM employees;
SELECT salary, last_name, first_name, department FROM employees;

Both queries return identical rows. The first reads like a directory listing. The second leads with the metric. Choose the order that matches how the consumer will read the result. When the output feeds a report, put identifying columns first and measures last.

Why SELECT * Breaks Things

SELECT * expands to every column in the table at query time. That creates three concrete problems:

1. Schema drift: Add a column to the table and every SELECT * query suddenly returns a different shape. Downstream code that expects column positions breaks silently.
2. Wasted work: A query that needs two columns from a wide table still transfers all of them. On large tables this is measurable.
3. Hidden dependencies: A reader cannot tell which columns the analysis actually uses, so they cannot tell whether a schema change is safe.

Selecting Columns from Joined Tables

When a query joins tables, column control becomes more important because multiple tables often share column names.

SELECT
    o.order_id,
    c.customer_name,
    o.order_date,
    o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Without table aliases (o and c), a column like customer_id would be ambiguous and the database would reject the query. Aliasing tables and qualifying every column with its table alias is the standard practice in analytical SQL. It prevents ambiguity and makes the query self-documenting.

When SELECT * Is Acceptable

There are legitimate uses: quick exploration during data discovery, EXPLAIN plans, and SELECT * FROM table LIMIT 10 to inspect a new table's shape. The rule is simple — exploration is fine, production queries are not. Any query that feeds a report, a dashboard, or another system should name its columns explicitly.

The AS Keyword

An alias renames a column in the output. The syntax is expression AS alias_name.

SELECT
    first_name AS fname,
    last_name AS lname,
    salary AS annual_salary
FROM employees;

The AS keyword is optional in most SQL dialects, but writing it explicitly is the convention in production code because it makes the alias unambiguous to readers and to the parser.

Aliasing Computed Columns

Computed columns have no natural name, so aliasing them is not optional in practice — the database will generate an unhelpful header like ?column? or repeat the full expression.

SELECT
    order_id,
    quantity * unit_price AS gross_revenue,
    quantity * unit_price * (1 - discount_pct / 100) AS net_revenue
FROM orders;

Without the aliases, the output headers would be the raw expressions. With them, the result is a clean table a business user can read.

Aliasing with Spaces and Special Characters

When an alias contains spaces or special characters, quote it with double quotes (standard SQL) or backticks (MySQL/MariaDB).

-- Standard SQL
SELECT first_name AS "First Name" FROM employees;

-- MySQL/MariaDB
SELECT first_name AS `First Name` FROM employees;

Quoted aliases are case-sensitive in most dialects. "First Name" and "first name" are different identifiers. Prefer single-word, snake_case aliases in production code and reserve quoted aliases for final presentation layers.

Aliasing Tables

Table aliases work the same way and serve the same purpose: they make the query shorter and unambiguous.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

The alias is defined at first use — in the FROM clause for tables, in the SELECT list for columns. Once defined, the alias replaces the full name everywhere in the query.

Alias Scope Rules

A column alias defined in the SELECT list is not visible in the WHERE clause. This is a common source of confusion.

-- This fails: annual_salary is not yet defined when WHERE runs
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

-- This works: repeat the expression, or use a subquery
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;

The reason is logical order of operations: WHERE filters rows before SELECT projects columns. The alias does not exist yet when WHERE executes. Understanding this ordering prevents a whole class of beginner errors.

Arithmetic in the Projection

The SELECT clause is not limited to stored columns. Any valid expression can appear there, and the database evaluates it once per row.

SELECT
    product_name,
    unit_price,
    quantity,
    unit_price * quantity AS line_total,
    unit_price * quantity * 0.08 AS estimated_tax
FROM order_items;

Arithmetic operators follow standard precedence: multiplication and division before addition and subtraction. Use parentheses to make the order explicit — a * (b + c) is not the same as a * b + c.

Working with NULL in Computed Fields

Any arithmetic expression involving NULL returns NULL. This is not an error — it is SQL's three-valued logic at work.

-- If discount_pct is NULL, net_revenue is NULL
SELECT
    order_id,
    quantity * unit_price * (1 - discount_pct / 100) AS net_revenue
FROM orders;

For a revenue report, a NULL net_revenue is a data quality problem. The fix is COALESCE, which returns the first non-NULL argument.

SELECT
    order_id,
    quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100) AS net_revenue
FROM orders;

COALESCE(discount_pct, 0) says: use the discount if it exists, otherwise treat it as zero. The expression now returns a number for every row. NULL handling is a deep topic — the null semantics topic in this phase covers it in full.

String Functions in the Projection

Derived fields are not limited to numbers. String functions let you shape text for output.

SELECT
    first_name || ' ' || last_name AS full_name,
    UPPER(department) AS department_code,
    SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS email_local_part
FROM employees;

The exact function names vary by dialect — CONCAT(first_name, ' ', last_name) in MySQL, first_name + ' ' + last_name in SQL Server — but the principle is the same: transform values in the projection so the output is ready to consume.

Date and Time Expressions

Dates are a common source of derived fields. Extracting components, computing intervals, and formatting output all happen in the projection.

SELECT
    order_id,
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    order_date + INTERVAL '90 days' AS follow_up_date
FROM orders;

These expressions let you build time dimensions directly in the query instead of relying on a pre-built date table. For recurring reports, computing the period in the query keeps the logic in one place.

What DISTINCT Does

SELECT DISTINCT removes duplicate rows from the result. Two rows are duplicates only if every selected column matches exactly.

-- All departments, with duplicates
SELECT department FROM employees;
-- Engineering, Marketing, Engineering, HR, Marketing

-- Unique departments only
SELECT DISTINCT department FROM employees;
-- Engineering, Marketing, HR

DISTINCT operates on the entire SELECT list, not on individual columns. SELECT DISTINCT department, salary returns unique (department, salary) pairs, not unique departments.

When DISTINCT Changes the Question

DISTINCT is often used to answer "what are the possible values?" — a list of categories, a set of segments, a collection of regions. It is not a general-purpose deduplication tool for analytical queries.

-- How many distinct customers placed orders?
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

COUNT(DISTINCT column) is the most common analytical use: it counts unique values while ignoring duplicates. This is different from COUNT(*), which counts rows.

DISTINCT and NULL

DISTINCT treats NULL as a value. If a column contains NULLs, DISTINCT collapses them into a single NULL row.

SELECT DISTINCT manager_id FROM employees;
-- If 12 employees have no manager, the result has one NULL row, not twelve

This behavior is usually what you want, but it matters when you are counting. COUNT(DISTINCT manager_id) excludes NULLs by default, so it counts only employees who have a manager.

DISTINCT vs GROUP BY

SELECT DISTINCT department FROM employees and SELECT department FROM employees GROUP BY department return the same rows. They are not interchangeable in general.

GROUP BY exists to support aggregation. When you need a unique list plus a count per group, GROUP BY is the right tool.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Use DISTINCT for simple deduplication. Use GROUP BY when you need aggregates. Using DISTINCT as a substitute for GROUP BY is a sign the query is solving the wrong problem.

Performance Considerations

DISTINCT requires the database to sort or hash the result set to find duplicates. On large tables with many columns, this is expensive. Before adding DISTINCT, ask whether duplicates are actually possible — if the query already returns one row per entity (one row per order, one row per customer), DISTINCT is unnecessary overhead.