Guide to MySQL DISTINCT Clause

MySQL DISTINCT is a keyword used in SQL queries, specifically in SELECTstatements, to eliminate duplicate rows from the result set. It ensures that the query returns only unique rows by removing duplicates. This can be particularly useful when you have multiple records that are identical in one or several columns and you want to retrieve only distinct combinations.

Here's a brief explanation of how it works:

  • When you use DISTINCT, it applies to all columns that are listed after the SELECT keyword.
  • If you specify multiple columns, DISTINCT will return unique combinations of values in these columns. That means a row is only considered a duplicate if all the column values listed are identical to those in another row.
  • DISTINCT is often used in queries where data normalization might lead to duplication in the result set, and you're interested in getting a list of unique values.

For example, if you have a database of books and you execute the query SELECT DISTINCT author FROM books;, it will return a list of authors, with each author listed only once, regardless of how many books they have in the database.

In summary, DISTINCT is a powerful tool in SQL for ensuring that the data returned in your query is unique across the specified columns.

Here's the basic syntax:

SQL
SELECT DISTINCT column_names
FROM table_name
WHERE condition
ORDER BY column;

In this syntax:

  • DISTINCT: This keyword ensures that the query returns only distinct (different) values.
  • column_names: These are the columns you want to retrieve.
  • table_name: The name of the table from where you're retrieving the data.
  • condition: Optional. It filters the records.
  • ORDER BY: Optional. It sorts the result set.

Examples of MySQL DISTINCT Clause

Let's consider a table named books with columns author, title, and genre.

Example 1: Selecting Unique Values from a Single Column

Suppose we want to list all unique genres:

SQL
SELECT DISTINCT genre
FROM books
ORDER BY genre;

Output Example:

Code
+---------+
| genre   |
+---------+
| Fiction |
| Mystery |
| Sci-Fi  |
| Fantasy |
...

Example 2: Removing Duplicates with DISTINCT

Without DISTINCT, if we select genres:

SQL
SELECT genre
FROM books
ORDER BY genre;

You might get duplicates:

Code
+---------+
| genre   |
+---------+
| Fiction |
| Fiction |
| Mystery |
| Sci-Fi  |
| Sci-Fi  |
...

Example 3: DISTINCT with Multiple Columns

To get unique combinations of author and genre:

SQL
SELECT DISTINCT    author, genre
FROM    books
ORDER BY     author,     genre;

Output Example:

Code
+---------------+---------+
| author        | genre   |
+---------------+---------+
| John Doe      | Fiction |
| John Doe      | Mystery |
| Jane Smith    | Sci-Fi  |
| Jane Smith    | Fantasy |
...

Handling NULL Values with DISTINCT

If a column has NULL values, DISTINCT will consider all NULLs as equal and return only one NULL.

DISTINCT with NULL Example

If genre has NULL values:

SQL
SELECT DISTINCT genre
FROM books;

Output Example:

Code
+---------+
| genre   |
+---------+
| NULL    |
| Fiction |
| Mystery |
...

Summary

  • Use DISTINCT in the SELECT statement to remove duplicate rows.
  • Works with single or multiple columns.
  • Treats NULL values as identical, returning only one NULL.