Very often we might retrieve duplicate values from our records. In order to "see" only distinct records we can use the DISTINCT operator. The basic syntax for DISTINCT operator is shown in the example:

Syntax

SELECT DISTINCT column_names FROM table_name WHERE where_conditions;

And of course in a real world example, something like this:

SELECT DISTINCT employee FROM department WHERE rank = `leader`;

It will produce a list of distinct employees that are listed in department table, and are also ranked as leaders.

The main problems with the DISTINCT operator is when it gets applied on NULL values, as the MySQL treats all NULL values as the same. In order to avoid that we should use the IS NULL or IS NOT NULL operators. The example below shows how to apply IS NOT NULL and DISTINCT operators while retrieving (and comparing) more than one column:

SELECT DISTINCT country, state, city FROM customers 

WHERE state IS NOT NULL

ORDER BY country, state, city;