Right, let’s talk about the part of the SELECT statement that feels the most like creative writing: the column list. This is where you tell the database exactly what you want to see, and it’s far more powerful than just listing a few column names. You can rename things on the fly, perform calculations, and even create entirely new data from what’s stored. It’s the projection of your data universe.

The most basic form is the laundry list: SELECT column1, column2, .... But slapping a * in there is the equivalent of saying “I’ll have one of everything.” It’s fast, easy, and often what you start with to see what you’re working with. But using SELECT * in production code is like shouting your social security number in a coffee shop—it works, but it’s a terrible, reckless habit. You’re asking for every single column, which is wasteful over the network and a ticking time bomb if someone adds a 4GB BLOB column to that table later. Always specify the columns you need. Your future self (and your DBA) will thank you.

Expression Columns: Your Data, Transformed

This is where the magic happens. You’re not limited to just the raw data in the columns; you can manipulate it right in the query. Need to calculate a total? Concatenate a first and last name? Adjust a price for inflation? Do it here.

SELECT
    product_name,
    unit_price,
    units_in_stock,
    unit_price * units_in_stock AS potential_revenue
FROM products;

See that unit_price * units_in_stock? That’s an expression column. The database calculates that value for every single row on the fly. You can use all the standard arithmetic operators (+, -, *, /), string functions (CONCAT(), UPPER()), date functions, and more. It’s a full-fledged calculator and text editor built into your query.

Aliases: Or, How to Make Your Output Less Ugly

Look at the output of the previous query. That fourth column probably has a nightmare name like potential_revenue or, even worse, something generated by the database like ?column?. This is where aliases come in. You use the AS keyword to give a column (or a table) a temporary name for the duration of the result set. It doesn’t change the underlying table; it just makes the output readable.

SELECT
    first_name || ' ' || last_name AS full_name, -- PostgreSQL string concat
    title,
    hire_date,
    EXTRACT(YEAR FROM AGE(hire_date)) AS years_with_company -- More PG magic
FROM employees;

The AS keyword is actually optional in most SQL dialects. You could just write full_name, but including it is a best practice. It makes your intent crystal clear and separates the expression from the alias, which is critical when the expression is complex. Omitting AS is like removing the Oxford comma—it might work most of the time, but eventually it will cause a confusing disaster.

The Subtle Pitfalls of Aliases

Here’s the first major “gotcha”: you cannot use a column alias in the WHERE clause. The order of execution in a SQL query is weird. The database needs to figure out which rows to get (FROM + WHERE) before it figures out what to call those rows and how to calculate your fancy expressions (SELECT). So this will fail spectacularly:

SELECT
    unit_price * quantity AS total_cost
FROM order_details
WHERE total_cost > 100; -- NOPE. The database has no idea what 'total_cost' is here.

You have to repeat the expression in the WHERE clause:

SELECT
    unit_price * quantity AS total_cost
FROM order_details
WHERE unit_price * quantity > 100; -- This is the correct, albeit annoying, way.

Some databases, like MySQL, might let you get away with this sin in a GROUP BY due to their… let’s call it “flexible” interpretation of the standard, but don’t rely on it. Be explicit. Be safe.

DISTINCT: The Great Deduplicator

Sometimes you just want to know the unique values. DISTINCT is your tool for this. Plop it right after SELECT and it will remove duplicate rows from your entire result set.

-- How many different countries are our customers in?
SELECT DISTINCT country
FROM customers
ORDER BY country;

Crucially, DISTINCT operates on the entire row, not just the first column. It checks the combination of all selected columns for uniqueness.

-- Gets unique combinations of city and country
SELECT DISTINCT city, country
FROM customers;

A word of caution: DISTINCT is often used as a band-aid for a poorly written join that creates duplicates. If you find yourself slapping DISTINCT on a query “just to be safe,” stop and figure out why you’re getting duplicates in the first place. You’re likely masking a deeper problem in your logic. Also, it’s a relatively expensive operation; the database has to sort and compare every single result row. Use it, but use it intentionally.