sql - 确定只出现在两个表之一或两个表中的值

Question

我在 SQL Server 2008 数据库中有两个表，我想在其中找到:

两个表中都存在的值(两个表中都存在所有列)

值出现在第一个表中但不在第二个表中

值出现在第二个表中但不在第一个表中

代码:

CREATE TABLE #_temp
(ATM INT, Fault INT)

CREATE TABLE #_temp1
(ATM INT, Fault INT)

INSERT INTO #_temp  VALUES (10,101), (11,101), (12,101), (12,101), (10,105), (13,101)
INSERT INTO #_temp1 VALUES (10,102), (11,101), (12,103), (12,100), (10,105), (13,101)

/* My Try

SELECT * FROM #_temp t RIGHT JOIN #_temp1 t1 ON t.ATM=t1.ATM AND t.Fault=t.Fault AND t.ATM IS NULL AND t.Fault IS NULL

SELECT * FROM #_temp t JOIN #_temp1 t1 ON t.ATM=t1.ATM AND t.Fault=t.Fault

*/

DROP Table #_temp
DROP Table #_temp1 

Answer 1

要查找存在于一个表中而不是另一个表中的值，您应该使用 where 子句来确定空值:

Create Table #_temp
(ATM Int,Fault Int)

Create Table #_temp1
(ATM Int,Fault Int)

Insert Into #_temp Values(10,101),(11,101),(12,101),(12,101),(10,105),(13,101)
Insert Into #_temp1 Values(10,102),(11,101),(12,103),(12,100),(10,105),(13,101)


--Values Present in both Table

SELECT t.* 
FROM #temp t
    INNER JOIN #_temp1 t1
        ON t.[ATM Int] = t1.[ATM Int]
        AND t.[Fault Int] = t1.[Fault Int]

--Values Present in First Table But not in Second

SELECT t.* 
FROM #temp t
    LEFT JOIN #_temp1 t1
        ON t.[ATM Int] = t1.[ATM Int]
        AND t.[Fault Int] = t1.[Fault Int]
WHERE t1.[ATM Int] IS NULL

--Values Present in Second Table But not in First

SELECT t.* 
FROM #_temp1 t
    LEFT JOIN #temp t1
        ON t.[ATM Int] = t1.[ATM Int]
        AND t.[Fault Int] = t1.[Fault Int]
WHERE t1.[ATM Int] IS NULL