Use the SQL DISTINCT clause to return a unique list of values from a SQL SELECT statement. With DISTINCT, you elect to return unique values based on a combination of one or more columns.
SQL DISTINCT Example
The DISTINCT clause is used with the SELECT statement. It is placed immediately after SELECT and before the columns you wish to select. Here is a general form for the command:
SELECT DISTINCT column1, column2, ...
You can specify as many columns as you want, but as you’ll see, most time you’ll use just a couple of columns.
Let’s try an example. To get a unique list of cities that have had a pro base ball park you can write:
SELECT DISTINCT city FROM parks;
Click Run Query to try it!
Here is the same query without DISTINCT…
SELECT city FROM parks;
Notice how the cities Altoona, Atlanta, and Baltimore are repeated. In this query every city for the parks listed is retrieved!
As you saw from the beginning of our article, you can use DISTINCT with several columns. Doing so, instructs SQL to return the various unique column combination found.
For example, here’s is how we can find a unique list of State and Cities.
SELECT DISTINCT city, state FROM parks;
Now You Try It!
Let’s find all the unique countries and cities that player or managers were born. To do this you can use the people table, shown below.
Using the space below, write a query to get a unique list of their birth countries and cities:
-- Answer SELECT DISTINCT birthcountry, birthcity FROM people;
Practical uses of SQL SELECT DISTINCT
I like to use DISTINCT when I’m exploring a new data set. It makes it easy to see if there are any variation or misspelling to look out.
I use this as part of my three steps to writing a query. Check out these steps I take to write complex queries.
In addition, if you have some raw data, and you’re looking to create reference or “lookup” tables, then using a SQL SELECT DISTINCT with queries is a great way to get the data you’ll insert into those tables.
About the Sample Data
Note: This articles uses Lahmans’ Baseball Database. It is a wonderful compilation of batting and pitching statistics from 1871 to 2018! Whether you’re a baseball fan or not, you will find the data interesting for great SQL queries. Read this documentation to learn more about the table and where to get the database for your own use.