When you query a table, the result set often contains repeated values. For example, if you list the countries of every customer, the same country name appears many times. The DISTINCT keyword tells the database to return only unique values, removing duplicates from the result.

In this tutorial you will learn what DISTINCT does, how to apply it to one or several columns, how it interacts with NULL, and when to prefer alternatives such as GROUP BY.

Prerequisites

To follow along you should be comfortable with:

• writing a basic SELECT statement;
• filtering rows with WHERE;
• running SQL against a local database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).

If you are new to SELECT, review a beginner SELECT tutorial first.

Sample Database

We will use a small online bookstore schema. Create the table and insert the sample data below.

CREATE TABLE books (
    book_id      INTEGER      PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    author_name  VARCHAR(100) NOT NULL,
    genre        VARCHAR(50),
    language     VARCHAR(30)  NOT NULL,
    published_year INTEGER
);

INSERT INTO books (book_id, title, author_name, genre, language, published_year) VALUES
    (1, 'The Silent River',       'Anna Novak',      'Fiction',   'English', 2019),
    (2, 'Cooking with Herbs',     'Marco Bianchi',   'Cooking',   'English', 2021),
    (3, 'Distant Galaxies',       'Anna Novak',      'Science',   'English', 2022),
    (4, 'La Cucina Italiana',     'Marco Bianchi',   'Cooking',   'Italian', 2020),
    (5, 'Silent Mountains',       'Chen Wei',        'Fiction',   'English', 2019),
    (6, 'Introduction to Botany', 'Priya Sharma',    'Science',   'English', 2018),
    (7, 'Recipes from Home',      'Marco Bianchi',   'Cooking',   'Italian', 2023),
    (8, 'Untitled Draft',         'Anna Novak',       NULL,       'English', 2024),
    (9, 'Notes on Rivers',        'Chen Wei',         NULL,       'English', 2020);

Basic Syntax

Place DISTINCT immediately after SELECT:

SELECT DISTINCT column_name
FROM table_name;

• DISTINCT applies to the entire row produced by the SELECT list, not to a single column in isolation.
• When you list multiple columns, uniqueness is evaluated across the whole combination.

Practical Example

Suppose the marketing team wants a list of every genre carried by the bookstore, without duplicates.

SELECT DISTINCT genre
FROM books;

The database reads every row in books, extracts the genre value, and returns each unique value once.

Expected Result

genre
Fiction
Cooking
Science
NULL

Two rows contain NULL in genre, but DISTINCT collapses them into a single NULL in the output. DISTINCT treats NULL values as equal to each other for the purpose of removing duplicates, even though NULL = NULL is not true in general SQL comparisons.

Additional Examples

DISTINCT on Multiple Columns

Uniqueness is calculated across the combination of listed columns.

SELECT DISTINCT
    author_name,
    language
FROM books;

Expected result:

author_name	language
Anna Novak	English
Marco Bianchi	English
Marco Bianchi	Italian
Chen Wei	English
Priya Sharma	English

Marco Bianchi appears twice because he has books in two different languages. The pair (Marco Bianchi, English) is distinct from (Marco Bianchi, Italian).

Combining DISTINCT with WHERE

Filter rows first, then remove duplicates:

SELECT DISTINCT author_name
FROM books
WHERE genre = 'Cooking';

Expected result:

author_name
Marco Bianchi

Combining DISTINCT with ORDER BY

You can sort the unique values. Every column in ORDER BY must also appear in the SELECT list when using DISTINCT.

SELECT DISTINCT genre
FROM books
WHERE genre IS NOT NULL
ORDER BY genre ASC;

Counting Unique Values

To count how many unique values exist, wrap the column in COUNT(DISTINCT ...):

SELECT COUNT(DISTINCT author_name) AS unique_authors
FROM books;

Expected result:

unique_authors
4

Note that COUNT(DISTINCT column_name) ignores NULL values, which is different from how DISTINCT behaves in a SELECT list.

DISTINCT vs. GROUP BY

The following two queries produce the same set of unique genres:

SELECT DISTINCT genre
FROM books;

SELECT genre
FROM books
GROUP BY genre;

Prefer DISTINCT when you only need unique values. Prefer GROUP BY when you also need aggregate values per group, such as counts or sums:

SELECT
    genre,
    COUNT(*) AS book_count
FROM books
GROUP BY genre;

Common Mistakes

Applying DISTINCT to One Column Expecting per-Column Uniqueness

Incorrect assumption:

SELECT DISTINCT author_name, title
FROM books;

Readers sometimes expect this to return one row per unique author_name. It does not. DISTINCT considers the entire selected row, and every title is unique, so no rows are collapsed.

If you want one author per row, select only author_name:

SELECT DISTINCT author_name
FROM books;

Using DISTINCT to Hide a Faulty JOIN

Adding DISTINCT to remove duplicate rows caused by an incorrect join often masks a real problem. Instead, review the join condition, the relationships between tables, and whether a one-to-many relationship is producing the extra rows.

Assuming DISTINCT Sorts the Result

DISTINCT does not guarantee any specific ordering. If the order matters, add an explicit ORDER BY clause.

Database Compatibility

SELECT DISTINCT is part of standard SQL and works in:

• PostgreSQL;
• MySQL;
• MariaDB;
• SQL Server;
• Oracle Database;
• SQLite.

PostgreSQL additionally supports a non-standard extension, DISTINCT ON, which keeps the first row per group according to ORDER BY:

SELECT DISTINCT ON (author_name)
    author_name,
    title,
    published_year
FROM books
ORDER BY author_name, published_year DESC;

This returns the most recently published book per author. DISTINCT ON is not portable, so avoid it in code that must run on other databases.

Best Practices

• List only the columns you need. DISTINCT on many columns is expensive because the database must compare every column value.
• Filter first with WHERE. Reducing the row set before applying DISTINCT lowers the work required.
• Prefer GROUP BY when you need aggregates. Do not combine DISTINCT with GROUP BY unless you have a specific reason.
• Add ORDER BY when order matters. DISTINCT does not sort.
• Measure performance on large tables. DISTINCT may require sorting or hashing the entire result. Inspect the execution plan with EXPLAIN to understand the cost. The exact syntax and output of EXPLAIN vary between database systems.
• Handle NULL deliberately. Remember that DISTINCT treats all NULL values as one group, while COUNT(DISTINCT column_name) ignores them entirely.

Conclusion

You learned how to use the SQL DISTINCT keyword to remove duplicate rows from a result set, how it applies to one or more columns, how it interacts with NULL, and how it compares to GROUP BY. Use DISTINCT when you need a clean list of unique values, and switch to GROUP BY when you also need aggregate information per group.