Database distinct, union, exists and other technical summary

First,MySQL distinct technical point

1, create table:

CREATE TABLE t1 (ID varchar, name varchar);

2. Insert Data :

INSERT into T1 values (1,a);

INSERT into T1 values (2,B);

INSERT into T1 values (3,C);

INSERT into T1 values (4,C);

INSERT into T1 values (5,B);

3, the query does not repeat name:

Select distinct name from T1, get a,b,C

4, query the name and ID fields:

Select DISTINCT name,ID from T1, to get all the data

That is, duplicate records are excluded only if both name and ID are the same

5, place the distinct after the ID :

Select ID, distinct name from table, results Error!

6, get each record that name does not repeat:

Select *,count (distinct name) from T1 GROUP by name

II. Union UNION ALL distinct technical points

1, Union Merge and then distinct

2, UNION All is not distinct after merging

3, (a) union = UNION ALL + distinct,(b) union! = DISTINCT + UNION ALL

4, The difference between the understanding of 3 (a) and (b) lies in the order of execution

5, only when the data magnitude is small (such as thousands of below), there is no obvious query efficiency; When the data magnitude is large, the temporary table merge policy should be used to replace Union, replace with exists DISTINCT

Third, replace the distinct technical point with exists

1, Example:

SELECT DISTINCT dept_no,dept_name from DEPT D, EMP E whered.   Dept_no = E.dept_no; Low Efficiency

Select Dept_no,dept_name from DEPT D where EXISTS (select ' X ' from EMP E where e.dept_no = D.dept_no); Efficient

Select Dept_no,dept_name from DEPT, (select Distinctdept_no from EMP) TMP WHERE DEPT. Dept_no=tmp.  Dept_no; Avoid using

2, Parse:

when you submit a message that contains a pair of multiple tables ( such as departmental and employee tables ) query , avoid using DISTINCT in the SELECT clause . you can generally consider using EXIST Replace , EXISTS makes the query faster because the RDBMS core module returns results immediately after the subquery's conditions are met .

with EXISTS can indeed replace DISTINCT, but the scenario above is only suitable for Dept_no as a unique primary key.

3, reference, if you want to remove duplicate records, you need to refer to the following methods:

SELECT * from emp where Dept_no EXISTS (select Max (dept_no) from DEPT D, EMP E where e.dept_no = D.dept_no GROUP by D.dept_ NO)

four, in and exists efficiency technical points

Table A(small table), table B(large table)

Select *from B where cc in (select CC from A)

in this statement, the first The CC is found in a table , and then according to CC again in B to find the relevant cc because cc a is far less than b the CC of the table so you can save time.

Select *from B exists (select cc from A where cc=b.cc)

This sentence is first from b in the table to find cc and then in a table to find the relevant cc because the cc of B is far more than the cc of a table so it's a waste of time.

Summary : outer inner small with in, outside small inside big with EXISTS

This article is from the "Aeaiesb" blog, make sure to keep this source http://aeaiesb.blog.51cto.com/9300472/1566697

Database distinct, union, exists and other technical summary