mysql嵌套查询问题

鸿钧

mysql> select * from a where domain not in (select domain from b);

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select domain from b)' at line 1


这个查询哪里错了呀？

如果改成多表联合查询怎么改。

[ 本帖最后由 鸿钧 于 2009-11-23 17:37 编辑 ]

鸿钧

难道是我mysql版本的问题。

[root@localhost ~]# mysql -V
mysql  Ver 12.22 Distrib 4.0.27, for pc-linux-gnu (i686)

leoxqing

Try:
select * from a where domain not exists (select 1 from b where domian=a.domain);

cenalulu

5.0.22没问题，也许是4.0还没支持 not in嵌套子查询吧吧

mysql> create table a (domain int); Query OK, 0 rows affected (0.01 sec) mysql> create table b (domain int); Query OK, 0 rows affected (0.01 sec) mysql> insert into a values(1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2  Duplicates: 0  Warnings: 0 mysql> insert into b values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from a where domain not in (select domain from b); +--------+ | domain | +--------+ |      2 | +--------+ 1 row in set (0.00 sec)

表连接查询的话：
select a.* from a left join b on a.domain=b.domain where b.primary_key is null;