Removing Duplicates — DISTINCT

SELECT DISTINCT returns only unique rows — it eliminates duplicate rows from the result set before returning them to the caller. A plain SELECT returns every row that satisfies the WHERE condition, including duplicates. The deduplication applies to the complete combination of all columns listed in SELECT: a row is considered a duplicate only if every column in the SELECT list has an identical value to another row.

The practical difference: SELECT city FROM customers returns one row per customer — 20 rows if there are 20 customers, with cities repeated for customers in the same city. SELECT DISTINCT city FROM customers returns one row per unique city — 7 rows if customers are distributed across 7 cities, regardless of how many customers are in each.

Internally, DISTINCT requires the database to process all result rows and eliminate duplicates before returning any. This is done through sorting (duplicates become adjacent) or hashing (track seen values in a hash table). Both approaches require processing the full result set, making DISTINCT a blocking operation — it cannot return partial results early the way LIMIT can. The cost grows with the number of input rows and the number of columns in the SELECT list.

COUNT(*) counts every row in the result set regardless of the values in any column. It includes rows where all columns are NULL. It is the correct function for counting how many rows a query returns — total orders, total customers, total products.

COUNT(column) counts rows where the specified column is NOT NULL. If delivery_date is NULL for undelivered orders, COUNT(delivery_date) counts only the delivered orders — those where delivery_date has a real value. This makes COUNT(column) useful for counting non-missing values: how many orders have been assigned a delivery date, how many employees have a specified manager.

COUNT(DISTINCT column) counts the number of unique non-null values in the specified column. COUNT(DISTINCT customer_id) from the orders table counts how many distinct customers have placed at least one order — not how many total orders, and not including customers who have never ordered. This is the cardinality question: how many unique values exist. A common analytical pattern combining all three: SELECT COUNT(*) AS total_orders, COUNT(delivery_date) AS delivered_orders, COUNT(DISTINCT customer_id) AS unique_customers FROM orders — each answers a different question about the same table.

When multiple columns are listed in a SELECT DISTINCT query, DISTINCT applies to the full combination of all listed columns — not to each column independently. A row is eliminated as a duplicate only if every column in the SELECT list has an identical value to another row. If any one column differs, the row is considered unique and is included in the result.

Concrete example: SELECT DISTINCT city, loyalty_tier FROM customers. The result contains every unique city-tier pair that exists in the data. If Bangalore has customers at Gold and Platinum tiers, two rows appear: (Bangalore, Gold) and (Bangalore, Platinum). If Hyderabad only has Silver customers, one row appears: (Hyderabad, Silver). The total number of result rows is the count of unique combinations — not the sum of distinct values in each column independently.

This combination behaviour is important to understand because it means adding more columns to a DISTINCT query increases the number of rows returned (or keeps it the same — never decreases it). If every combination of city and loyalty_tier is unique, SELECT DISTINCT city, loyalty_tier returns as many rows as SELECT DISTINCT city. Only if multiple rows share the exact same city AND loyalty_tier does DISTINCT reduce the count. The more columns you add, the more specific the combination and the fewer rows get eliminated as duplicates.

Both DISTINCT and GROUP BY can return unique combinations of column values — for simple deduplication they produce identical results. The choice depends on whether you need to calculate anything per unique combination.

Use DISTINCT when you only need the unique values themselves with no aggregation: SELECT DISTINCT city FROM customers. It is concise and communicates intent clearly — you want the unique set of cities, nothing more. DISTINCT is also the appropriate choice in COUNT(DISTINCT column) expressions inside aggregate queries.

Use GROUP BY when you need unique values plus any calculation per group: SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city. DISTINCT cannot perform this — it only eliminates duplicates, it does not aggregate. GROUP BY is also preferred in production code for large tables because it is more directly optimised in most database engines and is more extensible — you can add SUM, AVG, or MAX columns without changing the query structure. A practical rule: if the query only has DISTINCT with no aggregate functions, GROUP BY is an equally valid and often preferable alternative. If you need aggregation per group, GROUP BY is the only option.

DISTINCT in a JOIN query is a warning sign because it often indicates that the JOIN is producing more rows than intended — and DISTINCT is being used to hide the problem rather than fix it. The most common cause is a one-to-many JOIN that fans out rows: if you join customers to orders on customer_id, and a customer has 5 orders, that customer's row appears 5 times in the result. Adding DISTINCT collapses those 5 rows back to 1 — but you have also lost the information that there were 5 orders, and you are paying the cost of both the fan-out and the deduplication.

The correct fix depends on what you actually want. If you want one row per customer with an order count, use GROUP BY: SELECT customer_id, COUNT(order_id) AS order_count FROM customers JOIN orders USING (customer_id) GROUP BY customer_id. If you want customers who have placed at least one order (existence check), use EXISTS: SELECT customer_id FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id). Both are more correct and more efficient than joining and then applying DISTINCT.

