When working with large datasets in MySQL, it is common to encounter duplicate rows. For various analyses and reports, it can be helpful to get a list of only the unique rows from a table.  

This article will explore how to use the DISTINCT keyword in MySQL to effectively return unique data in your SQL pulls.  

Basic Syntax of DISTINCT

The DISTINCT keyword is used in a select statement before the target column name. This code example will pull only the unique departments from the dataset, even if there are multiple rows with the same value.  

SELECT DISTINCT department
FROM store_information;

Unique Combinations of Columns

You can also use DISTINCT on multiple columns to get unique combinations of values. For example, you can retrieve the unique combinations of departments and managers. If there are multiple rows with teh same department and manager, such as for different quarters of data, only one combination will be shown in the query result.  

SELECT DISTINCT department, manager
FROM store_information;

Including three or more columns will continue to output all the possible unique combinations.  

Counting Distinct Rows

Another feature of DISTINCT is that it can be used in combination with COUNT to return just the number of unique rows in the dataset for that variable. The column name needs to be placed in parenthesis after DISTINCT for COUNT to work correctly. For example, to get just a count of the number of distinct departments, you can run the following:  

SELECT COUNT(DISTINCT(department))
FROM store_information;

Combining with Other Statements

You can combine your DISTINCT query with other statements to further refine your SQL data pull. For example, if you only want the unique departments where quarterly sales were above $5,000, you can include a WHERE statement in your MySQL query.  

SELECT DISTINCT department
FROM store_information
WHERE sales > 5000;

Best Practices and Troubleshooting

• DISTINCT will treat any null values as a unique value, so if your column has null entries, these will appear as unique rows in your output. You can filter them out using a WHERE clause if you do not want them in your query results.
• Depending on your settings, MySQL may be case sensitive when searching for unique values. To avoid this, you can surround the column name with LOWER() or UPPER() in your query.
• Consider if you should use GROUP BY instead of DISTINCT. For example, GROUP BY is a better option when aggregating data, such as calculating the mean or counting the number of rows within each category.

Summary

The DISTINCT keyword in MySQL is a valuable tool for eliminating duplicate rows in your data pulls. Understanding how it works is critical to ensuring your queries work as expected to ensure data accuracy.