{"id":169,"date":"2019-03-26T20:39:11","date_gmt":"2019-03-27T04:39:11","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=169"},"modified":"2020-06-29T22:22:05","modified_gmt":"2020-06-30T06:22:05","slug":"db2-select-distinct","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-select-distinct\/","title":{"rendered":"Db2 SELECT DISTINCT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 SELECT DISTINCT<\/code> to prevent duplicate rows returned by a query.<\/p>\n\n\n\n

Introduction to Db2 SELECT DISTINCT<\/h2>\n\n\n\n

Sometimes, you want to select distinct values from one or more columns of a table. To do this, you use the DISTINCT<\/code> keyword in the SELECT<\/a><\/code> clause as follows:<\/p>\n\n\n

SELECT<\/span> \n    DISTINCT<\/span> column_name\nFROM<\/span>\n    table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The DISTINCT<\/code> keyword appears after the SELECT<\/code> keyword but before any column or expression in the select list. The query above returns distinct values in the column_name<\/code> from the table_name<\/code>.<\/p>\n\n\n\n
If you have multiple column names listed after the DISTINCT<\/code> keyword like the following query:<\/p>\n\n\n
SELECT<\/span>\n    DISTINCT<\/span> \n        column_name1, \n        column_name2, ...\nFROM<\/span>\n    table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The DISTINCT<\/code> keyword is applied to all columns. It means that the query will use the combination of values in all columns to evaluate the distinction.<\/p>\n\n\n\n
In case a column contains multiple NULL<\/code> values, DISTINCT<\/code> will keep only one NULL<\/code> in the result set.<\/p>\n\n\n\n
Db2 SELECT DISTINCT examples<\/h2>\n\n\n\n
We will use the authors<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using Db2 SELECT DISTINCT with one column<\/h3>\n\n\n\n
The following query returns all last names of authors from the authors<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    last_name\nFROM<\/span> \n    authors<\/span>\nORDER<\/span> BY<\/span> \n    last_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
As clearly shown in the output, we had many authors with the same last name e.g., Abbott<\/code>, Agans<\/code>, and Albahari<\/code>.<\/p>\n\n\n\n
To get unique author’s last names, you add the DISTINCT<\/code> keyword as shown in the following query:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> \n    last_name\nFROM<\/span> \n    authors<\/span>\nORDER<\/span> BY<\/span> \n    last_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
As you can see clearly from the output, the DISTINCT<\/code> operator keeps one value for each group of duplicates.<\/p>\n\n\n\n
2) Using Db2 SELECT DISTINCT with NULL values<\/h3>\n\n\n\n
The middle_name<\/code> column of the authors<\/code> table contains many rows with NULL<\/code> values. When we apply the DISTINCT<\/code> to the middle_name<\/code> column, only one instance of NULL<\/code> is included in the result set as shown in the result set of the following query;<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> \n    middle_name\nFROM<\/span> \n    authors<\/span>\nORDER<\/span> BY<\/span> \n    middle_name DESC<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
3) Using Db2 SELECT DISTINCT with multiple columns<\/h3>\n\n\n\n
Let’s set up a new table for the demonstration.<\/p>\n\n\n\n
First, create a new table<\/a> named book_inventories<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> book_inventories\n(\n    book_id  INT<\/span> NOT<\/span> NULL<\/span>, \n    store_id INT<\/span> NOT<\/span> NULL<\/span>, \n    quantity INT<\/span> NOT<\/span> NULL<\/span>, \n    PRIMARY KEY<\/span>(book_id, store_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> book_inventories(book_id, store_id, quantity)\nVALUES<\/span>(100<\/span>, 1<\/span>, 15<\/span>);\nINSERT<\/span> INTO<\/span> book_inventories(book_id, store_id, quantity)\nVALUES<\/span>(100<\/span>, 2<\/span>, 20<\/span>);\nINSERT<\/span> INTO<\/span> book_inventories(book_id, store_id, quantity)\nVALUES<\/span>(200<\/span>, 1<\/span>, 25<\/span>);\nINSERT<\/span> INTO<\/span> book_inventories(book_id, store_id, quantity)\nVALUES<\/span>(200<\/span>, 2<\/span>, 30<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * \nFROM<\/span> book_inventories;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
If you use the DISTINCT<\/code> keyword on the book_id<\/code>, you will get two values 100 and 200 as shown in the output of the following query:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> \n    book_id\nFROM<\/span> \n    book_inventories;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
BOOK_ID     \n----------- \n100         \n200<\/code><\/span><\/pre>\n\n\n
However, when you add the store_id<\/code> column to the query as follows:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> \n    book_id, \n    store_id\nFROM<\/span> \n    book_inventories;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
BOOK_ID     STORE_ID    \n----------- ----------- \n100         1           \n100         2           \n200         1           \n200         2<\/code><\/span><\/pre>\n\n\n
It returns the distinct values of both book_id<\/code> and store_id<\/code>.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the Db2 SELECT DISTINCT<\/code> to remove duplicate rows in the result set of a query.<\/p>\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
Third, query<\/a> data from the book_inventories<\/code> table:<\/p>\n\n\n
Second, insert<\/a> some rows into the table:<\/p>\n\n\n
If you want to select distinct values of some columns in the select list, you should use the GROUP BY<\/a><\/code> clause.<\/p>\n\n\n\n