union of two sets in mysql

Common scenarios of MySQL: getting the intersection and difference of two data sets

step

1. The structures of the two collections must be consistent, with corresponding number of fields and field types

2. Combine the two sets using the UNION ALL keyword. The result here is all sets with duplicates.

3. GROUP BY id for all the above sets

4. Finally, HAVING COUNT(id)=1. If it is equal to 1, it means that it only appears once, so this is a difference set. If it is equal to 2, then it is an intersection set.

Code Demonstration

Difference

The following sql has obvious problems, but this is just a hint.

There is no need to use intersection and difference to query from a table. The conditions can be combined together to query directly. It's good to understand the meaning.

The following SQL means to find the ID, code and name of all non-technical employees

SELECT a.* FROM(
    SELECT id,code,name FROM test_emp
    UNION ALL
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)a GROUP BY a.id HAVING COUNT(a.id)=1

Intersection

The following SQL means to find all employees in the technical department who are older than 25

SELECT a.* FROM(
    SELECT id,code,name FROM test_emp WHERE age>25
    UNION ALL
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)a GROUP BY a.id HAVING COUNT(a.id)=2

Union

The following SQL means to find all employees in the technical department and employees older than 30

Union can automatically remove duplicate content and obtain a non-duplicate result set

SELECT a.* FROM(
    SELECT id,code,name FROM test_emp WHERE age>25
    UNION
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)

Intersection, union, difference, left join, right join in mysql

I have been learning MySQL for a month. During this month, I have performed some basic operations on the data table according to the needs. In this process, I often used left join, right join, intersection, difference set, etc. Now I will summarize its basic operations.

Data Source:

Table 1:

id name sex age
1 mike1 male 34
1 mike2 Male 23
1 mike3 Female 24
2 mike1 Male 46
2 mike2 Male 35
2 mike3 Male 42
2 mike4 male 62
3 mike1 Female 45
4 mike5 male 72
5 mike4 Female 23

Table 2:

id school
1 Peking University
2 Tsinghua University
3. Harvard University
7. MIT

Left Join:

Joins tables based on an equality condition. This experiment is to perform a left join when the id is the same

code:

select a.*,b.school 
FROM 
(SELECT * FROM mike1.test001) a
LEFT JOIN
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id

The result is as follows: Table 1 is used as the basis to connect Table 2 and match the same ID number

Right join:

Take Table 2 as the root and right-join it through the fields with the same id.

code:

select a.*,b.school 
FROM 
(SELECT * FROM mike1.test001) a
right JOIN
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id

result:

Do you think there is a problem with the result or not?

Intersection:

By using the same ID number, inner join is performed on Table 1 and Table 2, taking the same parts and omitting the different parts.

code:

select a.*,b.school 
FROM 
(SELECT * FROM mike1.test001) a
inner JOIN
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id

result:

Difference:

Difference sets are used quite frequently, which are similar to the set operations such as intersection, combination and union that we learned in mathematics.

Code 1: Get the same part of Table 2 as Table 1, which is the second method of getting the intersection mentioned above.

select a.id,a.name,a.sex,a.age,b.school 
FROM 
(SELECT * FROM mike1.test001) a
LEFT OUTER join
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id
WHERE b.id IS NOT null

result:

code2: Take the difference between the id in table 1 and the id in table 2, and finally list the data.

select a.id,a.name,a.sex,a.age,b.school 
FROM 
(SELECT * FROM mike1.test001) a
LEFT OUTER join
(SELECT id,school FROM mike1.test003 ) b
ON a.id=b.id
WHERE b.id IS null

result: