The implementation of MySQL intersections and differential sets

In MySQL, only Union (union) collection operations are supported, while intersect and differential sets Excelt are not. S o how do you implement intersections and differentials in MySQL?

Generally in MySQL, we can indirectly implement intersections and differentials through in and not in, of course, there are some limitations, in the face of a small amount of data can also, but the data volume will become less efficient.

Create table1

/*DDL 信息*/------------  
  
CREATE TABLE `t1` (  
  `id` int(11) NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  `age` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

Create table2

/*DDL 信息*/------------  
  
CREATE TABLE `t2` (  
  `id` int(11) NOT NULL,  
  `name` varchar(20) DEFAULT NULL,  
  `age` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

Insert

INSERT INTO t1 VALUES(1,'小王',10);  
INSERT INTO t1 VALUES(2,'小宋',20);  
INSERT INTO t1 VALUES(3,'小白',30);  
INSERT INTO t1 VALUES(4,'hello',40);  
  
  
INSERT INTO t2 VALUES(1,'小王',10);  
INSERT INTO t2 VALUES(2,'小宋',22);  
INSERT INTO t2 VALUES(3,'小肖',31);  
INSERT INTO t2 VALUES(4,'hello',40);  

SELECT t1.* FROM t1   
  
id  name    age  
1   小王      10  
2   小宋      20  
3   小白      30  
4   hello   40  

SELECT t2.* FROM t2   
  
id  name    age  
1   小王  10  
2   小宋  22  
3   小肖  31  
4   hello   40  

Use not in for differential sets, but inefficiently

SELECT t1.* FROM t1   
WHERE   
name NOT IN  
(SELECT name FROM t2)  
  
id  name    age  
3   小白      30  

SELECT t1.id, t1.name, t1.age  
FROM t1   
LEFT JOIN t2   
ON t1.id = t2.id  
WHERE t1.name != t2.name  
  
   OR t1.age != t2.age;  
  
  
id  name    age  
2   小宋      20  
3   小白      30  

Ask for intersections, at which point only id name age is the same as all are eligible.

SELECT  id,  NAME,  age, COUNT(*)  
    FROM (SELECT id, NAME, age  
        FROM t1  
          
        UNION ALL  
          
        SELECT id, NAME, age  
        FROM t2  
        ) a  
    GROUP BY id, NAME, age  
    HAVING COUNT(*) > 1  
      
    id  NAME    age COUNT(*)  
    1   小王      10  2  
    4   hello   40  2  

The difference between union all and union

Union and UNION ALL both have the ability to assemble two results into one, but the two keywords differ in terms of usage and efficiency.

On use:

1. Treatment of duplicate results: UNION filters out duplicate records after table links, while Union All does not remove duplicate records.

2. Processing of sorting: Union will sort in the order of fields; UNION ALL will only return after combining the two results, and will not be sorted.

Efficiency:

Union ALL is much more efficient than UNION in terms of efficiency, so UNION ALL is recommended if you can confirm that the merged two result set does not contain duplicate data and does not need to be sorted.

Related reading:

Migration scenarios for MySQL in different scenarios

MySQL FAQ series finishing

Original address: https://blog.csdn.net/mine_song/article/details/70184072