楼主写的意思是仅在表1中姓名存储的记录,似乎与题意不符,而且也可以直接简化为:select * from table1 a where not exists(select * from table2 where 姓名=a.姓名)--或者(如果姓名值不唯一,可能会导致查询结果有重复记录): select a.* from table1 a left join table2 b on a.姓名=b.姓名 where b.姓名 is null
SELECT * FROM table1 a WHERE NOT EXIST (SELECT * FROM Table2 WHERE table1.[姓名] = a.[姓名] )
SELECT * FROM table a full join table1 b using ([姓名]) where table1.[姓名] <>table2.[姓名]
Select * from table1 where [name] in (Select [name] from table2 Union all Select [name] from table1 Group by [name] Having Count(name) = 1 )沒有用NOT,應該運行起來會快點.
SELECT * FROM table a full join table1 b using ([姓名]) where a.[姓名] <>b.[姓名] 刚才那个写错了
首先楼主的写法: SELECT * FROM table1 WHERE NOT EXIST (SELECT * FROM table1, Table2 WHERE table1.[姓名] = table2.[姓名] ) 里面多了个table1吧? 应该是: SELECT * FROM table1 WHERE NOT EXIST (SELECT * FROM table2 WHERE table1.[姓名] = table2.[姓名] ) 这是个相关子查询,设table1中记录数为N,table2中记录数为M,查询代价为N*M
这样写: SELECT * FROM table1 WHERE table1.[姓名] NOT IN (SELECT table2.[姓名] FROM table2) 这是不相关子查询,代价N+M不知道我对题目的理解对不对?
To:楼主 这个子句语法有错误,谈不上优化,所以你被枪毙也就不足为奇。 SELECT * FROM table1, Table2 WHERE table1.[姓名] = table2.[姓名] 应该是Select Table1.* from Table1 inner join Table2 where table1.[姓名]=table2.[姓名]
from table1
union
select *
from table2
where not exists(select * from table2 where 姓名=a.姓名)--或者(如果姓名值不唯一,可能会导致查询结果有重复记录):
select a.* from table1 a left join table2 b on a.姓名=b.姓名 where b.姓名 is null
(SELECT * FROM Table2 WHERE table1.[姓名] = a.[姓名] )
where [name] in
(Select [name] from table2
Union all
Select [name] from table1
Group by [name]
Having Count(name) = 1 )沒有用NOT,應該運行起來會快點.
刚才那个写错了
SELECT * FROM table1 WHERE NOT EXIST
(SELECT * FROM table1, Table2 WHERE table1.[姓名] = table2.[姓名] )
里面多了个table1吧?
应该是:
SELECT * FROM table1 WHERE NOT EXIST
(SELECT * FROM table2 WHERE table1.[姓名] = table2.[姓名] )
这是个相关子查询,设table1中记录数为N,table2中记录数为M,查询代价为N*M
SELECT * FROM table1 WHERE table1.[姓名] NOT IN
(SELECT table2.[姓名] FROM table2)
这是不相关子查询,代价N+M不知道我对题目的理解对不对?
这个子句语法有错误,谈不上优化,所以你被枪毙也就不足为奇。
SELECT * FROM table1, Table2 WHERE table1.[姓名] = table2.[姓名]
应该是Select Table1.* from Table1 inner join Table2 where table1.[姓名]=table2.[姓名]