Orthogonal difference in MySQL

MySQL only provides the union, does not provide the difference set, and the intersection, but we can use the Union to achieve the intersection and the difference, the following is the implementation method:

First create two tables:

ERROR 1064 (42000):mysql> create table K1 (    name varchar (20)); Query OK, 0 rows affected (0.05 sec) mysql> insert into K1 value (' Zhang '), (' Three '), (' Wind '); Query OK, 3 rows affected (0.02 sec)

Mysql> CREATE TABLE K2 (    name varchar (20)); Query OK, 0 rows affected (0.04 sec)
mysql> INSERT into K2 value (' Zhang '), (' Three '), (' King '), (' Four '), (' Li '); Query OK, 5 rows affected (0.01 sec)

Search Show:

Mysql> SELECT * FROM k1;+------+| Name |+------+| Zhang   | | three   | | Wind   |+------+3 rows in Set (0.00 sec) mysql> SELECT * from k2;+------+| name |+------+| Zhang   | | three 
   | | Wang   | | four   | | Li   |+------+5 rows in Set (0.00 sec)

1. and union

Mysql> select * from K1,    union,    SELECT * from k2;+------+| name |+------+| Zhang   | | three   | | Wind 
   
    || Wang   | | four   | | Li   |+------+6 rows in Set (0.00 sec)
   

2. and (thought is: Query two tables after merging, Count is greater than 1)

Mysql> SELECT * FROM (SELECT * To K1 UNION ALL SELECT * from K2) as a group by name have count (name) >1;+------+ | Name |+------+| Sam   | | Zhang   |+------+2 rows in Set (0.00 sec) Some points to note: 1. The table that we use to show the subquery after the from is required to have an alias, and here I am set to A2. Use Union and so on to show the distinct, here we have to show all, so we have to use the Union All3. After querying from a federated table, group by name, then filter count less than 2 for the results we want

3. Poor (thought: take one of the non-repeating entries, and then find the reduced tables)

Mysql> SELECT * FROM (SELECT * To K1 UNION ALL SELECT * from K2) as a where name in (select name from K2) GROUP by NA Me having count (name) = 1;+------+| Name |+------+| Lee   | | four   | | Wang   |+------+3 rows in Set (0.00 sec) Some points to note: 1. First, query all federated tables for Count equals 1 in 2. Then find the 3 that belongs to K2. So the result of this query is K2-K1

　　

Orthogonal difference in MySQL