The usage of distinct in SQL and the meaning of the left join query

• 1. Acting on a single row
• 2. Acting on multiple columns
• 3.COUNT Statistics
• 4.distinct must be placed at the beginning
• 5. Other

In the table, duplicate values may be included. That's not a problem, but sometimes you might want to just list different values (distinct). Keyword DISTINCT is used to return only different values.

Table A:

Table B:

1. Acting on a single row

Select distinct name from A

The following results are performed:

2. Acting on multi-column Example 2.1

Select DISTINCT name, ID from A

The following results are performed:

In fact, it is the same way that access and SQL Server support it, depending on the name and ID two fields.

Example 2.2

Select distinct Xing, Ming from B

Returns the following results:

The returned result is two lines, indicating that distinct is not the Xing and Ming two columns "string concatenation" after the heavy, but respectively for the Xing and Ming columns.

3.COUNT Statistics

Select COUNT (distinct name) from A;  --the number of name to go back in the table, SQL Server support, and access does not support

Count is not able to count multiple fields, and the following SQL cannot be run in SQL Server and access.

Select COUNT (distinct name, id) from A;

To use it, use nested queries, as follows:

Select COUNT (*) from (select Distinct Xing, name from B) as M;

4.distinct must be placed at the beginning

Select ID, distinct name from A;   --The error will be indicated because the distinct must be placed at the beginning

5. Other

The field selected in the distinct statement can only be a field specified by distinct, and other fields are not possible. For example, if Table A has a memo column, if you want to get Distinc name and the corresponding Memo field, it is not possible to go directly through distinct. However, there are other ways to implement a discussion of how SQL Server will stitch up multiple lines of content into one row

The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table. In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid). The low-record of table B is null.

The usage of distinct in SQL and the meaning of the left join query