- 4 years ago
- Zaid Bin Khalid
- 2548 Views
-
3
In this session, you will learn how to remove duplicate values from a result set in SQL.
SQL DISTINCT Clause.
SELECT DISTINCT returns only distinct (different) values and eliminates duplicate records from the results. The SQL DISTINCT Clause only support single column, whereas the DISTINCT Clause also be used with the combination of: COUNT, AVG, MAX, etc., in SQL.
Retrieving Distinct Values.
When you are getting data from a database table, the result set may show duplicate rows or values as a result. If you like to remove these duplicate values you can specify the keyword as shown below:
Syntax.
The DISTINCT clause is used to remove duplicate rows from the result set:
SELECT DISTINCT column_list FROM table_name;
Now you can see, column_list is a comma-separated list of column or field names of a database table (e.g. name, age, country, etc.) whose values you want to fetch.
Let’s check out some examples that demonstrate how it works.
Let suppose we have a customer table in our database with the following records.
+---------+--------------------+-----------+-------------+
| cust_id | cust_name | city | postal_code |
+---------+--------------------+-----------+-------------+
| 1 | Maria Anders | Berlin | 12209 |
| 2 | Fran Wilson | Madrid | 28023 |
| 3 | Dominique Perrier | Paris | 75016 |
| 4 | Martin Blank | Turin | 10100 |
| 5 | Thomas Hardy | Portland | 97219 |
| 6 | Christina Aguilera | Madrid | 28001 |
+---------+--------------------+-----------+-------------+
Let execute the following statement which returns all the rows from the city column of this table.
SELECT city FROM customers;
After the execution of the statement you will have the output result such as:
+-----------+
| city |
+-----------+
| Berlin |
| Madrid |
| Paris |
| Turin |
| Portland |
| Madrid |
+-----------+
If you see the output carefully, it will show the city “Madrid” appears two times in our result set, which is not good. Well now, let’s fix this problem.
Removing Duplicate Data.
The following statement uses DISTINCT to generate a list of all cities in the customer’s table.
SELECT DISTINCT city FROM customers;
After you execute the statement, you will get the following output result, as shown.
+-----------+
| city |
+-----------+
| Berlin |
| Madrid |
| Paris |
| Turin |
| Portland |
In the above result set, there is no duplicate values return.
Note: In SQL, the DISTINCT CLAUSE only considers one NULL value and removes other multiples Null values from the column to get the result because of DISTINCT treats all the NULL values as the same value.
- 4 years ago
- Zaid Bin Khalid
- 2548 Views
-
3