Select values from Three tables value not in another table MSSQL-CodePudding

I am using SQL Database. I am having three tables like T1,T2,T3. I want to get a column values from a table by excluding the values exists in another table. SELECT T3 THEN SELECT T2 NOT in T3 THEN SELECT T1 NOT in T3 and T2

CREATE TABLE T1(CID INT,NAME VARCHAR(100),EMAIL VARCHAR(50), MOIBLE VARCHAR(20)) 
CREATE TABLE T2(CID INT,NAME VARCHAR(100),EMAIL VARCHAR(50), MOIBLE VARCHAR(20))
CREATE TABLE T3(CID INT,NAME VARCHAR(100),EMAIL VARCHAR(50), MOIBLE VARCHAR(20))

Insert value

INSERT INTO T1 VALUES (1,'TEST1','a@gmail','123456')
INSERT INTO T1 VALUES (2,'TEST2','b@gmail','123456')
INSERT INTO T1 VALUES (3,'TEST3','c@gmail','123456')
INSERT INTO T1 VALUES (4,'TEST4','d@gmail','123456')

INSERT INTO T2 VALUES (1,'TEST1','updateda@gmail','123456')
INSERT INTO T2 VALUES (2,'TEST2','updatedb@gmail','77777')

INSERT INTO T3 VALUES (1,'TEST1','updateda@gmail','999999')

Expected output

SELECT T3 and

SELECT T2 record NOT in T3 and

SELECT T1 record NOT in T3 and T2

cid   Name    email             moible
 1    TEST1   updateda@gmail    999999     From T3
 2    TEST2   updatedb@gmail    77777      From T2
 3    TEST3   c@gmail           123456     From T1
 4    TEST4   d@gmail           123456     From T1

I have written query for this

SELECT *  FROM T2 where cid not in ( SELECT cid from T3)
UNION
SELECT * FROM T3

Here How to include T1 table?

CodePudding user response：

You can add anther 1 union cid not in T2 and T3

SELECT * from T1 where cid not in(SELECT cid  FROM T2 where cid not in ( SELECT 
      cid from T3)
      UNION
       SELECT cid FROM T3)
 UNION
 SELECT *  FROM T2 where cid not in ( SELECT cid from T3)
 UNION
 SELECT * FROM T3