SQL DISTINCT Clause

SQL DISTINCT clause can be used to fetch distinct records from a database table, based on the columns specified.

  • The DISTINCT keyword must immediately follow the SELECT keyword in a statement.
  • In the case of multiple NULL values, only one NULL value is returned considering all the NULL values as the same value.
  • It is commonly used to fetch distinct values of a specific column, by mentioning the column next to the DISTINCT keyword on a SELECT statement.

In the previous chapters, we have seen how to limit records returned by a SELECT statement using the TOP and LIMIT clauses.

Now, let's see how to use the DISTINCT clause on a SELECT statement to fetch distinct records.

Syntax

Here is the basic syntax for the DISTINCT clause on a SELECT statement.

  • The column_list is a comma-separated list of table columns.
  • The table_name is the name of the table.
SELECT DISTINCT column_list FROM table_name;

Table Data

Let's consider the below data in the table customers before executing any of the below queries.

customer_id customer_name address city country postal_code
1 Thomas Hardy 11 Chiaroscuro Rd. Portland USA 97219
2 Christina Aguilera Gran Va, 21 Madrid Spain 28001
3 Hanna Moos Forsterstr. 47 Mannheim Germany 68306
4 Frdrique Citeaux 22, place Klber Strasbourg France 67000
5 Laurence Lebihan 45, rue des Bouchers Marseille France 13008

SELECT Without DISTINCT

The below SQL statement returns the list of country values from all the customers selected.

Run this on IDE

SELECT country FROM customers;

After successful execution, the output looks like this, which contains duplicates of the country 'France'.

country
USA
Spain
Germany
France
France

SELECT With DISTINCT

The below SQL statement returns the list of distinct country values from all the customers selected. In case of duplicates, the country code is ignored.

Run this on IDE

SELECT DISTINCT country FROM customers;

After successful execution, the output looks like this, which contains distinct values of country values without including the duplicates.

country
USA
Spain
Germany
France

Other Statements

The DISTINCT clause can only be used on a SELECT statement.

Overall

We now know how to fetch distinct records from a table using the DISTINCT clause.

Related Links