SQL Distinct

When you’re working with a database, there may be times when you want to get all of the unique values in a particular dataset. For example, you may want to get a list of the names of products you have sold in the past, or you may want to get a list of all the different categories of movies that exist in a movie database.

SQL has a built-in function that can help you retrieve this information: DISTINCT. The DISTINCT operator allows you to remove duplicate records so you can retrieve all of the unique combinations of values in a dataset.

In this tutorial, we’ll break down the basics of DISTINCT in SQL, and when you may use this operator.

SQL Query Refresher

To retrieve information from a database, you have to write a query. SQL queries almost always begin with the SELECT statement, and they specify what information you want to retrieve from a database. Here is the basic syntax for an SQL query:

SELECT column_name FROM table_name WHERE your_query_conditions;

Let’s use an example to illustrate queries in action. The following query will return the names and titles of all employees who work for our business:

SELECT name, title FROM employees;

Our code returns:

If you want to retrieve multiple columns, you can do so by separating the column names with a comma as we did above. Or if you want to get information from every column, you can use an asterisk (*), which represents all the columns in a table.

When we’re writing a query, we can use WHERE to filter records that meet a specific set of conditions. For example, if we want to get all the sales associates that work for our business, we could use the following query:

SELECT name, title FROM employees
WHERE title = 'Sales Associate':

Our query returns the following:

(4 rows)

Now we know the basics of queries, we can explore how to use DISTINCT in SQL.

SQL Distinct

When you’re gathering data from a database, the result may contain duplicate rows or values. Let’s say we want to get a list of all the titles held by our employees. If we were to use a standard SELECT query, we would get duplicate values. Here’s an example:

SELECT title FROM employees;

Our query returns the following result:

(7 rows)

As you can see, the title Sales Associate appears four times, because there are four employees who hold that title. But what if we only want to know what titles are in the database, rather than how many people have that title?

That’s where the SQL DISTINCT operator comes in. By using the SQL DISTINCT clause, we can remove all the duplicate data from a query. 

The DISTINCT keyword is used in conjunction with the SELECT operator. Here’s an example of running the same query from above, but with the DISTINCT operator present:

SELECT DISTINCT title FROM employees;

Our query returns the following:

(4 rows)

Our query returned a list of all the titles that employees hold. However, it didn’t repeat any titles held by more than one employee. We have no duplicate values in our output.

The DISTINCT operator is particularly useful when you’re working with large datasets. In our example above, we only have seven employees, but if we had five hundred, it would be difficult for us to see what titles people held without using the DISTINCT operator.

Conclusion

Now you know how to use the DISTINCT operator on an SQL server.

As we have discussed, SQL SELECT DISTINCT allows you to fetch data from a table and remove any duplicate rows or values from the result. For example, if you want a list of all a company’s branches, you could use DISTINCT to produce a list. DISTINCT is especially useful when you’re working with larger datasets where duplicates may be distracting.