Querying
DISTINCT and Duplicates
The DISTINCT keyword in SQL eliminates duplicate rows from the result set of a SELECT statement.
Overview
The DISTINCT keyword in SQL eliminates duplicate rows from the result set of a SELECT statement. It is part of the SQL-86 standard and remains a fundamental tool for data deduplication. When you specify SELECT DISTINCT, the database evaluates the combination of all columns in the select list for each row and returns only one row for each unique combination. This is different from filtering rows with WHERE, which operates on individual rows based on conditions. DISTINCT operates on the entire row after projection, meaning that two rows are considered duplicates only if all selected columns have equal values. For example, SELECT DISTINCT city FROM customers returns each city once, regardless of how many customers reside there. DISTINCT can be applied to multiple columns: SELECT DISTINCT city, state FROM customers returns each unique city-state pair. It is important to note that DISTINCT is not a function; it applies to all columns in the SELECT list. The keyword is often used in aggregate queries to count unique values, such as SELECT COUNT(DISTINCT customer_id) FROM orders. While DISTINCT is simple to use, it can have performance implications because it requires sorting or hashing the result set to identify duplicates. Understanding when and how to use DISTINCT effectively is essential for writing correct and efficient SQL queries.
Syntax and usage
The syntax for DISTINCT is straightforward: it appears immediately after the SELECT keyword and before the select list. The general form is SELECT DISTINCT column1, column2, ... FROM table_name WHERE condition. DISTINCT applies to the entire row of selected columns, not just the first column. For example, SELECT DISTINCT a, b FROM t returns unique combinations of a and b. If you want to deduplicate based on a subset of columns while still returning other columns, you need to use GROUP BY or a subquery, because DISTINCT always considers all selected columns. DISTINCT can also be used inside aggregate functions: COUNT(DISTINCT column) counts the number of unique non-null values in that column. This is standard SQL and supported by all major databases. Some vendors allow DISTINCT ON (column) (PostgreSQL) to deduplicate based on specific columns while returning others, but this is non-standard. In standard SQL, DISTINCT is also used with set operators like UNION DISTINCT (the default for UNION) to remove duplicates from the combined result. The keyword is not allowed in the middle of an expression; it must directly follow SELECT. It can be combined with ORDER BY, but the ORDER BY columns must appear in the select list when DISTINCT is used, to avoid ambiguity. Overall, DISTINCT is a simple clause with clear semantics, but its interaction with other clauses requires attention.
How it works
Conceptually, DISTINCT operates after the FROM and WHERE clauses have produced a set of rows, and after any grouping or aggregation (if present). The database engine takes the projected columns from each row and compares them to identify duplicates. Two rows are considered duplicates if all corresponding column values are equal, with NULLs treated as equal for the purpose of DISTINCT (i.e., two NULLs are considered the same). The implementation typically involves sorting the rows or building a hash table to group identical rows, then returning one row per group. This process can be memory-intensive for large result sets. The order of rows in the output is not guaranteed unless an ORDER BY clause is specified. DISTINCT does not affect the order of rows; it only removes duplicates. In terms of query execution, DISTINCT is applied after SELECT but before ORDER BY and LIMIT. This means that if you use DISTINCT with ORDER BY, the sort is applied to the deduplicated rows. Some optimizers can push DISTINCT down to avoid processing unnecessary rows, but this is not guaranteed. When DISTINCT is used with aggregate functions like COUNT, the aggregation is performed on the distinct values, which may require a separate pass. Understanding the logical order of operations helps in writing correct queries: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
Examples
Consider a table orders with columns order_id, customer_id, order_date, and amount. To get a list of unique customers who have placed orders, you can write: SELECT DISTINCT customer_id FROM orders. This returns each customer_id once, even if they have multiple orders. If you want to count the number of unique customers, use: SELECT COUNT(DISTINCT customer_id) FROM orders. For multiple columns, suppose you want unique combinations of customer_id and order_date: SELECT DISTINCT customer_id, order_date FROM orders. This returns each pair once. Note that if you include order_id, which is unique, DISTINCT will have no effect because every row is already distinct. Another example: SELECT DISTINCT city, state FROM customers ORDER BY state, city. This returns each unique city-state pair, sorted. If you want to deduplicate based on a subset of columns but still return other columns, you might use a subquery or window function. For instance, in PostgreSQL you could use SELECT DISTINCT ON (customer_id) customer_id, order_date FROM orders ORDER BY customer_id, order_date DESC to get the most recent order per customer. In standard SQL, you can achieve similar results with a correlated subquery or a join to a derived table. Always test your queries to ensure they return the expected uniqueness.
Common pitfalls
One common mistake is assuming that DISTINCT applies only to the first column in the select list. In reality, it applies to all columns, so SELECT DISTINCT a, b FROM t returns unique combinations of a and b, not unique a values with arbitrary b. Another pitfall is using DISTINCT unnecessarily, which can add overhead. If the underlying data is already unique (e.g., selecting a primary key), DISTINCT has no effect but may still cause a sort or hash operation. Also, DISTINCT can mask data quality issues: if you expect unique rows but get duplicates, DISTINCT hides the problem instead of revealing it. When used with ORDER BY, the ORDER BY expressions must appear in the select list; otherwise, the query is invalid in standard SQL because the order of rows is ambiguous. Some databases allow it but with unpredictable results. Another issue is that DISTINCT treats NULLs as equal, so multiple NULLs are collapsed into one. This is standard behavior but can be surprising. Finally, DISTINCT can be inefficient on large datasets because it requires comparing all rows. In such cases, consider whether GROUP BY or a different query structure might be more efficient. For example, SELECT customer_id FROM orders GROUP BY customer_id may perform similarly but allows additional aggregations.
Best practices
Use DISTINCT only when you need to eliminate duplicate rows based on the selected columns. Before adding DISTINCT, check whether the query already returns unique rows due to primary keys or unique constraints. If you need to deduplicate based on a subset of columns, consider using GROUP BY or window functions instead, as they provide more control. When using DISTINCT with multiple columns, ensure that you understand the combination that defines uniqueness. Avoid using DISTINCT as a quick fix for missing joins or incorrect WHERE clauses; it can hide logical errors. For counting unique values, use COUNT(DISTINCT column) rather than selecting DISTINCT and counting in the application, as it is more efficient. Be mindful of performance: DISTINCT may require sorting or hashing, so if the result set is large, consider adding appropriate indexes or rewriting the query. When combining DISTINCT with ORDER BY, include all ORDER BY columns in the select list to comply with standard SQL. In set operations, remember that UNION removes duplicates by default; use UNION ALL if you want to keep duplicates. Finally, document the purpose of DISTINCT in complex queries to clarify intent for future maintainers.
Frequently asked questions
Q: What is the difference between DISTINCT and GROUP BY? A: Both can remove duplicates, but GROUP BY is used for aggregation and allows aggregate functions in the select list. DISTINCT simply deduplicates the result set without aggregation. In many databases, they can produce the same result for simple deduplication, but GROUP BY offers more flexibility. Q: Does DISTINCT apply to all columns or just the first? A: DISTINCT applies to the entire row of selected columns. Two rows are considered duplicates only if all selected columns are equal. If you want to deduplicate based on a subset, you need a different approach. Q: Can I use DISTINCT with multiple columns and an ORDER BY? A: Yes, but the ORDER BY columns must appear in the select list. For example, SELECT DISTINCT a, b FROM t ORDER BY a, b is valid. If you order by a column not in the select list, the query may be rejected or produce unpredictable results. Always include the ordering columns in the select list when using DISTINCT.