The general principle: when you find yourself adding DISTINCT to remove unexpected duplicates, stop and investigate why the duplicates exist. The answer is almost always a JOIN issue — wrong join column, missing join condition creating a cartesian product, or a one-to-many relationship producing more rows than expected. Fixing the root cause gives you correct results with better performance. DISTINCT on top of a broken JOIN gives you correct-looking results that hide a performance problem and a misunderstood data model.

Cause: DISTINCT is applied to the combination of ALL columns in SELECT, not just the first column. If your SELECT list includes multiple columns — SELECT DISTINCT city, loyalty_tier — the result contains unique city-tier pairs, not just unique cities. If you have 7 cities but some cities have customers at multiple loyalty tiers, the number of unique (city, tier) combinations is larger than the number of unique cities alone.

Fix: Reduce your SELECT list to only the columns you want uniqueness on. To get unique cities only: SELECT DISTINCT city FROM customers. If you also need loyalty_tier in the output but want one row per city, use GROUP BY with an aggregate to select which tier to show: SELECT city, MAX(loyalty_tier) FROM customers GROUP BY city — or rethink what 'unique' means for your use case. DISTINCT is not a column-level operation — it always applies to the full row.

Cause: DISTINCT requires processing all rows in the result before returning any, using either a sort or hash-based deduplication. On a large table with millions of rows and no index on the SELECT columns, this is an expensive O(n log n) or O(n) operation. If the table has no index covering the DISTINCT columns, the database performs a full table scan followed by a full sort or hash of all rows.

Fix: Three approaches in order of preference. First, add an index on the columns used in DISTINCT — the database can then scan the index (much smaller than the table) to find unique values. Second, rewrite as GROUP BY which may use a more efficient execution plan: SELECT city FROM customers GROUP BY city. Third, if you need COUNT(DISTINCT column), consider caching or pre-computing the value in a summary table if it is queried frequently. Use EXPLAIN ANALYZE to see the actual execution plan and confirm whether an index is being used.

Cause: COUNT(DISTINCT column) excludes NULL values from its count. If the column contains NULL values, those rows are not counted. For example, if the brand column is NULL for 3 products and there are 12 non-null distinct brands, COUNT(DISTINCT brand) returns 12, not 15. Additionally, values that appear to be different but are actually the same due to trailing spaces or case differences ('Amul' vs 'amul') may count as two distinct values when you expected one.

Fix: First, check for NULLs: SELECT COUNT(*) FROM products WHERE brand IS NULL — this shows how many NULL brand rows exist. If NULLs should be counted as a distinct value, add a COALESCE: COUNT(DISTINCT COALESCE(brand, 'Unknown')). Second, check for case or whitespace inconsistencies: SELECT DISTINCT LOWER(TRIM(brand)) FROM products — this normalises the values and shows what you actually have. If inconsistencies exist, fix the data at source or use COUNT(DISTINCT LOWER(TRIM(brand))) in your query.

Cause: DISTINCT considers two rows identical if every column in the SELECT list has the same value. If your SELECT list does not include a unique identifier (like order_id), two genuinely different orders with identical visible data — same customer, same date, same amount — will be collapsed into one row by DISTINCT. This is a data loss bug, not a DISTINCT bug.

Fix: Include a unique identifier column (primary key) in your SELECT list when using DISTINCT on transactional data. SELECT DISTINCT order_id, customer_id, total_amount ensures each order is unique because order_id is unique. If including the primary key defeats the purpose of your DISTINCT (you wanted to find duplicates at a different level of granularity), reconsider whether DISTINCT is the right tool — GROUP BY with HAVING COUNT(*) > 1 is better for finding genuine duplicates.

Cause: In some databases (notably older MySQL versions and some SQL Server configurations), when using SELECT DISTINCT, all columns in ORDER BY must also appear in the SELECT list. This is logically required: if DISTINCT is deduplicating on specific columns, sorting by a column not in the SELECT list would produce an ambiguous result — the database does not know which instance of the duplicate value to use for sorting.

Fix: Add the ORDER BY column to the SELECT list: instead of SELECT DISTINCT city FROM customers ORDER BY loyalty_tier — which fails because loyalty_tier is not in the SELECT — use SELECT DISTINCT city, loyalty_tier FROM customers ORDER BY loyalty_tier, city. If you do not want loyalty_tier in the output, use GROUP BY instead: SELECT city FROM customers GROUP BY city ORDER BY MIN(loyalty_tier) — GROUP BY allows ordering by an aggregate of a non-selected column.

The FreshMart marketing team wants to understand their customer reach across India. Write three queries: (1) All distinct states where FreshMart has customers, sorted alphabetically. (2) The total count of distinct cities across all customers. (3) All distinct store-city combinations — which city each store is in — sorted by city.