DISTINCT and Aliases

As you query databases, you’ll frequently encounter data that contains duplicate values or columns with names that don’t look great on a report. SQL gives us two powerful tools to clean up our output: DISTINCT and Aliases (AS).

Using DISTINCT to Remove Duplicates

Let’s say you want to know which countries your users come from. If you run a standard SELECT country FROM users;, you’ll get a list of every single user’s country. If 5,000 users are from the USA, “USA” will appear 5,000 times in your results!

To see only the unique values, you add the DISTINCT keyword immediately after SELECT.

SELECT DISTINCT country FROM users;

This tells the database to look at the country column, discard any duplicate entries, and return a clean, deduplicated list. You can also use DISTINCT across multiple columns. SELECT DISTINCT country, city FROM users; would return every unique combination of country and city.

Renaming Columns with Aliases

Sometimes the names of columns in a database aren’t very user-friendly. For example, a column might be named usr_cntry_cd (User Country Code). If you’re exporting this data for a presentation, you don’t want that ugly header.

You can temporarily rename a column in your result set using an Alias. This is done with the AS keyword.

SELECT country AS origin_country FROM users;

[!NOTE] Aliases only change how the column is displayed in the output of your specific query. They do not actually rename the column in the underlying database table.

If you want your alias to include spaces, you must wrap it in quotes: SELECT country AS "Origin Country" FROM users;.

Combining the Two

You can absolutely use DISTINCT and AS in the same query. It’s time to put both of these concepts to the test in the interactive lab on the right!