SELECT Basics: Columns, SELECT * and Aliases

Concept

SELECT reads data. It never changes anything, and it is the only SQL statement most applications run in bulk. A SELECT answers one question: which columns, from which rows, of which tables.

Syntax

SELECT column1, column2
FROM   table_name;

SELECT column1 AS alias_name
FROM   table_name AS table_alias;

Example

-- Named columns, in the order you want them
SELECT first_name, last_name, salary
FROM   employees;

-- Aliases rename columns in the result only
SELECT first_name AS given_name,
       last_name  AS family_name,
       salary     AS monthly_salary
FROM   employees;

-- A table alias shortens every reference below it
SELECT e.first_name, e.salary
FROM   employees AS e;

-- An alias with a space or mixed case needs quoting
SELECT first_name AS "Given Name"
FROM   employees;

Explanation

An alias exists only for the duration of the query. It does not rename the column in the table. It matters more than it looks: the alias is the column name your application code, report tool or CSV export will see, so SELECT salary * 12 gives a result column with an unpredictable machine generated name, while SELECT salary * 12 AS annual_salary gives it a contract.

SELECT * and why to avoid it in code

SELECT * FROM employees;              -- fine while exploring
SELECT e.* FROM employees e JOIN departments d ON d.id = e.dept_id;  -- one table's columns

SELECT * is excellent at a console and a liability inside an application:

• It transfers columns nobody reads, including large TEXT columns.
• The result shape changes silently whenever someone adds or reorders a column.
• It defeats covering indexes: an index that contains every column a query needs can answer that query without touching the table, and * almost guarantees it cannot.
• With a join, two tables having an id column produces two columns called id.

Important rules

• The select list can contain columns, literals, expressions and function calls.
• AS is optional for aliases in most dialects, but SELECT salary annual reads like a typo. Write AS.
• Once you give a table an alias, you must use the alias, not the original name, everywhere else in the query.
• Column aliases are not visible in WHERE, GROUP BY or HAVING in standard SQL, because those clauses run before SELECT. MySQL permits it in GROUP BY and HAVING as an extension; PostgreSQL and SQL Server do not.
• A SELECT with no FROM is legal in MySQL, PostgreSQL and SQL Server (SELECT 1 + 1). Oracle needs FROM dual.

Common mistakes

• Shipping SELECT * in application queries and then wondering why adding a column broke a report.
• Aliasing a table and then still writing the full table name in the WHERE clause.
• Forgetting that an alias containing a space needs double quotes (or backticks in MySQL).
• Assuming rows come back in insert order. Without ORDER BY, no order is promised.

Best practices

• List columns explicitly in anything that is saved, deployed or scheduled.
• Give every table a short, meaningful alias (e, d, o) and qualify every column with it.
• Alias every computed column, and name it the way the consumer should see it.
• Use lower case snake_case aliases so no dialect needs to quote them.

Practice

1. Return each employee's full name as a single column called full_name, plus their salary.
2. Rewrite SELECT * FROM orders as an explicit column list, then say which columns a "recent orders" screen actually needs.
3. Why does SELECT e.*, d.* FROM employees e JOIN departments d ON d.id = e.dept_id produce two columns called id and name?