SQL handles duplicate data

SQL handles duplicate data

Sometimes, the same records exist in the data table. When obtaining records in a table, it is clearly more meaningful to have unique records than to obtain duplicate records.

The SQL DISTINCT keyword, which we discussed earlier, can be used with SELECT statements to eliminate all duplicate records and return only unique records.

Grammar:

The basic syntax for using the DISTINCT keyword to eliminate duplicate records is as follows:

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

Example:

Consider the CUSTOMERS table, which is recorded as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

First, let's look at how the SELECT statement returns duplicate salary records:

SQL> SELECT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

Running the above statement results in two records with SALARY of 2000, which are duplicate records from the original data table:

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+

Now let's use the DISTINCT keyword in the SELECT query above and see what you get:

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
     ORDER BY SALARY;

The above statement will produce the following result, and there will be no duplicate entries again:

+----------+
| SALARY   |
+----------+
|  1500.00 |
|  2000.00 |
|  4500.00 |
|  6500.00 |
|  8500.00 |
| 10000.00 |
+----------+