Prerequisite: Create two relational tables:
CREATE TABLE t_blog( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(50), typeId INT ); CREATE TABLE t_type( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) );
The table data are as follows:
1. Acquisition of Public Parts A and B
MySql provides an internally connected way to obtain data in tables A and B that conform to on expressions:
mysql> select * from t_blog b inner join t_type t on b.typeId = t.id; +----+-------------------+--------+----+-------+ | id | title | typeId | id | name | +----+-------------------+--------+----+-------+ | 1 | java Foundation type | 1 | 1 | JAVA | | 2 | java Programming idea | 1 | 1 | JAVA | | 3 | java From Initial to Abandoned | 1 | 1 | JAVA | | 4 | C Language proficiency | 2 | 2 | C | | 5 | C Language from Initial to Abandoned | 2 | 2 | C | | 6 | mysql Basics | 4 | 4 | MYSQL | +----+-------------------+--------+----+-------+ 6 rows in set
Through the internal connection, the "graphical http" data in the blog and the "C++" data in the type table are filtered.
2. A's Unique and AB's Common
Get all the data of A by using left contiguous connection, filter out the data that accords with on expression in table B, and fill NULL with corresponding fields in table B that do not accord with it.
mysql> select * from t_blog b left join t_type t on b.typeId = t.id; +----+-------------------+--------+------+-------+ | id | title | typeId | id | name | +----+-------------------+--------+------+-------+ | 1 | java Foundation type | 1 | 1 | JAVA | | 2 | java Programming idea | 1 | 1 | JAVA | | 3 | java From Initial to Abandoned | 1 | 1 | JAVA | | 4 | C Language proficiency | 2 | 2 | C | | 5 | C Language from Initial to Abandoned | 2 | 2 | C | | 6 | mysql Basics | 4 | 4 | MYSQL | | 7 | graphic http | NULL | NULL | NULL | +----+-------------------+--------+------+-------+ 7 rows in set
Through the left outer link, all data in the left table are queried, the unique "C++" data in the right table is filtered, and the unique "graphical http" data in the left table is filled with NULL in the right table position.
3. B Exclusive and AB Common
In the same way, the right outer link is used to filter out the data that do not meet on condition in table A and query all the data in table B. The fields that do not satisfy in table A will fill NULL.
mysql> select * from t_blog b right join t_type t on b.typeId = t.id; +------+-------------------+--------+----+-------+ | id | title | typeId | id | name | +------+-------------------+--------+----+-------+ | 1 | java Foundation type | 1 | 1 | JAVA | | 2 | java Programming idea | 1 | 1 | JAVA | | 3 | java From Initial to Abandoned | 1 | 1 | JAVA | | 4 | C Language proficiency | 2 | 2 | C | | 5 | C Language from Initial to Abandoned | 2 | 2 | C | | NULL | NULL | NULL | 3 | C++ | | 6 | mysql Basics | 4 | 4 | MYSQL | +------+-------------------+--------+----+-------+ 7 rows in set
All data in the right table are found by right outer link. The unique position of "C++" data in the left table is filled with NULL, and the "graphical http" data in the left table is filtered out.
4, A is unique.
It is known that the unique part of A and the common part of AB will be obtained through the left outer join. In the unique part of A, the position of B table will complement NULL, that is to say, the data of B for NULL in the left outer join result is unique to A.
mysql> select * from t_blog b left join t_type t on b.typeId = t.id where t.name is null; +----+----------+--------+------+------+ | id | title | typeId | id | name | +----+----------+--------+------+------+ | 7 | graphic http | NULL | NULL | NULL | +----+----------+--------+------+------+ 1 row in set
5, B is unique.
Ibid., the right outer link will acquire B's unique and AB's common. In the unique part of B, the position of table A will complement NULL, that is to say, the data of table A as NULL in the left outer link result is B's unique.
mysql> select * from t_blog b right join t_type t on b.typeId = t.id where b.title is null; +------+-------+--------+----+------+ | id | title | typeId | id | name | +------+-------+--------+----+------+ | NULL | NULL | NULL | 3 | C++ | +------+-------+--------+----+------+ 1 row in set
6. Merging Sets
MySql provides the union command merge set and automatically de-duplicates. If there is no match for the opposite table, fill in NULL
mysql> select * from t_blog b left join t_type t on b.typeId = t.id -> union -> select * from t_blog b right join t_type t on b.typeId = t.id; +------+-------------------+--------+------+-------+ | id | title | typeId | id | name | +------+-------------------+--------+------+-------+ | 1 | java Foundation type | 1 | 1 | JAVA | | 2 | java Programming idea | 1 | 1 | JAVA | | 3 | java From Initial to Abandoned | 1 | 1 | JAVA | | 4 | C Language proficiency | 2 | 2 | C | | 5 | C Language from Initial to Abandoned | 2 | 2 | C | | 6 | mysql Basics | 4 | 4 | MYSQL | | 7 | graphic http | NULL | NULL | NULL | | NULL | NULL | NULL | 3 | C++ | +------+-------------------+--------+------+-------+ 8 rows in set
7. Finding the Difference Set
The so-called difference set is the unique union of A and B.
mysql> select * from t_blog b left join t_type t on b.typeId = t.id where t.name is null -> union -> select * from t_blog b right join t_type t on b.typeId = t.id where b.title is null; +------+----------+--------+------+------+ | id | title | typeId | id | name | +------+----------+--------+------+------+ | 7 | graphic http | NULL | NULL | NULL | | NULL | NULL | NULL | 3 | C++ | +------+----------+--------+------+------+ 2 rows in set