Handling copy - HANDLING DUPLICATE in SQL

There is a situation where data has multiple duplicate records in a table and you want to retrieve a single record rather than retrieving all duplicate records. To handle this situation, use the DISTINCT keyword in SQL in conjunction with the SELECT statement, you will remove all duplicate records and retrieve only records.

Syntax

The basic syntax of the DISTINCT keyword to remove duplicates is as follows:

 SELECT DISTINCT cot1, cot2. cotN 
 FROM ten_bang 
 WHERE [dieu_kien] 

Examples of how to handle Duplicate in SQL

Considering the table NHANVIEN has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

First, we see how the SELECT query returns a copy of LUONG:

 SQL> SELECT SALARY FROM CUSTOMERS 
  ORDER BY SALARY; 

In the resulting result, LUONG 2000 appears twice, and is a copy from the original table.

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

Now, use the DISTINCT keyword with the SELECT query and see the result:

 SQL> SELECT DISTINCT SALARY FROM CUSTOMERS 
  ORDER BY SALARY; 

In the results, you will not see any copies.

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

Previous post: CLONE TABLE in SQL

Next lesson: Query SUBQUERY child in SQL