我的目的是利用Table1找出Table2中与之ID相同的数据行方法一:
select * from Table2 where BOOK_ID in (select BOOK_ID from Table1)
用这种方式找到的记录有50多万行
方法二:
select Table2.* from Table2,Table1 where Table2.BOOK_ID = Table1.BOOK_ID
而用这种方式找到的记录却只有30多万行为什么不一样呢,哪个是正确的呢?
select * from Table2 where BOOK_ID in (select BOOK_ID from Table1)
用这种方式找到的记录有50多万行
方法二:
select Table2.* from Table2,Table1 where Table2.BOOK_ID = Table1.BOOK_ID
而用这种方式找到的记录却只有30多万行为什么不一样呢,哪个是正确的呢?
select * from Table2 a where exists (select 1 from Table1 where a.BOOK_ID = BOOK_ID )
INSERT @TA
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3DECLARE @TB TABLE([BOOK_ID] INT)
INSERT @TB
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 3SELECT * FROM @TA WHERE [BOOK_ID] IN (SELECT [BOOK_ID] FROM @TB)
SELECT A.* FROM @TA AS A JOIN @TB AS B ON A.[BOOK_ID]=B.[BOOK_ID]
/*
BOOK_ID
-----------
1
3(2 row(s) affected)BOOK_ID
-----------
1
1
3(3 row(s) affected)*/
select distinct Table2.* from Table2,Table1 where Table2.BOOK_ID = Table1.BOOK_ID
是的,我这两张表的ID确实有重复,因为表中的每个城市都有一套相似的ID那这时我该用方法一还是二呢,谢谢