有关PGSQL的INTERSECT和EXCEPT以及在MYSQL中的实现。
一下是表语句。
create table t1 (id serial not null,game_id int not null);
insert into t1(game_id) values (1),(2),(3),(5),(6),(8);
create table t2 (id serial not null,game_id int not null);
insert into t2(game_id) values (1),(2),(6),(8),(10),(4);
PGSQL：
交集，
t_girl=# select game_id from t1 intersect select game_id from t2;
game_id
---------
1
2
6
8
(4 rows)
Time: 0.400 ms
t_girl=# select game_id from t1 where game_id not in (select t1.game_id from t1 left join t2 on t1.game_id = t2.game_id where t2.game_id is null);
game_id
---------
1
2
6
8
(4 rows)
Time: 0.530 ms
t_girl=#
差集，
t_girl=# select game_id from t1 except select game_id from t2;
game_id
---------
3
5
(2 rows)
Time: 0.371 ms
t_girl=# select t1.game_id from t1 left join t2 using(game_id) where t2.game_id is null;
game_id
---------
3
5
(2 rows)
Time: 0.471 ms
MYSQL没有提供这两个关键字，可以用ＬＥＦＴ ＪＯＩＮ来代替。
交集，
mysql> select game_id from t1 where game_id not in (select t1.game_id from t1 left join t2 on t1.game_id = t2.game_id where t2.game_id is null);
+---------+
| game_id |
+---------+
| 1 |
| 2 |
| 6 |
| 8 |
+---------+
4 rows in set (0.01 sec)
mysql>
差集，
mysql> select t1.game_id from t1 left join t2 using(game_id) where t2.game_id is null;
+---------+
| game_id |
+---------+
| 3 |
| 5 |
+---------+
2 rows in set (0.00 sec)
以后的再更新。