DISTINCT Keyword in SQL

GROUP BY, covered in the previous lessons, is built for aggregation, computing a COUNT, SUM, or AVG per group. When all you want is unique rows, with no aggregate function involved at all, DISTINCT is the more direct tool. The two can sometimes produce the same set of rows when a GROUP BY query has no aggregate functions in its SELECT list, but they exist for different reasons: GROUP BY is the construct for aggregation, DISTINCT is duplicate elimination on the final result rows.

If a request can be phrased as "show me every unique X" with no summarizing involved, that's the signal DISTINCT is the right tool. The moment the request becomes "show me every X along with a count, total, or average," you're back to GROUP BY, exactly the distinction the previous lessons in this module were built around.

To pull every unique email address from a mailing list:

SELECT DISTINCT Email
FROM MailingList;

This returns every distinct value in Email, with duplicates collapsed down to a single occurrence each.

DISTINCT can be combined with WHERE exactly as you'd expect, filtering happens first, then duplicates are eliminated from whatever rows survive the filter:

SELECT DISTINCT Email
FROM MailingList
WHERE Active = 'Y';

This pulls unique email addresses only from active subscribers, ignoring inactive ones entirely before duplicate elimination ever runs. That ordering, filter first, then deduplicate, is the same WHERE-before-everything-else principle already established for GROUP BY.

One correction worth making here: the result is not guaranteed to come back sorted by email or in any particular order at all, unless an explicit ORDER BY is added. This is the exact same misconception covered in an earlier lesson about GROUP BY: whatever mechanism the database uses internally to find duplicates, comparable to the HASH GROUP BY and SORT GROUP BY distinction covered previously, might happen to produce output that looks sorted on a given run, but that's incidental, not a guarantee. To guarantee an order, add ORDER BY explicitly:

SELECT DISTINCT Email
FROM MailingList
ORDER BY Email;

This also fits into the same logical processing order established for GROUP BY and HAVING: duplicate elimination happens after WHERE, GROUP BY, and HAVING have all already run, and ORDER BY still comes last, after DISTINCT has finished collapsing the rows. DISTINCT doesn't get a special exemption from that sequence just because it looks like a simple keyword rather than a full clause.

DISTINCT applies to the entire row of selected columns together, not to each column independently:

SELECT DISTINCT department, job_title
FROM employees;

This returns every unique combination of department and job_title, not a separate list of unique departments alongside a separate list of unique job titles. A row is only eliminated as a duplicate if every selected column matches another row exactly.

This has a direct consequence worth internalizing: if you add a column that's already unique per row, like a primary key, to a DISTINCT query, every row survives, since no two rows can ever match on that column. Consider a MemberDetails table where several members share a city:

SELECT City FROM MemberDetails;

City
Townsville
Orange Town
New Town
Orange Town
Orange Town
Big City
Windy Village

Orange Town appears three times, once per member living there. Adding DISTINCT collapses it to one:

SELECT DISTINCT City FROM MemberDetails;

City
Big City
New Town
Orange Town
Townsville
Windy Village

But add MemberId, which is unique on every row, back into the mix, and DISTINCT stops removing anything:

SELECT DISTINCT City, MemberId FROM MemberDetails;

City                 MemberId
Big City             8
New Town             4
Orange Town          5
Orange Town          6
Orange Town          7
Townsville           1
Windy Village        9

Orange Town shows up three times again, because each of those three rows has a different MemberId, so no two rows are identical across both selected columns. Using DISTINCT alongside a column that's already guaranteed unique doesn't accomplish anything; it's a sign the query is asking for something other than what DISTINCT actually does. If you find yourself adding DISTINCT to a query and it isn't removing any rows, that's usually a clue that one of the selected columns is a primary key or otherwise unique, and the actual goal might be better served by removing that column from the SELECT list rather than keeping DISTINCT around out of habit.

Since the previous several lessons focused entirely on GROUP BY, it's worth putting the two side by side directly:

	DISTINCT	GROUP BY
Purpose	Removes duplicate rows from the result set	Partitions rows into groups for aggregation
Requires an aggregate function?	No	Not strictly, but it's rarely used without one
Guarantees output order?	No	No
Can appear with aggregate functions?	Yes, inside the function's argument (COUNT(DISTINCT x))	Yes, in the SELECT list alongside the grouping columns
Typical use	"Show me every unique X"	"Show me a count, sum, or average per X"

When a GROUP BY query lists only the grouping columns and no aggregate functions at all, it produces the same rows DISTINCT would on those same columns. That overlap is exactly why the two get compared, not because they're the same construct wearing different names.

Case sensitivity. Whether 'Sales' and 'sales' are treated as distinct values depends on the collation in effect for that column or session. Oracle's default is a case-sensitive, binary comparison, so DISTINCT will normally treat differently-cased strings as different values unless a case-insensitive collation has been configured. Concretely, if a department column has both 'Sales' and 'sales' entered inconsistently over time, this query returns two rows, not one:

SELECT DISTINCT department FROM employees;

department
Sales
sales

That's rarely the intended result; it's usually a sign of inconsistent data entry rather than two genuinely different departments. Normalizing case before comparing, with UPPER(department) or LOWER(department), collapses them to a single value if that's actually what you want:

SELECT DISTINCT UPPER(department) FROM employees;

Performance. DISTINCT does have a real cost on large tables, but the common explanation that "it has to sort everything" is an oversimplification, the same kind of oversimplification already corrected for GROUP BY: the optimizer can implement duplicate elimination as either a hash-based or a sort-based operation, and which one it picks depends on the data and available indexes, not a fixed rule that sorting is always involved.

NULL values. DISTINCT treats all NULLs as identical to one another for the purpose of duplicate elimination, so a column with several NULL entries returns just one NULL row, not one per occurrence. If three employees have no manager_id on file:

SELECT DISTINCT manager_id FROM employees;

manager_id
100
101
(null)

All three NULL rows collapse into the single (null) entry shown above, alongside whatever actual manager IDs exist. This is a deliberate exception to how NULL normally behaves in comparisons; ordinarily two NULLs are never considered equal to each other under =, but DISTINCT's duplicate-elimination logic treats them as matching for exactly this purpose.

DISTINCT isn't limited to the SELECT list. It can also sit inside an aggregate function's argument, telling that function to ignore duplicate values before computing its result:

SELECT COUNT(DISTINCT department_id) FROM employees;

This counts how many different departments have at least one employee, which is a different question than COUNT(*) or plain COUNT(department_id) would answer, since either of those would count every employee row rather than every unique department. The same pattern works with SUM, AVG, MIN, and MAX:

SELECT region, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY region;

This finds, per region, how many distinct customers placed at least one order, correctly counting each customer once even if they ordered multiple times. COUNT(*) has no DISTINCT form of its own; to count unique non-null values of a specific expression, COUNT(DISTINCT expr) is the pattern.

SUM(DISTINCT ...) is less common but follows the same logic: it sums only the unique values of an expression, ignoring repeats. Suppose several products share the same list price, and you want the total of each distinct price point rather than the total across every product:

SELECT SUM(DISTINCT price) AS total_of_unique_prices
FROM products;

If three products are all priced at 19.99, this counts 19.99 once toward the sum rather than three times. That's a narrower use case than COUNT(DISTINCT ...), and it's worth pausing to make sure it's genuinely what a question is asking for, since summing unique values rather than all values is easy to reach for by habit when a plain SUM(price) was actually the intended calculation.

DISTINCT shows up naturally inside the kind of subqueries covered a few lessons back. When a subquery feeds an IN clause, adding DISTINCT doesn't change which rows the outer query returns, IN already treats repeated values in its list as redundant, but it can reduce the amount of data the subquery has to hand back:

SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM job_history
);

Whether DISTINCT here actually helps performance or just adds overhead depends on the optimizer and the data, the same "test rather than assume" guidance from the subquery lessons applies here too. The more important point is conceptual: DISTINCT composes cleanly with everything covered in those earlier lessons, subqueries, joins, and aggregate functions all interact with DISTINCT the same way whether it's sitting in the outermost SELECT or nested several layers deep.

Since COUNT(DISTINCT ...) on a genuinely large table can be expensive, exactly the performance concern raised above, Oracle AI Database 26ai offers APPROX_COUNT_DISTINCT as a faster alternative when an exact count isn't necessary:

SELECT APPROX_COUNT_DISTINCT(customer_id) AS approx_customers
FROM orders;

This returns an approximate count of distinct values, processing large amounts of data significantly faster than an exact COUNT(DISTINCT ...), with only a negligible deviation from the true number. It's the right tool when you're reporting a dashboard metric like "roughly how many unique visitors" rather than a figure that needs to be exact to the row.

The trade-off is worth stating plainly rather than glossing over: APPROX_COUNT_DISTINCT will not return the exact same number as COUNT(DISTINCT ...) run against the same data. On a table with exactly 84,213 distinct customers, the approximate version might return 84,180 or 84,240, close enough for a dashboard tile, not close enough for a number that's going into a financial report or a contractual reconciliation. The deciding question is simple: does the number need to be exact, or does it need to be fast? If a report genuinely requires the precise count, COUNT(DISTINCT ...) remains the correct choice regardless of table size; APPROX_COUNT_DISTINCT is an optimization for the cases where approximate is good enough, not a universal replacement.

A Common Mistake: Using DISTINCT to Paper Over a Bad Join

One habit worth catching early: reaching for DISTINCT the moment a query starts returning more rows than expected after adding a JOIN, without stopping to ask why those extra rows appeared in the first place. A join that matches each row on one side to multiple rows on the other, a one-to-many relationship, produces exactly that kind of row multiplication, and DISTINCT will often make the symptom disappear without fixing the actual cause.

SELECT DISTINCT e.last_name, e.department_id
FROM employees e
JOIN job_history j ON e.employee_id = j.employee_id;

If a given employee has three rows in job_history, from three past job assignments, the join produces three rows for that employee, and DISTINCT collapses them back down to one, assuming last_name and department_id happen to be identical across all three. That works here, but only by accident: the real issue is that this query joins employees to job_history at all when the underlying question, apparently, wasn't actually about job history. If a different query joined to a table where the extra columns genuinely did vary, DISTINCT would fail to collapse anything, and you'd be left wondering why duplicates are still showing up despite the keyword being right there in the query.

The better fix is almost always to address the join itself: aggregate the many-side table first, filter it down to the specific rows actually needed, or reconsider whether the join belongs in the query at all. DISTINCT is a legitimate tool for genuinely getting unique rows; it's a poor substitute for understanding why a join is multiplying rows in the first place.

Looking Ahead

DISTINCT and GROUP BY will continue to show up side by side throughout the rest of this course. A few points worth carrying forward:

• DISTINCT eliminates duplicate rows from a result set; it doesn't aggregate, summarize, or compute anything.
• It applies to the entire selected row, not each column independently, so adding a unique column like a primary key defeats it entirely.
• Neither DISTINCT nor GROUP BY guarantees output order without an explicit ORDER BY, and neither one's internal mechanism (hash-based or sort-based) is something you should write code that depends on.
• COUNT(DISTINCT expr) and its relatives handle the common case of counting or summing only unique values inside an aggregate, while APPROX_COUNT_DISTINCT trades exactness for speed on large tables when that trade is acceptable.
• DISTINCT is not a substitute for fixing a join that's producing more rows than intended; it's a tool for genuine duplicate elimination, not a patch for unexamined row multiplication.

The distinction to hold onto above all the rest: reach for DISTINCT when the goal is simply unique rows, and reach for GROUP BY the moment an aggregate function needs to summarize each group.