比對差異資料的SQL

參考
--
/******建立測試資料******/
--DROP TABLE #Book_List DROP TABLE #Book_Order
CREATE TABLE #Book_List (PID varchar(10), BookName varchar(10))
CREATE TABLE #Book_Order (PID varchar(10), Out Char(1))

INSERT INTO #Book_List
SELECT 'A001','AAAAA' UNION ALL
SELECT 'B001','BBBBB' UNION ALL
SELECT 'C001','CCCCC' UNION ALL
SELECT 'D001','DDDDD' UNION ALL
SELECT 'E001','EEEEE' UNION ALL
SELECT 'F001','FFFFF' UNION ALL
SELECT 'G001','GGGGG'

INSERT INTO #Book_Order
SELECT 'A001','Y' UNION ALL
SELECT 'B001','N' UNION ALL
SELECT 'C001','Y' UNION ALL
SELECT 'D001','Y' UNION ALL
SELECT 'E001','N' UNION ALL
SELECT 'F001','Y' UNION ALL
SELECT 'G001','N'

--SELECT * FROM #Book_List
--SELECT * FROM #Book_Order

/******以下開始******/
--使用EXCEPT
SELECT PID FROM #Book_List
EXCEPT --比較SELECT後的差異
SELECT PID FROM #Book_Order --尋找借出的
WHERE [OUT] = 'N'

--使用LEFT JOIN
SELECT a.* FROM #Book_List a
LEFT JOIN #Book_Order b ON a.PID = b.PID AND b.[OUT] = 'N'
WHERE b.PID IS NULL

--使用SubQuery
SELECT * FROM #Book_List
WHERE PID NOT IN (SELECT PID FROM #Book_Order WHERE [OUT] = 'N')