SQL DISTINCT: Return Unique Rows and Values

DISTINCT removes duplicate rows from a SELECT result. To return each class only once:

SELECT DISTINCT class
FROM student
ORDER BY class;

If class Four appears several times in the table, it appears only once in this result.

SQL DISTINCT Syntax Top ↑

SELECT DISTINCT column1, column2
FROM table_name;

DISTINCT removes duplicate result rows after the selected expressions have been evaluated.

Return Unique Values from One Column Top ↑

Using the Plus2net student table:

SELECT DISTINCT class
FROM student;

For these six sample rows, the result is:

DISTINCT with Multiple Columns Top ↑

With more than one selected column, DISTINCT removes duplicate combinations.

SELECT DISTINCT class, gender
FROM student
ORDER BY class, gender;

If one class contains both male and female students, that class can appear twice because the two (class, gender) combinations are different.

DISTINCT with WHERE Top ↑

Use WHERE first when only selected rows should contribute to the distinct result.

SELECT DISTINCT class
FROM student
WHERE mark >= 70
ORDER BY class;

This returns the different classes represented by students whose mark is at least 70.

DISTINCT with ORDER BY Top ↑

DISTINCT removes duplicate result rows; ORDER BY controls their display order.

SELECT DISTINCT class
FROM student
ORDER BY class ASC;

Do not rely on DISTINCT itself to return values in a particular order.

Count Unique Values with COUNT(DISTINCT) Top ↑

Use COUNT(DISTINCT ...) when you need the number of unique non-NULL values rather than the values themselves.

SELECT COUNT(DISTINCT class) AS unique_classes
FROM student;

For the complete Plus2net student table, the output is 7.

DISTINCT and NULL Values Top ↑

If a selected column contains several NULL values, SELECT DISTINCT returns one NULL row for that distinct result.

SELECT DISTINCT class
FROM student3;

This differs from COUNT(DISTINCT class), which counts distinct non-NULL class values and does not count NULL.

DISTINCT vs GROUP BY Top ↑

For simply returning unique values, DISTINCT expresses the intent directly:

SELECT DISTINCT class
FROM student;

A GROUP BY query can produce the same list:

SELECT class
FROM student
GROUP BY class;

However, GROUP BY is primarily used when you want one result row per group together with aggregate calculations:

SELECT class,
       COUNT(*) AS total_students,
       AVG(mark) AS average_mark
FROM student
GROUP BY class;

Use DISTINCT for deduplicating a SELECT result. Use GROUP BY when the groups themselves are needed for aggregate analysis.

DISTINCT vs UNIQUE Constraint Top ↑

DISTINCT is a query operator. It changes only the rows returned by that SELECT.

A UNIQUE constraint is a table rule used to prevent duplicate values from being stored in a constrained key.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE
);

Using DISTINCT does not clean or permanently remove duplicate data from the table.

DISTINCT with JOIN Top ↑

Suppose a customer can make several purchases. A JOIN can therefore return the same customer more than once.

Return each customer who has at least one sale only once:

SELECT DISTINCT c.c_id, c.name
FROM customer AS c
INNER JOIN sale AS s
    ON s.c_id = c.c_id
ORDER BY c.c_id;

For customers who have not purchased anything, use a LEFT JOIN or another anti-match pattern rather than DISTINCT.

Unique Values from Two Columns with UNION Top ↑

Suppose tournament teams are stored in two columns:

UNION combines the two result sets and removes duplicate rows:

SELECT Team_1 AS team
FROM icc_world_cup
UNION
SELECT Team_2
FROM icc_world_cup
ORDER BY team;

The unique teams are Aus, Eng, India, NZ, SA and SL.

SQL for the tournament sample table Top ↑

CREATE TABLE icc_world_cup (
    Team_1 VARCHAR(20) DEFAULT NULL,
    Team_2 VARCHAR(20) DEFAULT NULL,
    Winner VARCHAR(20) DEFAULT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

INSERT INTO icc_world_cup
    (Team_1, Team_2, Winner)
VALUES
    ('India', 'SL', 'India'),
    ('SL', 'Aus', 'Aus'),
    ('SA', 'Eng', 'Eng'),
    ('Eng', 'NZ', 'NZ'),
    ('Aus', 'India', 'India');

DISTINCT with PHP PDO Top ↑

For a static DISTINCT query containing no external values, PDO query() is sufficient:

<?php
require 'config.php';

$sql="SELECT DISTINCT class, gender
      FROM student
      ORDER BY class, gender";

$stmt=$dbo->query($sql);

echo '<table class="table table-striped">';
echo '<tr><th>Class</th><th>Gender</th></tr>';

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $class=htmlspecialchars(
        (string)$row['class'],
        ENT_QUOTES,
        'Windows-1252'
    );

    $gender=htmlspecialchars(
        (string)$row['gender'],
        ENT_QUOTES,
        'Windows-1252'
    );

    echo "<tr><td>$class</td><td>$gender</td></tr>";
}

echo '</table>';

If a WHERE condition contains application input, use a prepared statement for those values. See PDO SELECT and record fetching.

DISTINCT with Python SQLAlchemy Top ↑

The existing Plus2net Python/MySQL example can use the same DISTINCT SQL. With modern SQLAlchemy, execute textual SQL through a connection:

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

engine = create_engine(
    "mysql+mysqldb://root:pw@localhost/db"
)

try:
    with engine.connect() as connection:
        result = connection.execute(
            text(
                "SELECT DISTINCT class FROM student ORDER BY class"
            )
        )

        for row in result:
            print(row[0])

except SQLAlchemyError as error:
    print(error)

The DISTINCT logic belongs to SQL; Python only executes the statement and consumes the result.

DISTINCT Performance Notes Top ↑

• DISTINCT may require sorting, hashing or temporary work to eliminate duplicate result rows.
• Select only the columns you actually need. Adding more selected columns changes the definition of a duplicate row.
• Indexes can help some DISTINCT queries, depending on the selected columns, WHERE conditions and execution plan.
• Do not add DISTINCT merely to hide an incorrect JOIN that is creating unexpected duplicates. Fix the join logic when duplicate rows are not logically expected.
• Use EXPLAIN when a DISTINCT query is unexpectedly expensive on a large table.

Common SQL DISTINCT Mistakes Top ↑

Thinking DISTINCT applies only to the first column Top ↑

With multiple selected columns, DISTINCT compares the complete selected row.

Confusing SELECT DISTINCT with COUNT(DISTINCT) Top ↑

SELECT DISTINCT returns the unique rows. COUNT(DISTINCT column) returns the number of distinct non-NULL values.

Assuming DISTINCT removes duplicate rows from the table Top ↑

DISTINCT changes only the query result. It does not modify stored data.

Using DISTINCT to hide a bad JOIN Top ↑

If the JOIN should logically return one row but produces many, check the join condition before adding DISTINCT.

Assuming DISTINCT sorts the result Top ↑

Use ORDER BY when output order matters.

Assuming COUNT(DISTINCT) counts NULL Top ↑

COUNT(DISTINCT expression) ignores NULL values.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: