How to use MySQL DISTINCT to avoid duplication?

Syntax

When querying data from a table that might contain duplicate records, you can include DISTINCT to your SELECT statement to eliminate all the duplicated data.

Here is the generic syntax:

SELECT DISTINCT
    col_name
FROM
    table_name;

Example

We have table sales:

item	customer_name
book	Alex
pen	Bob
book	Alex
book	Jim
shoes	Jim
pen	Bob
backpack	Bob
book	Bob
wallet	Alex
wallet	Alex
book	Bob
backpack	Jim

There were probably some duplicated records, so use DISTINCT to select unique records only:

SELECT DISTINCT
    *
FROM
    sales;

The result without duplication:

item	customer_name
book	Alex
pen	Bob
book	Jim
shoes	Jim
backpack	Bob
book	Bob
wallet	Alex
backpack	Jim

To select unique value from the column item:

SELECT DISTINCT
    item
FROM
    sales;

The result is:

item
book
pen
shoes
backpack
wallet

To select unique item from purchased by the customer Alex:

SELECT DISTINCT
    *
FROM
    sales
WHERE
    customer_name = 'Alex';

The result is:

item	customer_name
book	Alex
wallet	Alex

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS