SELECT DISTINCT

This lesson on SELECT DISTINCT is hands-on and example-driven. You will learn how to filter out duplicate values from query results using the SELECT DISTINCT clause. You will be able to write queries that retrieve only unique entries and calculate the total count of those unique items across any column.

What You'll Be Able To Do

• Identify the primary purpose of the SELECT DISTINCT statement in SQL.
• Write a query to retrieve only the unique values present in a specified column.
• Explain the mechanism SQL uses to filter duplicate rows before returning the result set.
• Calculate the total number of unique entries within a column using an aggregate function.
• Recognize the specific database environments where standard COUNT(DISTINCT) syntax is unsupported.

Detailed Concept Walkthrough

1. Retrieving Unique Values

SELECT DISTINCT is a clause placed immediately after SELECT that forces the database engine to evaluate the result set and eliminate identical rows. It is essential for generating clean lists of categories or identifiers.

• Mechanism: The database scans the specified column(s) and uses an internal hash or sort operation to identify and discard any rows that exactly match a row already processed. This process ensures that if you select multiple columns (e.g., SELECT DISTINCT City, State), the combination of City and State must be unique for the row to be returned.
• Execution Flow: DISTINCT is applied after the initial data selection (the FROM and WHERE clauses are processed) but before the final result set is delivered to the user, acting as a final filter on the projected columns defined in the SELECT list. This means filtering happens late in the query execution pipeline.
• Syntax Rule: The DISTINCT keyword must be placed directly following the SELECT keyword; it is a modifier to the entire projection list and cannot be applied to individual columns unless used within an aggregate function.

-- Assume 'Products' table has multiple entries for the same 'SupplierID'
-- Standard SELECT returns all rows, including duplicates:
SELECT SupplierID
FROM Products;

-- SELECT DISTINCT returns only one instance of each unique SupplierID:
SELECT DISTINCT SupplierID
FROM Products;

Key Takeaway: DISTINCT applies to the entire selected row, ensuring the combination of all columns is unique.

2. Aggregating Distinct Counts

The COUNT(DISTINCT column_name) function is an aggregate tool used to quickly determine the cardinality (number of unique values) within a specific column. This avoids retrieving the actual list of values first.

• Mechanism: The database engine first identifies all unique values for the specified column, similar to the process used by SELECT DISTINCT, effectively creating a temporary set of unique entries. It then applies the standard COUNT() function to this temporary, filtered set, returning a single integer result.
• Performance Note: Calculating COUNT(DISTINCT) often requires significantly more resources (memory and CPU) than a simple COUNT(*) because the engine must store and compare all values in the column to ensure uniqueness before counting them. For very large tables, this operation can be resource-intensive and slow.
• Syntax Rule: The DISTINCT keyword must be placed inside the parentheses of the COUNT() function, immediately preceding the column name, ensuring the aggregation only operates on the unique values of that specific column.

-- Count the total number of rows in the table (includes duplicates):
SELECT COUNT(*) AS TotalProducts
FROM Products;

-- Count the number of unique suppliers represented in the table:
SELECT COUNT(DISTINCT SupplierID) AS UniqueSuppliers
FROM Products;

Key Takeaway: Use COUNT(DISTINCT) to measure the variety of entries in a dataset efficiently.

3. COUNT(DISTINCT) Limitations

While standard SQL supports COUNT(DISTINCT), certain database systems, notably MS Access, do not implement this specific syntax. This requires developers to use alternative, often more complex, methods.

• Compatibility Issue: MS Access uses a different SQL dialect (Jet SQL) which historically lacked native support for the DISTINCT keyword when nested within aggregate functions like COUNT(). This limitation forces users of that specific database to find alternative query structures.
• Workaround: To achieve the same result in unsupported environments, a common technique is to use a subquery that first executes a standard SELECT DISTINCT to generate the unique list. The outer query then performs a simple COUNT(*) on the rows returned by that unique list.
• Best Practice: Always verify the specific SQL dialect and feature support of your target database environment before relying on advanced syntax like COUNT(DISTINCT), as compatibility issues are common across different vendors (e.g., MySQL vs. MS Access).

-- Standard SQL syntax (works in most modern databases):
SELECT COUNT(DISTINCT City) FROM Customers;

-- NOTE: This standard syntax is not supported in MS Access.
-- MS Access requires a subquery workaround (not covered here).

Key Takeaway: Be aware of database-specific limitations, especially when working with MS Access environments.

Topics Covered in SELECT DISTINCT

1. SELECT DISTINCT Syntax (0:00 - 0:57) — The DISTINCT keyword is placed after SELECT to ensure only unique rows are returned in the result set.
2. Counting Unique Values (0:58 - 1:06) — The COUNT(DISTINCT column) function is used to aggregate and report the total number of unique entries in a column.
3. MS Access Limitation (1:06 - 1:16) — Standard COUNT(DISTINCT) syntax is not supported in MS Access, requiring developers to use alternative query structures.

SQL Cheat Sheet

• SELECT DISTINCT column FROM table; — Retrieve unique values from a specified column

  SELECT DISTINCT Country FROM Customers;
  

• SELECT DISTINCT col1, col2 FROM table; — Retrieve unique combinations across multiple columns

  SELECT DISTINCT City, State FROM Addresses;
  

• COUNT(DISTINCT column_name) — Calculate the number of unique entries in a column

  SELECT COUNT(DISTINCT ProductID) FROM Orders;
  

• SELECT COUNT(*) FROM table; — Count all rows, including all duplicates

  SELECT COUNT(*) FROM Employees;
  

• DISTINCT keyword — Filters out duplicate rows in the result set

  SELECT DISTINCT Name FROM Users;
  

Comparison Table

Common Pitfalls

• Mistake: Applying DISTINCT only to one column when selecting multiple columns. Avoid: Remember DISTINCT applies to the entire row combination.
• Mistake: Expecting COUNT(DISTINCT) to work in MS Access. Avoid: Use a subquery or alternative method for MS Access environments.
• Mistake: Placing DISTINCT after the column name in a standard SELECT. Avoid: DISTINCT must immediately follow the SELECT keyword.

FAQs

• Does SELECT DISTINCT slow down my query? Yes, because the database must perform extra sorting or hashing operations to identify and eliminate duplicates before returning the result set.
• What happens if I use DISTINCT with multiple columns? The combination of values across all selected columns must be unique for the row to be returned; rows are only duplicates if all columns match.
• Why is COUNT(DISTINCT) not supported everywhere? It is due to variations in SQL dialects and historical implementation choices by specific database vendors, such as MS Access.