Offensive Security Researcher & Quantum Cryptography Analyst

The DISTINCT Keyword in SQLite: A Complete Guide

Abdul Wahab Junaid
The DISTINCT keyword in SQLite

One of the very first problems you run into when writing SQL queries is duplicate data. You run a SELECT statement expecting a clean list of unique values, and instead you get the same value repeated over and over, once for every row it appears in. That’s where the DISTINCT keyword comes in — one of the simplest, most frequently used tools in SQLite (and SQL generally) for cleaning up query results.

In this article, I’ll walk through exactly how DISTINCT works in SQLite, how it behaves with multiple columns, how it interacts with NULLs, and where it fits (or doesn’t fit) compared to other tools like GROUP BY.

What Is the DISTINCT Keyword?

DISTINCT is a modifier used in a SELECT statement to eliminate duplicate rows from the result set. Instead of returning every matching row, including repeats, DISTINCT collapses identical rows down to a single instance.

It’s important to understand exactly what “duplicate” means in this context: DISTINCT considers a row a duplicate only if every selected column matches another row exactly. This becomes especially important once you start selecting multiple columns, which I’ll cover in detail below.

Basic Syntax

The syntax is refreshingly simple:

SELECT DISTINCT column_name
FROM table_name;

DISTINCT goes immediately after SELECT, before the list of columns you want to retrieve.

Let’s look at a basic example. Suppose we have a table of orders:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    city TEXT,
    total REAL
);

INSERT INTO orders (customer, city, total) VALUES
    ('Alice', 'Boston', 45.00),
    ('Bob', 'Chicago', 30.00),
    ('Carol', 'Boston', 60.00),
    ('Dave', 'Chicago', 25.00),
    ('Eve', 'Denver', 80.00);

If we run:

SELECT city FROM orders;

We get back five rows: Boston, Chicago, Boston, Chicago, Denver — with duplicates for cities that appear multiple times.

Now with DISTINCT:

SELECT DISTINCT city FROM orders;

This returns just three rows: Boston, Chicago, Denver — each city appearing exactly once, regardless of how many orders came from that city.

DISTINCT With Multiple Columns

This is where DISTINCT behavior often surprises people who are new to SQL. When you apply DISTINCT to a query selecting multiple columns, it doesn’t deduplicate each column independently — it deduplicates based on the combination of all selected columns together.

SELECT DISTINCT customer, city FROM orders;

This returns every unique combination of customer and city. Since every customer in our sample data appears only once, this query would return all five rows unchanged, because no two rows share the exact same customer-and-city pairing.

But consider a scenario where the same customer placed multiple orders from the same city:

INSERT INTO orders (customer, city, total) VALUES
    ('Alice', 'Boston', 15.00);

Now, running SELECT DISTINCT customer, city FROM orders; would return only one row for ('Alice', 'Boston'), even though Alice placed two separate orders from Boston, because the combination of those two column values is identical between the two rows.

DISTINCT and NULL Values

Another detail worth understanding: SQLite treats NULL as a single, distinct “value” for the purposes of DISTINCT deduplication, even though NULL normally represents “unknown” and doesn’t equal itself in standard comparison logic (NULL = NULL evaluates to NULL, not TRUE, in ordinary WHERE clauses).

CREATE TABLE contacts (id INTEGER PRIMARY KEY, phone TEXT);
INSERT INTO contacts (phone) VALUES ('555-1234'), (NULL), (NULL), ('555-5678');

SELECT DISTINCT phone FROM contacts;

This returns three rows: 555-1234, NULL, and 555-5678 — the two NULL entries are collapsed into a single NULL row, even though NULL doesn’t technically equal NULL in standard SQL comparison semantics. This is a special-case behavior defined specifically for DISTINCT (and similarly for GROUP BY), and it’s worth remembering since it can occasionally cause confusion.

Practical Examples

Example 1: Finding unique product categories

SELECT DISTINCT category FROM products;

A classic use case — quickly getting a list of every unique category without duplicates, useful for populating a dropdown filter in a UI, for instance.

Example 2: Counting unique values

SELECT COUNT(DISTINCT city) FROM orders;

This combines DISTINCT with an aggregate function to count how many unique cities appear in the orders table — in our original sample data, that would return 3.

Example 3: Finding unique combinations across multiple columns

SELECT DISTINCT category, subcategory FROM products;

