34,587
社区成员
发帖
与我相关
我的任务
分享
select * from table_2 where id not in (select id from table_1)
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values(1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> -- 方法 1
mysql> select * from t2 where id not in(select id from t1) ;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
mysql> -- 方法 2
mysql> select * from t2 where not exists(select * from t1 where t1.id = t2.id);
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
mysql> -- 方法 3
mysql> select t2.* from t2 left join t1 on t1.id = t2.id where t1.id is null;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
mysql>
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.00 sec)
mysql>