SQL查詢相反條件

[用戶ID080]

SQL聯合查詢，怎麼能查到兩個表不同的列，
顯示的結果為湖北，湖南，浙江
這種SQL語句怎麼寫。想不起來怎麼做了，謝謝各位了

[顺势而为1]

用 Not in , 或 Not Exists 写

[dahaiWNX]

select * from table1 where name not in(select name from table2)

[吉普赛的歌]

DECLARE @a TABLE (id INT,c2 NVARCHAR(20))
DECLARE @b TABLE (id INT,c2 NVARCHAR(20))
INSERT INTO @a VALUES (1,'北京')
INSERT INTO @a VALUES (2,'上海')

INSERT INTO @b VALUES (1,'北京')
INSERT INTO @b VALUES (2,'上海')
INSERT INTO @b VALUES (3,'湖北')

--方法1
SELECT * FROM @b WHERE c2 NOT IN (
	SELECT c2 FROM @a	
)
--方法2
SELECT * FROM @b b WHERE NOT EXISTS (
	SELECT 1 FROM @a a WHERE a.c2=b.c2
)
--方法3
SELECT * FROM @b
EXCEPT
SELECT * FROM @a

 

[dahaiWNX]

select * from table1 where (not exists(select * from table2 where table1.name=table2.name))

[qq_38999925]

前排混個水a.