Aggregation
DISTINCT — Removing Duplicates
The DISTINCT clause is a foundational SQL operator used to filter out redundant rows from result sets to ensure data uniqueness. By instructing the database engine to perform a sorting or hashing operation on requested columns, it allows analysts to generate precise lists of distinct entities. You should reach for this tool whenever your query returns repetitive entries that obscure the underlying count or variety of your dataset.
Understanding the Foundation of Uniqueness
At its core, the DISTINCT keyword functions as a projection operator that alters the shape of your result set by collapsing multiple identical rows into a single representative row. When you execute a SELECT statement, the database typically retrieves every row that satisfies your criteria. By prepending DISTINCT, you force the engine to inspect the entire set of returned columns and discard any duplicates based on that combined row definition. The mechanism relies on either sorting the result set to bring identical rows together for identification or utilizing a hash table to track seen combinations. Because this operation requires the database to maintain an internal record of what it has already encountered, it can be computationally intensive on very large tables. Understanding this underlying cost is crucial for writing efficient queries, as it reminds you that retrieving only the specific columns you need is always better than applying DISTINCT across a wide table.
-- Selecting unique job titles from the employees table
-- The engine evaluates each row, discarding duplicates to return only the list of individual roles.
SELECT DISTINCT job_title
FROM employees;Applying DISTINCT Across Multiple Columns
A common point of confusion arises when applying DISTINCT to multiple columns simultaneously. It is vital to recognize that the filter does not apply to each column independently; instead, it looks for uniqueness based on the entire tuple—the combination of all columns requested. If you select both 'city' and 'state', the database checks if the pair of values matches any previous pair it has already added to the result set. Consequently, you might see the same city name appearing multiple times if those cities exist in different states. This behavior is intentional because it preserves the integrity of the relationship between the values requested. When reasoning about this, imagine the engine building a composite key out of all selected columns for every row; only rows that produce a truly unique key value are allowed to pass through the filter into the final output.
-- Unique pairs of cities and states where customers reside.
-- Results represent distinct combinations, not unique city names alone.
SELECT DISTINCT city, state
FROM customers;Handling Null Values in Duplicate Removal
SQL treats NULL values with specific logic that often surprises beginners, especially within the context of duplicate removal. According to the SQL standard, if you compare two NULL values, they are considered equivalent for the purposes of the DISTINCT clause. If your dataset contains multiple rows where a column is NULL, the DISTINCT operator will collapse all of those NULLs into a single NULL entry in your final output. This behavior is consistent with the idea that DISTINCT is concerned with identifying distinct logical states within your data. Because NULL signifies the absence of a value, the database interprets the absence in one row as being the same as the absence in another. When designing queries that involve optional fields, keep this in mind, as it ensures that your report does not provide redundant NULL entries that could inflate your analysis or lead to incorrect conclusions during data processing.
-- DISTINCT treats all NULL values as a single entity.
-- Even if 100 rows have NULL phone numbers, only one NULL will appear in the result.
SELECT DISTINCT phone_number
FROM contacts;Optimizing DISTINCT with Aggregations
DISTINCT is frequently used inside aggregate functions to calculate the variety of data points rather than their total count. When you wrap a column name in COUNT(DISTINCT column_name), you are instructing the engine to filter out duplicates before the counting process begins. This is an essential technique for reporting metrics like the number of unique customers who placed an order on a given day. Without the DISTINCT keyword, you would simply be counting the number of order records, which would incorrectly report high volume if one customer placed multiple orders. The reasoning here is structural: the inner operation transforms the data by reducing its dimensionality through deduplication, and the outer operation summarizes that reduced set. By understanding this layered processing order, you can effectively leverage aggregation to extract sophisticated insights regarding user behavior, inventory variety, and categorical distribution within your relational database environment.
-- Counting unique customers who have made purchases.
-- This avoids overcounting customers with multiple orders.
SELECT COUNT(DISTINCT customer_id) AS total_unique_customers
FROM orders;Comparing DISTINCT to GROUP BY
In many scenarios, the functionality of DISTINCT overlaps with the GROUP BY clause, which also serves to eliminate duplicates by creating buckets for aggregate calculations. While you could technically use GROUP BY to achieve the same result as DISTINCT, they are semantically different tools intended for different phases of analysis. DISTINCT is a clean, declarative way to ask for unique rows when no mathematical summary is required. GROUP BY is intended for when you need to perform calculations—like sums or averages—on groups of rows. If you find yourself using GROUP BY on every column simply to remove duplicates, you are writing unnecessary overhead. Modern database engines are highly optimized for both, but using the correct keyword signals your intent to other developers. Always prefer DISTINCT for simple deduplication tasks to keep your code readable and aligned with its logical purpose within your data transformation pipelines.
-- Using GROUP BY to achieve the same result as DISTINCT.
-- Only use this if you intend to perform further calculations on the categories.
SELECT category_id
FROM products
GROUP BY category_id;Key points
- The DISTINCT clause serves to filter out identical rows from a result set by examining the uniqueness of the entire selected row.
- When selecting multiple columns, the database determines uniqueness based on the combined set of values across all selected columns.
- SQL treats all NULL values as equal to each other when filtering for distinct results, resulting in a single NULL entry.
- Using DISTINCT within an aggregate function like COUNT allows you to measure the cardinality or variety of a specific data column.
- Applying DISTINCT involves significant performance overhead because the engine must track every unique value encountered during execution.
- While GROUP BY can emulate DISTINCT, the latter is syntactically cleaner when performing simple deduplication without further aggregation.
- You should limit the number of columns included in a DISTINCT query to minimize the memory and processing requirements of the operation.
- The order of columns in your SELECT statement does not impact the behavior of the DISTINCT operator, as it evaluates the row as a whole.
Common mistakes
- Mistake: Expecting DISTINCT to return unique rows across the entire table when used with only one column. Why it's wrong: DISTINCT applies to the whole combination of selected columns, not just the one it is placed next to. Fix: To get unique values for a specific column, select only that column or use GROUP BY.
- Mistake: Using DISTINCT with an aggregate function inside the SELECT list without understanding scope. Why it's wrong: DISTINCT affects the entire result set, not just the specific column inside an aggregate (unless used as COUNT(DISTINCT column)). Fix: Be explicit about whether you want distinct rows or distinct values within a calculation.
- Mistake: Assuming DISTINCT ignores NULL values. Why it's wrong: DISTINCT treats all NULL values as identical, so it will return a single NULL in the result set if duplicates exist. Fix: If you need to exclude NULLs, explicitly add a WHERE clause filtering them out.
- Mistake: Placing DISTINCT after the column name instead of before. Why it's wrong: SQL syntax requires DISTINCT to be a modifier of the SELECT statement immediately following the keyword. Fix: Always place DISTINCT immediately after SELECT.
- Mistake: Confusing DISTINCT with GROUP BY. Why it's wrong: While they can produce similar results for unique values, GROUP BY is designed for aggregation, whereas DISTINCT is for de-duplication. Fix: Use DISTINCT for simple de-duplication and GROUP BY when you need to perform calculations on groups.
Interview questions
What is the primary purpose of the DISTINCT keyword in SQL, and when would a developer typically use it?
The DISTINCT keyword is used in a SELECT statement to remove duplicate rows from the result set, ensuring that only unique values are returned for the specified columns. Developers typically use it when they want to identify unique categories or entities within a dataset, such as finding every unique city where customers live, rather than seeing a list of every customer's city that includes many repetitions of the same location.
How does the DISTINCT keyword behave when you include multiple columns in your SELECT statement?
When you specify multiple columns with DISTINCT, SQL treats the combination of those values as a unique record. It does not look for uniqueness in each column independently. Instead, it only filters out rows where every column value matches another row exactly. For example, selecting DISTINCT city and state will return a unique pair of city and state, ensuring you don't get the same combination twice in the output.
What is the performance implication of using DISTINCT, and does it affect how the database engine handles the result set?
Using DISTINCT often incurs a performance cost because the database engine must perform an internal sort or use a hash aggregate operation to identify and discard duplicate rows. This requires additional memory and CPU cycles to compare row data across the entire result set. Because the engine must verify every row before returning results, large datasets can experience significant latency compared to queries that do not require deduplication.
Compare using the DISTINCT keyword versus using the GROUP BY clause to remove duplicates. When is one preferred over the other?
While both can be used to achieve distinct results, they serve different primary purposes. DISTINCT is cleaner when simply removing duplicates for a display. However, GROUP BY is preferred when you need to perform aggregate calculations like COUNT, SUM, or AVG alongside your unique values. In many modern SQL engines, the query optimizer produces the same execution plan for both, but GROUP BY is more extensible if reporting requirements grow.
If you are working with a table containing NULL values, how does the DISTINCT keyword handle these entries?
In SQL, the DISTINCT keyword treats all NULL values as identical to one another for the purpose of deduplication. This means if you have a column with multiple NULL entries, the result set will display only one NULL row. This is consistent with how many grouping and comparison operations function in SQL, where NULLs are not treated as distinct values but as a single category of missing or unknown data.
Can you explain the relationship between DISTINCT and the ORDER BY clause, and why you might be limited in how you sort results when using DISTINCT?
When using DISTINCT, you are restricted to sorting by the columns that appear in your SELECT list. This is because the database engine must eliminate duplicates before the final result set is finalized. If you try to sort by a column that is not part of the distinct set, the database cannot guarantee a unique mapping between that extra column and the resulting rows, which would cause an ambiguity that the engine cannot resolve logically.
Check yourself
1. If you run SELECT DISTINCT first_name, last_name FROM employees;, what determines if a row is considered a duplicate?
- A.Only the first_name must be unique.
- B.Only the last_name must be unique.
- C.The combination of both first_name and last_name must be unique.
- D.The entire row including hidden primary key columns must be unique.
Show answer
C. The combination of both first_name and last_name must be unique.
DISTINCT applies to the entire tuple of selected columns. Option 0 and 1 are wrong because the modifier is not restricted to one column. Option 3 is wrong because DISTINCT ignores columns not explicitly listed in the SELECT clause.
2. What is the result of SELECT DISTINCT department FROM staff; if there are three employees in the 'Sales' department and two in 'HR'?
- A.Sales, Sales, Sales, HR, HR
- B.Sales, HR
- C.Sales, HR, 5
- D.An error, because there are multiple values for each department.
Show answer
B. Sales, HR
DISTINCT removes all duplicate values from the result set. Option 0 is wrong as it doesn't remove duplicates. Option 2 and 3 are wrong because there is no aggregation occurring here.
3. How does SQL handle NULL values when the DISTINCT keyword is applied to a column containing them?
- A.It removes all NULL values from the result.
- B.It treats every NULL as a unique value, keeping all of them.
- C.It treats all NULL values as duplicates of each other and returns one NULL.
- D.It ignores the column entirely if it contains a NULL.
Show answer
C. It treats all NULL values as duplicates of each other and returns one NULL.
In SQL, NULLs are treated as duplicates of each other in the context of DISTINCT. Option 0 is wrong because they aren't removed, 1 is wrong because they are grouped, and 3 is wrong because the column is still processed.
4. Which of the following is functionally equivalent to SELECT DISTINCT status FROM orders;?
- A.SELECT status FROM orders GROUP BY status;
- B.SELECT status FROM orders ORDER BY status;
- C.SELECT status FROM orders HAVING COUNT(status) > 0;
- D.SELECT ALL status FROM orders;
Show answer
A. SELECT status FROM orders GROUP BY status;
GROUP BY status creates one row per unique status value, identical to the behavior of DISTINCT. Option 1 doesn't remove duplicates, 2 doesn't filter, and 3 is a filter that doesn't handle duplicates.
5. You want to know the number of unique cities where customers are located. Which query is correct?
- A.SELECT DISTINCT COUNT(city) FROM customers;
- B.SELECT COUNT(DISTINCT city) FROM customers;
- C.SELECT COUNT(*) FROM (DISTINCT city) FROM customers;
- D.SELECT DISTINCT (COUNT(city)) FROM customers;
Show answer
B. SELECT COUNT(DISTINCT city) FROM customers;
COUNT(DISTINCT column) is the standard syntax for counting unique occurrences. Option 0 counts all and then tries to apply distinct (invalid), 2 is syntactically incorrect, and 3 applies distinct to a single aggregate result.