How to use distinct in MySQL

First, basic use

distinctis generally used to remove duplicate records in the query results, and this statement in select , insert , delete and update can only be used in, select the specific syntax is as follows:

select distinct expression[,expression...] from tables [where conditions];

• 1

The expressions here can be multiple fields. All of the actions in this article are for the following sample tables:

CREATETABLE' NewTable ' (' ID 'int11)NotNULL Auto_increment,' Name 'varchar60Lnull default NULL,  ' country ' varchar (50) null default NULL,  ' province ' varchar (30) null default NULL,  City ' varchar (30)  NULL default null, PRIMARY key ( ' id ')) engine=innodb;         

1.1 Operations on only one column

This operation is the most common and simple, as follows:

select distinct country from person

• 1

The results are as follows:

1.2 Working with multiple columns

select distinct country, province from person

• 1

The results are as follows:

As you can see from the above example, when distinct applied to more than one field, its scope is applied to all fields following it, not just one field next to it, but distinct only to the front of all fields, and the following statement is incorrect:

SELECT country, distinct province from person; // 该语句是错误的

• 1

The following error is thrown:

[ERR] 1064-you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' DISTINCT-province from PE Rson ' at line 1

1.3 for NULLThe processing

As can be seen from 1.1 and 1.2, the distinct pair NULL is not filtered, that is, the returned result contains a NULL value.

1.4 and ALLcannot be used at the same time

By default, all results are returned at the time of the query, and the all statement is used, which distinct corresponds to the following:

select all country, province from person

• 1

The results are as follows:

1.5 and distinctrowSynonymous

select distinctrow expression[,expression...] from tables [where conditions];

• 1

This statement has the same effect as distinct.

1.6 Pairs *The processing

*Represents an entire column, using distinct to * manipulate

sql 
select DISTINCT * from person 


Equivalent

select DISTINCT id, `name`, country, province, city from person;

How to use distinct in MySQL