This is useful when you want to understand the unique category/subcategory pairings in your data, perhaps to build a navigation menu that only shows subcategories that actually exist within a given category.

Example 4: DISTINCT with ORDER BY

SELECT DISTINCT city FROM orders ORDER BY city;

You can freely combine DISTINCT with ORDER BY to get a clean, alphabetically sorted list of unique values. Note that you can technically order by columns not included in your SELECT DISTINCT list under certain conditions, but keeping your ORDER BY columns aligned with your selected columns is generally the clearest, least error-prone approach.

Example 5: DISTINCT inside a subquery

SELECT * FROM customers
WHERE city IN (SELECT DISTINCT city FROM orders WHERE total > 50);

Here, DISTINCT is used inside a subquery to get a clean list of cities where high-value orders occurred, which is then used to filter the outer customers query. Technically, DISTINCT isn’t strictly necessary here since IN already handles duplicates gracefully, but it can make the query’s intent clearer and, in some query planning scenarios, can be marginally more efficient.

Common Use Cases

  1. Populating filter dropdowns and select menus in UIs. Getting a unique list of categories, statuses, regions, or tags to display as filter options.
  2. Deduplicating imported or merged data. After combining data from multiple sources, DISTINCT helps produce a clean list of unique entries.
  3. Counting unique entities. Combined with COUNT(), DISTINCT answers questions like “how many unique customers placed an order this month?”
  4. Auditing data quality. Running SELECT DISTINCT on a column that’s supposed to have a limited, known set of values (like a status field) can quickly reveal unexpected or inconsistent entries.
  5. Building lookup or reference lists on the fly. When you don’t have a dedicated lookup table for something like city names or categories, DISTINCT lets you derive one directly from your existing data.

Important Considerations

DISTINCT operates on the entire row of selected columns, not each column individually. This is the single most common point of confusion, so it’s worth repeating: SELECT DISTINCT col1, col2 deduplicates based on the combination of col1 and col2 together, not each column separately.

DISTINCT can be a performance consideration on large datasets. Because SQLite needs to compare rows against each other to identify duplicates, DISTINCT typically requires sorting or hashing the result set internally. On large tables without helpful indexes, this can add noticeable overhead compared to a plain SELECT. If you’re seeing performance issues, check whether an index on the relevant columns can help, or whether restructuring the query (perhaps using GROUP BY instead, which can sometimes be optimized differently) makes sense.

DISTINCT is not the same as GROUP BY, even though they can produce similar results. SELECT DISTINCT city FROM orders and SELECT city FROM orders GROUP BY city will often return the same rows, but GROUP BY is fundamentally about creating groups for aggregate calculations (like COUNT, SUM, AVG), while DISTINCT is purely about eliminating duplicate rows from the final output. If you’re not using any aggregate functions, DISTINCT is usually the clearer, more idiomatic choice.

DISTINCT applies to the entire SELECT list, including expressions. If you select a computed expression alongside a column, DISTINCT considers the result of that expression as part of the uniqueness comparison:

SELECT DISTINCT ROUND(total) FROM orders;

This deduplicates based on the rounded total, not the original unrounded value.

Best Practices

Troubleshooting Common Issues

I used DISTINCT but I’m still seeing what look like duplicate rows. This is almost always because you’re selecting more columns than you realize, and those additional columns differ between the rows that look like duplicates at a glance. Remember that DISTINCT considers the full combination of every selected column, so if you’re selecting an id column alongside a name column, every row will be “unique” since IDs differ, even if the names repeat. Double-check exactly which columns are in your SELECT list.

DISTINCT is running slowly on a large table. Since DISTINCT typically requires SQLite to sort or hash the result set internally to identify duplicates, large unindexed datasets can be slow to deduplicate. Consider whether an index on the relevant column(s) would help, or whether restructuring your query to filter down the dataset first (with a WHERE clause) before applying DISTINCT would reduce the amount of data that needs to be processed.

My COUNT(DISTINCT column) result seems too low. Check whether the column contains NULL values — COUNT(DISTINCT column) ignores NULLs entirely (unlike plain DISTINCT in a SELECT, which does count a single NULL as one distinct value). This is a subtle but important difference between how NULLs are treated in COUNT(DISTINCT ...) versus SELECT DISTINCT.

