DISTINCT and aliases

Remove duplicate rows, and give columns and tables short, clear names.

DISTINCT: unique rows only

SELECT DISTINCT industry
FROM accounts;

That returns each industry once, however many accounts share it.

DISTINCT looks at every column you select, not just the first:

SELECT DISTINCT industry, region
FROM accounts;   -- one row per industry AND region combination

Common mistake: reaching for DISTINCT to hide duplicate rows you didn’t expect. Duplicates almost always mean the query is wrong somewhere, usually in a join (lesson 14). Find the cause instead of covering it up.

Column aliases

Lesson 1 showed AS renaming a column. Aliases matter most on calculated columns, which otherwise get an unhelpful name:

SELECT name,
       amount * 1.03 AS amount_after_increase
FROM opportunities;

The alias is the column name Excel, Power BI or the next query will see. Use lower_case_with_underscores and avoid spaces.

Table aliases

A table alias is a nickname you give a table in FROM, then use for the rest of the query:

SELECT o.name, o.amount
FROM opportunities AS o;

FROM opportunities AS o means “call opportunities o from now on”, so o.amount is the amount column from opportunities. With one table it saves little. Once you join two tables that share column names, like account_id, the prefix is required.

AS is optional on most databases. FROM opportunities o means exactly the same thing, and you’ll see it often in other people’s code. This track always writes AS because it’s clearer.

Try it

List each unique combination of stage and lead_source in opportunities, sorted by stage.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson