{"id":13727,"date":"2023-12-29T00:21:14","date_gmt":"2023-12-29T07:21:14","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-distinct\/"},"modified":"2024-06-09T20:55:35","modified_gmt":"2024-06-10T03:55:35","slug":"mysql-distinct","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-distinct\/","title":{"rendered":"MySQL DISTINCT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DISTINCT<\/code> clause in the SELECT<\/code> statement to eliminate duplicate rows in a result set.<\/p>\n\n\n\n

Introduction to MySQL DISTINCT clause<\/h2>\n\n\n\n

When querying data from a table, you may get duplicate rows. To remove these duplicate rows, you use the DISTINCT<\/code> clause in the SELECT<\/a><\/code> statement.<\/p>\n\n\n\n

Here’s the syntax of the DISTINCT<\/code> clause:<\/p>\n\n\n

SELECT<\/span> DISTINCT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nWHERE<\/span> \n    search_condition\nORDER<\/span> BY<\/span> \n    sort_expression;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify one or more columns that you want to select distinct values after the SELECT DISTINCT<\/code> keywords.<\/p>\n\n\n\n
If you specify one column, the DISTINCT<\/code> clause will evaluate the uniqueness of rows based on the values of that column.<\/p>\n\n\n\n
However, if you specify two or more columns, the DISTINCT<\/code> clause will use the values of these columns to evaluate the uniqueness of the rows.<\/p>\n\n\n\n
When executing the SELECT<\/code> statement with the DISTINCT<\/code> clause, MySQL evaluates the DISTINCT<\/code> clause after the FROM<\/code>, WHERE<\/code>, and SELECT<\/code> clause and before the ORDER BY<\/code> clause:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL DISTINCT clause examples<\/h2>\n\n\n\n
We’ll use the employees<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
First, select the last names from the employees<\/code> table using the following SELECT<\/code> statement:<\/p>\n\n\n
SELECT<\/span> \n    lastname\nFROM<\/span>\n    employees\nORDER<\/span> BY<\/span> \n    lastname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+-----------+\n| lastname  |\n+-----------+\n| Bondur    |\n| Bondur    |\n| Bott      |\n| Bow       |\n| Castillo  |\n| Firrelli  |\n| Firrelli  |\n| Fixter    |\n....\n| Jones     |\n\n| Patterson |\n| Patterson |\n| Patterson |\n| Thompson  |\n...\n+-----------+\n23 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
As shown clearly in the output, some employees have the same last names e.g.,Bondur,Firrelli<\/code> .<\/p>\n\n\n\n
Second, select unique last names by adding the DISTINCT<\/code> clause like this:<\/p>\n\n\n
SELECT<\/span> \n    DISTINCT<\/span> lastname\nFROM<\/span>\n    employees\nORDER<\/span> BY<\/span> \n    lastname;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As you can see clearly from the output, the DISTINCT<\/code> clause removes the duplicate last names from the result set.<\/p>\n\n\n
+-----------+\n| lastname  |\n+-----------+\n| Bondur    |\n| Bott      |\n| Bow       |\n| Castillo  |\n| Firrelli  |\n\n...\n| Nishi     |\n| Patterson |\n| Thompson  |\n| Tseng     |\n| Vanauf    |\n+-----------+\n19 rows in set (0.01 sec)\n<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL DISTINCT and NULL values<\/h2>\n\n\n\n
When you specify a column that has NULL<\/a><\/code> values in the DISTINCT<\/code> clause, the DISTINCT<\/code> clause will keep only one NULL<\/code> value because it considers all NULL<\/code> values are the same.<\/p>\n\n\n\n
For example, the state column in the customers<\/code> table has NULL<\/code> values.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
When you use the DISTINCT<\/code> clause to query the states, you will see distinct states and NULL<\/code> as follows:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> state\nFROM<\/span> customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------+\n| state         |\n+---------------+\n| NULL          |\n| NV            |\n| Victoria      |\n| CA            |\n| NY            |\n| PA            |\n...\n| Co. Cork      |\n| Pretoria      |\n| NH            |\n| Tokyo         |\n+---------------+\n19 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL DISTINCT with multiple columns<\/h2>\n\n\n\n
When you specify multiple columns in the DISTINCT<\/code> clause, the DISTINCT<\/code> clause will use the combination of values in these columns to determine the uniqueness of the row in the result set.<\/p>\n\n\n\n
For example, to get a unique combination of city and state from the customers<\/code> table, you use the following query:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n    state, city\nFROM<\/span>\n    customers\nWHERE<\/span>\n    state IS<\/span> NOT<\/span> NULL<\/span>\nORDER<\/span> BY<\/span> \n    state, \n    city;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------+----------------+\n| state         | city           |\n+---------------+----------------+\n| BC            | Tsawassen      |\n| BC            | Vancouver      |\n| CA            | Brisbane       |\n| CA            | Burbank        |\n| CA            | Burlingame     |\n| CA            | Glendale       |\n| CA            | Los Angeles    |\n| CA            | Pasadena       |\n| CA            | San Diego      |\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Without the DISTINCT<\/code> clause, you will get the duplicate combination of state and city as follows:<\/p>\n\n\n
SELECT<\/span> \n    state, city\nFROM<\/span>\n    customers\nWHERE<\/span>\n    state IS<\/span> NOT<\/span> NULL<\/span>\nORDER<\/span> BY<\/span> \n    state , \n    city;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
+---------------+----------------+\n| state         | city           |\n+---------------+----------------+\n| BC            | Tsawassen      |\n| BC            | Vancouver      |\n| CA            | Brisbane       |\n| CA            | Burbank        |\n..\n| CA            | San Francisco  |\n| CA            | San Francisco  |\n...\n| MA            | Boston         |\n| MA            | Boston         |\n| MA            | Brickhaven     |\n| MA            | Brickhaven     |\n| MA            | Brickhaven     |\n...\n| NY            | NYC            |\n| NY            | NYC            |\n| NY            | NYC            |\n| NY            | NYC            |\n| NY            | NYC            |\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL DISTINCT<\/code> clause to remove duplicate rows from the result set returned by the SELECT<\/code> clause.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n