I expected DISTINCT to deduplicate based on just one of several selected columns. This isn’t something DISTINCT can do directly — it always operates on the full row of selected columns. If you truly only want uniqueness based on one column while still displaying data from others, you likely need a different approach, such as a GROUP BY combined with an aggregate function like MIN() or MAX() to pick a representative value for the other columns within each group.

Frequently Asked Questions

*Can I use DISTINCT with SELECT ?

Yes, SELECT DISTINCT * is valid syntax, and it deduplicates based on every single column in the table. This is most useful for finding exact duplicate rows across an entire table, though it’s less common in practice than applying DISTINCT to a specific subset of columns.

Does DISTINCT guarantee any particular ordering of results?

No. DISTINCT alone doesn’t guarantee any specific order for the returned rows. If you need a predictable order, always pair it with an explicit ORDER BY clause rather than relying on whatever incidental order the deduplication process happens to produce.

Is DISTINCT case-sensitive?

By default, yes, in the sense that it relies on the column’s collation sequence for comparison, and SQLite’s default collation (BINARY) is case-sensitive. This means “Alice” and “alice” would be treated as distinct values unless the column uses a case-insensitive collation like NOCASE.

Can I combine DISTINCT with window functions?

You can use DISTINCT in a query that also includes window functions, but be aware that window functions operate over result sets in specific ways that can interact with deduplication in non-obvious ways. It’s worth testing carefully and, if the interaction feels unclear, considering whether restructuring the query with a subquery or CTE (Common Table Expression) makes the intended logic more explicit.

Does SELECT DISTINCT ever return fewer columns than requested?

No, DISTINCT never changes which columns appear in your results — it only affects how many rows are returned by collapsing exact duplicates. The number and identity of columns in your output is entirely determined by your SELECT list, just as it would be without DISTINCT.

Is there a performance difference between DISTINCT and GROUP BY when they produce the same result?

In many cases, SQLite’s query planner can optimize them similarly, but this isn’t guaranteed across every scenario, and the more efficient choice can depend on your specific schema, indexes, and data distribution. If performance is critical, it’s worth testing both approaches with EXPLAIN QUERY PLAN against your actual data rather than assuming one is always faster.

DISTINCT vs. GROUP BY: A Closer Comparison

Since these two are so frequently confused, it’s worth a dedicated side-by-side look at where they overlap and where they genuinely diverge.

When you’re not using any aggregate functions, SELECT DISTINCT city FROM orders and SELECT city FROM orders GROUP BY city will typically produce the same set of rows. In this narrow scenario, they’re functionally interchangeable, and the choice often comes down to which one more clearly communicates your intent to someone reading the query later. Most experienced SQL developers would reach for DISTINCT here, since it directly says “give me unique values” without implying that any aggregation is happening.

The moment you introduce an aggregate function, though, the two diverge completely. GROUP BY is built specifically to support aggregation — computing a SUM(), COUNT(), or AVG() per group. DISTINCT has no equivalent capability; it can only deduplicate the final rows, not calculate anything about the members of each implied group. You genuinely cannot use DISTINCT to answer a question like “what’s the total revenue per city,” because that requires grouping rows together and computing a sum within each group — a fundamentally different operation than filtering out exact duplicate rows from a result set.

There’s also a subtle performance dimension worth knowing about. In some query planning scenarios, particularly with appropriate indexes in place, GROUP BY can occasionally allow SQLite’s query planner more flexibility in how it approaches computing unique groups, since grouping is understood by the planner as a distinct operation with its own optimization strategies, whereas DISTINCT is applied as a final deduplication pass over the entire selected result set. This isn’t a hard and fast rule — actual performance depends heavily on your schema, indexes, and data distribution — but it’s worth testing both formulations with EXPLAIN QUERY PLAN if you’re optimizing a performance-critical query that could reasonably be written either way.

The practical takeaway: reach for DISTINCT when your goal is purely about eliminating duplicate rows, and reach for GROUP BY the moment you need to calculate anything about the contents of each group, even if that calculation is as simple as a COUNT(*).

Wrapping Up

DISTINCT is one of those SQL keywords that seems trivially simple on the surface — and for basic single-column use cases, it really is. But once you start working with multiple columns, NULL values, and larger datasets, understanding its exact behavior becomes genuinely important for writing correct, efficient queries. Remember the core rule: DISTINCT deduplicates based on the full combination of selected columns, not column by column. Keep that in mind, and you’ll be able to use it confidently anywhere you need clean, duplicate-free results.

Exit mobile version