The MySQL/MariaDB ‘DISTINCT’ Statement, With Examples

MySQL Distinct

The MySQL DISTINCT operator returns only unique values from a column from a database table; duplicates are not returned.  Here’s how to use it.

The DISTINCT operator can be useful in many scenarios; for example, you may want to generate a list of countries you have shipped to, or you may want to provide a drop-down menu containing unique product options for the user to select.

Example Usage of MySQL DISTINCT

Examples will use the following table:

table_orders:

order_id shipping_country product_category
1 Australia soap
2 China candles
3 India candles
4 Indonesia soap
5 Australia soap
6 China soap
7 Australia candles

To get a list of countries that have been shipped to, without duplicates, run the following query:

SELECT DISTINCT shipping_country FROM table_orders;

…which will result in:

shipping_country
Australia
China
India
Indonesia

Counting Unique Values in a Columns

If you want to know how many distinct/unique values there are in a table’s column:

SELECT COUNT(DISTINCT shipping_country) FROM table_orders;

…which will result in:

COUNT(DISTINCT shipping_country)
4

Using DISTINCT with Multiple Columns

You can use the DISTINCT statement to SELECT from multiple columns – the combination of the values will be used to generate the unique results:

SELECT DISTINCT
    shipping_country, product_category
FROM
    table_orders
;

…which will return:

shipping_country product_category
Australia soap
China candles
India candles
Indonesia soap
China soap
Australia candles

As you can see, only unique combinations of the two columns passed to the DISTINCT statement are shown.

If you’re using MySQL with PHP to build apps – why not consider a PHP framework like Laravel?

SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *