COUNT(DISTINCT customer_id) across a table with hundreds of millions of rows genuinely has to examine, in effect, every distinct value to count them correctly, there's no way around that cost for an exact answer. APPROX_COUNT_DISTINCT trades a small, statistically bounded margin of error for a dramatically faster result on exactly that kind of huge table.
Learning Objectives
- Explain what APPROX_COUNT_DISTINCT trades away, and why that trade is worth it on a genuinely large table.
- Use the real function, with its real version floor, on SQL Server and on Oracle, the only two databases here that have it.
- Know that the other four databases have no built-in equivalent at all.
Huge Table
Hundreds of millions of rows
Approximate Algorithm
A small, bounded margin of error
Fast Distinct Count
A dramatic speedup over an exact COUNT(DISTINCT)
Support, by Database
| Database | Support |
|---|---|
| SQL Server | APPROX_COUNT_DISTINCT(col), added in SQL Server 2019. |
| Oracle | APPROX_COUNT_DISTINCT(col), added in Oracle 12.1.0.2. Oracle also offers an alter session set approx_for_aggregation = TRUE option, which quietly routes an ordinary COUNT(DISTINCT ...) onto the approximate path without rewriting the query at all. |
| MySQL / MariaDB / PostgreSQL / SQLite | No built-in approximate distinct-count function. PostgreSQL needs the separate hll extension (not built in by default) for genuinely comparable functionality, the other three have no real equivalent in plain SQL at all. |
Using It
SELECT APPROX_COUNT_DISTINCT(email) AS approx_unique_emails FROM employees;
SELECT APPROX_COUNT_DISTINCT(email) AS approx_unique_emails FROM employees;
Try It Yourself
Get an approximate count of distinct email addresses in the employees table, on Oracle.
SELECT APPROX_COUNT_DISTINCT(email) AS approx_unique_emails FROM employees;
Frequently Asked Questions
How much accuracy does APPROX_COUNT_DISTINCT actually give up?
It's a small, statistically bounded margin, not a wild guess, both SQL Server's and Oracle's implementations document a specific, consistently small error rate under real-world conditions, Oracle's own documentation describes negligible deviation from the exact result in exchange for processing large data significantly faster.
When is the exact COUNT(DISTINCT ...) still the right choice over the approximate version?
Whenever the exact number genuinely matters, a billing count, a compliance figure, anything where being off by even a small margin is unacceptable. APPROX_COUNT_DISTINCT is for dashboards, exploratory analysis, and monitoring, where a fast, close-enough number is actually more useful than a slow, exact one.
Common Beginner Mistakes
Expecting APPROX_COUNT_DISTINCT to exist on MySQL, PostgreSQL, MariaDB, or SQLite
None of the four has a built-in equivalent, PostgreSQL's closest path is the separate, not-built-in hll extension, the other three have no comparable function in plain SQL at all.
Using APPROX_COUNT_DISTINCT where an exact count is genuinely required
It's a statistical approximation by design, even with a small, bounded error rate, using it for something like a legally required exact count is the wrong tool, reach for the plain COUNT(DISTINCT ...) there regardless of the performance cost.
Best Practices
- Reach for APPROX_COUNT_DISTINCT specifically on genuinely large tables where an exact COUNT(DISTINCT ...) is measurably too slow, not as a default habit on every table.
- Never use it where the exact number has a real consequence (billing, compliance, legal reporting), use the exact COUNT(DISTINCT ...) there regardless of cost.
- On Oracle, consider the session-level approx_for_aggregation setting when an entire reporting workload should transparently use the approximate path, rather than rewriting every individual query.
Interview Questions
What does APPROX_COUNT_DISTINCT trade away, and why is that trade often worth it?
It trades exact accuracy for speed, giving up a small, statistically bounded margin of error in exchange for a dramatically faster result on a table large enough that an exact COUNT(DISTINCT ...) has to examine, in effect, every distinct value to be certain. For dashboards, exploratory analysis, and monitoring, where a fast, close number beats a slow, exact one, that trade is clearly worth it, for billing or compliance figures, it isn't.
Which of these six databases have APPROX_COUNT_DISTINCT built in, and since which version?
SQL Server, since SQL Server 2019, and Oracle, since Oracle 12.1.0.2. The other four, MySQL, MariaDB, PostgreSQL, and SQLite, have no built-in equivalent, PostgreSQL's closest option is the separate hll extension, not something built in by default.
Summary
APPROX_COUNT_DISTINCT trades a small, bounded margin of error for a dramatic speedup over an exact COUNT(DISTINCT ...) on a genuinely large table. Only SQL Server (2019+) and Oracle (12.1.0.2+) have it built in, the other four databases here have no real built-in equivalent in plain SQL.
What's Next?
Two genuinely single-database functions to close out this module: SQL Server's CHECKSUM_AGG, and Oracle's BITMAP_CONSTRUCT_AGG, included for completeness even though each exists on exactly one of these six databases.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →