Reading Data with SELECT and Column Expressions

What SELECT Does (and What It Doesn’t)

The SELECT statement is the main tool for reading data from a database. It returns a result set: a table-like output made of rows and columns. You can think of it as asking the database to “project” certain columns and compute certain expressions for each row.

In this chapter, the focus is on two core skills: choosing what columns to return, and creating column expressions (computed columns) in the SELECT list. Filtering (WHERE), grouping (GROUP BY), and joining tables are important topics, but here we’ll keep attention on reading and shaping columns.

Basic SELECT Shape

A typical query looks like this:

SELECT column_or_expression, column_or_expression, ... FROM table_name;

The FROM clause tells the database where to read rows from. The SELECT list tells it which columns to return and what expressions to compute for each row.

Selecting Columns

Select All Columns with *

The simplest form returns every column from a table:

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

SELECT * FROM customers;

This is convenient for quick exploration, but it’s usually better to explicitly list columns in real work. Explicit columns make queries easier to read, reduce unnecessary data transfer, and protect you from unexpected schema changes (for example, a new column appearing and breaking an application that assumed a certain column order).

Select Specific Columns

To return only certain columns, list them separated by commas:

SELECT customer_id, first_name, last_name FROM customers;

The result set will contain only those three columns, in that order.

Control Column Order

The order of items in the SELECT list is the order of columns in the output:

SELECT last_name, first_name, customer_id FROM customers;

This doesn’t change the underlying table—only the output layout.

Column Expressions: Creating Computed Columns

A column expression is any calculation or transformation you place in the SELECT list. The database evaluates it for each row and returns the computed value as a column in the result.

Expressions can include:

• Arithmetic (add, subtract, multiply, divide)
• String concatenation and formatting
• Date/time calculations
• Conditional logic (e.g., CASE)
• Functions (numeric, string, date, conversion)
• Handling nulls

Arithmetic Expressions

Suppose you have an order_items table with quantity and unit_price. You can compute a line total:

SELECT order_id, product_id, quantity, unit_price, quantity * unit_price AS line_total FROM order_items;

Here, quantity * unit_price is an expression. The alias line_total names the output column.

You can combine multiple operations:

SELECT order_id, product_id, quantity, unit_price, (quantity * unit_price) * 1.07 AS line_total_with_tax FROM order_items;

Parentheses make the intended order of operations obvious. Even when operator precedence would work, parentheses improve readability.

String Expressions (Concatenation)

Often you want to build a display-friendly value, like a full name. Concatenation syntax differs across databases:

• Many databases support || (e.g., PostgreSQL, SQLite, Oracle)
• SQL Server uses + for strings
• MySQL typically uses CONCAT(a, b, ...)

To keep examples broadly understandable, we’ll show both a common operator form and a function form. Use the one your database supports.

-- Operator style (common in many systems) SELECT customer_id, first_name || ' ' || last_name AS full_name FROM customers;

-- Function style (common in MySQL and also available in others) SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

Concatenation is a classic example of an expression: it doesn’t exist as a stored column, but it’s computed in the result set.

Be Careful with NULL in Expressions

NULL represents “missing/unknown.” Expressions involving NULL can produce NULL results. For example, if last_name is NULL, then concatenating it may yield NULL (behavior varies by database and operator).

To protect against nulls, use a null-handling function. Common options include COALESCE (standard SQL) and database-specific functions like IFNULL (MySQL/SQLite) or ISNULL (SQL Server).

SELECT customer_id, CONCAT(first_name, ' ', COALESCE(last_name, '')) AS full_name FROM customers;

COALESCE(last_name, '') means: “use last_name if it’s not null; otherwise use an empty string.”

Aliasing: Naming Output Columns (and Tables)

An alias is a temporary name for a column expression (or a table) in the query output. Aliases make results easier to read and are essential when you compute expressions.

Column Aliases with AS

Use AS to label an output column:

SELECT quantity * unit_price AS line_total FROM order_items;

In many databases, AS is optional:

SELECT quantity * unit_price line_total FROM order_items;

Using AS is clearer, especially for beginners.

Aliases with Spaces (Quoted Identifiers)

If you want an alias with spaces, you typically need quotes. The exact quoting character varies:

• Standard SQL uses double quotes: "Line Total"
• MySQL often uses backticks: `Line Total` (depending on settings)
• SQL Server uses brackets: [Line Total]

To stay portable, prefer simple aliases like line_total.

Table Aliases (Useful Even Without Joins)

Even when selecting from one table, a table alias can make long column names easier to read:

SELECT c.customer_id, c.first_name, c.last_name FROM customers AS c;

This becomes especially helpful later when multiple tables have columns with the same name (like id or created_at).

Step-by-Step: Build a Useful SELECT with Expressions

Let’s build a query in small steps. Imagine a products table with columns like product_id, product_name, and unit_price.

Step 1: Start with a Simple Column List

SELECT product_id, product_name, unit_price FROM products;

This confirms the basic data you have and the column names you’ll use.

Step 2: Add a Computed Column

Compute a discounted price (say 15% off):

SELECT product_id, product_name, unit_price, unit_price * 0.85 AS discounted_price FROM products;

Now each row includes both the original and discounted price.

Step 3: Format or Round Values

Many databases store prices as decimals. When you compute a discount, you may get many decimal places. Use a rounding function. The function name is commonly ROUND(value, decimals):

SELECT product_id, product_name, unit_price, ROUND(unit_price * 0.85, 2) AS discounted_price FROM products;

If your database uses a different rounding function, look up its equivalent, but the idea is the same: compute first, then format for display.

Step 4: Add a Label-Like Expression

Create a display label combining name and price. Again, concatenation varies by database:

SELECT product_id, CONCAT(product_name, ' - $', unit_price) AS display_label FROM products;

This is not meant for calculations—just for readable output in reports or quick checks.

Using Functions in the SELECT List

Functions let you transform values. Here are common categories you’ll use frequently.

String Functions

Typical string functions include:

• UPPER(text) and LOWER(text) to change case
• TRIM(text) to remove leading/trailing spaces
• LENGTH(text) or CHAR_LENGTH(text) to measure text length (name varies)
• SUBSTRING(text, start, length) (name and indexing rules vary)

Examples:

SELECT customer_id, UPPER(last_name) AS last_name_upper FROM customers;

SELECT product_id, TRIM(product_name) AS clean_name FROM products;

These are computed in the result; they do not modify stored data.

Numeric Functions

Common numeric functions include ROUND, CEILING/CEIL, FLOOR, and ABS. Example:

SELECT order_id, ROUND(total_amount, 0) AS total_rounded FROM orders;

Date/Time Expressions

Date/time functions vary significantly across databases, but the concept is consistent: you can extract parts of a date, compute intervals, or format output.

Examples of common intentions:

• Extract the year from an order date
• Compute how many days since an event
• Convert a timestamp to a date

Because syntax differs, focus on the pattern: apply a function to a date column and alias the result.

-- Example intent: extract year (function name varies by database) SELECT order_id, EXTRACT(YEAR FROM order_date) AS order_year FROM orders;

If your database doesn’t support EXTRACT, it likely has an equivalent (for example, YEAR(order_date) in some systems).

CASE Expressions: Conditional Output Columns

The CASE expression lets you create conditional logic inside a SELECT. This is extremely useful for creating categories, flags, and human-friendly labels.

Simple CASE Pattern

SELECT product_id, product_name, unit_price, CASE WHEN unit_price < 20 THEN 'budget' WHEN unit_price < 100 THEN 'standard' ELSE 'premium' END AS price_category FROM products;

For each row, the database checks conditions in order and returns the first matching result. If none match, it uses ELSE. If you omit ELSE and nothing matches, the result is NULL.

CASE for Data Quality Flags

You can use CASE to highlight missing or suspicious values:

SELECT customer_id, email, CASE WHEN email IS NULL THEN 'missing_email' WHEN email NOT LIKE '%@%' THEN 'invalid_email' ELSE 'ok' END AS email_status FROM customers;

This doesn’t fix the data; it helps you see which rows need attention.

Distinct Values: Removing Duplicates in Output

Sometimes you want unique values from a column. Use DISTINCT to remove duplicate rows from the result set.

SELECT DISTINCT country FROM customers;

DISTINCT applies to the entire row returned by the SELECT list. If you select multiple columns, the combination must be unique:

SELECT DISTINCT country, city FROM customers;

If two customers share the same country and city, that pair appears once.

Working with Literals in SELECT

You can include constant values (literals) in the SELECT list. This is useful for labeling outputs, building templates, or creating computed fields that mix constants and columns.

SELECT customer_id, 'ACTIVE' AS status_label FROM customers;

Every row will show ACTIVE in status_label.

You can combine literals with columns:

SELECT order_id, total_amount, total_amount + 5 AS total_plus_fee FROM orders;

Common Pitfalls and How to Avoid Them

Pitfall 1: Using * in Production Queries

SELECT * can return more data than you need and can break assumptions if the table changes. Prefer explicit columns:

SELECT customer_id, first_name, last_name FROM customers;

Pitfall 2: Forgetting Parentheses in Calculations

Operator precedence can surprise you. If you intend “price times quantity, then add shipping,” write it explicitly:

SELECT order_id, (quantity * unit_price) + shipping_fee AS total_with_shipping FROM order_items;

Pitfall 3: Nulls Breaking Expressions

If a column can be null, protect expressions with COALESCE:

SELECT customer_id, COALESCE(phone, 'no phone') AS phone_display FROM customers;

Pitfall 4: Confusing Display Formatting with Data Types

It’s tempting to concatenate currency symbols into numeric values (e.g., '$' || price). That’s fine for display, but it turns the result into text, which is harder to sort numerically or use in further calculations. A good habit is to keep numeric outputs numeric, and apply formatting only when you truly need a display string.

Practice: Write SELECT Queries with Expressions

Try these exercises using your own sample tables (or adapt the column names to match your database). The goal is to practice shaping output with expressions and aliases.

Exercise 1: Build a Full Name Column

Return customer id and a computed full name. Handle missing last names.

SELECT customer_id, CONCAT(first_name, ' ', COALESCE(last_name, '')) AS full_name FROM customers;

Exercise 2: Compute Line Totals

Return each order item with a computed line total and a rounded version.

SELECT order_id, product_id, quantity, unit_price, quantity * unit_price AS line_total, ROUND(quantity * unit_price, 2) AS line_total_rounded FROM order_items;

Exercise 3: Categorize Products by Price

Create a price_category column using CASE.

SELECT product_id, product_name, unit_price, CASE WHEN unit_price < 20 THEN 'budget' WHEN unit_price < 100 THEN 'standard' ELSE 'premium' END AS price_category FROM products;

Exercise 4: Get Unique Locations

List unique country/city pairs from customers.

SELECT DISTINCT country, city FROM customers;

Reading SELECT Output Like a Pro

When you run a SELECT query with expressions, train yourself to read the output as a deliberately designed view:

• Which columns are original table columns, and which are computed?
• Are computed columns named clearly with aliases?
• Are numeric outputs still numeric (unless intentionally formatted as text)?
• Are nulls handled so the output is stable and readable?
• Does the column order match how you want to consume the results?

These habits make your queries easier to maintain and prepare you for more advanced querying where expressions become even more important.