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


這個查詢哪里錯了呀？

如果改成多表聯(lián)合查詢怎么改。

[ 本帖最后由 鴻鈞 于 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;