How to use SQL deduplication and distinct

There are two ways to deduplicate using DISTINCT in SQL: SELECT DISTINCT: Only the unique values ​​of the specified columns are preserved, and the original table order is maintained. GROUP BY: Keep the unique value of the grouping key and reorder the rows in the table.

Two ways to use DISTINCT to deduplicate in SQL

In SQL, the DISTINCT operator is used to remove duplicate rows from the result set, retaining only unique values. There are two ways to use DISTINCT to deduplicate:

Method 1: Use SELECT DISTINCT

This method uses DISTINCT in the SELECT statement to deduplicate. The syntax is as follows:

 <code class="sql">SELECT DISTINCT 列名1, 列名2, ... FROM 表名;</code>

For example:

 <code class="sql">SELECT DISTINCT 姓名, 年龄FROM 学生;</code>

This query returns a new table with a unique combination of name and age.

Method 2: Use GROUP BY

This method achieves deduplication by using the GROUP BY clause. The syntax is as follows:

 <code class="sql">SELECT 列名1, 列名2, ... FROM 表名GROUP BY 列名1, 列名2, ...;</code>

For example:

 <code class="sql">SELECT 姓名, MIN(年龄) AS 最小年龄FROM 学生GROUP BY 姓名;</code>

This query returns a new table grouped by name and has the minimum age. Since the name is a grouping key, there are only unique names in the new table.

The difference between the two

The main difference between DISTINCT and GROUP BY is:

• DISTINCT retains all unique values ​​for the specified column, while GROUP BY retains only unique values ​​for the grouping key.
• DISTINCT preserves the order of the original table, while GROUP BY reorders the rows in the table.

When choosing to use DISTINCT or GROUP BY, it needs to be decided based on the specific results to be achieved.

The above is the detailed content of How to use SQL deduplication and distinct. For more information, please follow other related articles on the PHP Chinese website!