SQL query to fetch the data from two tables with “

问题:

I have 2 tables, Table1 and Table2, where 2 columns in both tables are same

update:type of Table1.col1 same as Table2.col1 and Table1.col2 same as Table2.col2

Trying to fetch the data where table1.col1 not in table2.col1 and table1.col2 not in table2.col2, and this is my query.

select * from Table1 
    where Table1.col1 not in (select Table2.col1 from Table2)
      and Table1.col2 not in (select Table2.col2 from Table2)

would like to know any better way or is this correct ?

回答1:

This query should do the job, I ran a simple test based on your query and it doesn't produce the desired result

SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
WHERE t2.col1 IS NULL AND t2.col2 IS NULL

Given this

CREATE TABLE Table1
(
colA    VarChar(50),
col1    Int,
col2    Int
)

CREATE TABLE Table2
(
colB    VarChar(50),
col1    Int,
col2    Int
)

INSERT Table1
VALUES ('A', 1, 1),
        ('B', 1, 2),
        ('C', 2, 1)

INSERT Table2
VALUES ('X', 1, 1),
        ('Y', 2, 1),
        ('Z', 2, 2)

If I understood your question, we should get this B | 1 | 2

回答2:

Use a LEFT JOIN:

SELECT Table1.* 
FROM Table1 
LEFT JOIN Table2
  ON Table1.col1 = Table2.col1
  AND Table1.col2 = Table2.col2
WHERE Table2.col1 IS NULL

回答3:

please comment, if any problem with this query:

select * from table1
where not exists (select 1 from table2 where table2.col1 = table1.col1 and table2.col2 = table1.col2)