except在mysql中怎么实现_有关PGSQL的INTERSECT和EXCEPT以及在MYSQL中的实现代码

有关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)

以后的再更新。