Query with a join of 3 tables

Question

I want one query to produce the following results.

To make entries that are in table 1 and table 2, and were not in table 3.

Each table contains over 10,000 entries. I am looking for effective. Throughout the table, Cono is the primary key.

Detail with tables.

TABLE 1: -

Cono     

th-123
th-124
th-125

TABLE 2: -

Cono     

th-234
th-245
th-256

TABLE 3: -

Cono     

th-124
th-125
th-256

Now I want to have the following entries

Result TABLE: -

Cono     

th-123
th-234
th-245

Answer 1

try it

WITH Table1 AS
(
    SELECT 'th-123' CONO UNION
    SELECT 'th-124' UNION
    SELECT 'th-125'
)
,
Table2 AS
(
    SELECT 'th-234' CONO UNION
    SELECT 'th-245' UNION
    SELECT 'th-256'
)
,
Table3 AS
(
    SELECT 'th-124' CONO UNION
    SELECT 'th-125' UNION
    SELECT 'th-256'
)

SELECT CONO
FROM Table1 
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table3
    WHERE TABLE1.CONO = TABLE3.CONO
)

UNION ALL

SELECT CONO
FROM Table2
WHERE NOT EXISTS
(
    SELECT 1
    FROM Table3
    WHERE TABLE2.CONO = TABLE3.CONO
)

Answer 2

try the following:

select t.cono from Table1 t WHERE NOT EXISTS (SELECT 1 
                                              FROM Table3 x WHERE x.cono=t.cono)
UNION
select t.cono from Table2 t WHERE NOT EXISTS (SELECT 1 
                                              FROM Table3 x WHERE x.cono=t.cono)

Answer 3

(SELECT t1.Cono FROM table1 t1
LEFT JOIN table3 t3
ON t3.Cono = t1.Cono
WHERE t3.Cono IS NULL)
UNION ALL
(SELECT t2.Cono FROM table2 t2
LEFT JOIN table3 t3
ON t3.Cono = t2.Cono
WHERE t3.Cono IS NULL)

Answer 4

( ):

; WITH all_data AS (
   SELECT * FROM table1
   UNION ALL
   SELECT * FROM table2
)
SELECT * 
FROM all_data ad
WHERE NOT EXISTS (
   SELECT *
   FROM table3 t3
   WHERE ad.Cono = t3.Cono);

Answer 5

, , :

SELECT Cono
FROM Table3
WHERE NOT EXISTS ( SELECT Cono
                   FROM TABLE1 as T
                   WHERE EXISTS ( SELECT *
                                  FROM TABLE2
                                  WHERE T.Cono = TABLE2.Cono));

This should select the values ​​in table 3 that do not exist in the query, which was created in brackets, which is a table consisting of rows that are in tables 1 and 2.

Unfortunately, nesting and efficiency usually do not go hand in hand ...

Answer 6

this one worked for me ... and quickly worked:

select X.FID, c.id as CID 
from
(
    select a.id as FID from tbl1 a
    union 
    select b.id as FID from tbl2 b
) as X
 left outer join tbl3 c on FID = c.id
where
    c.id is null
;