SQL查询以使用"not in"从两个表中获取数据.健康)状况 [英] SQL query to fetch the data from two tables with "not in" condition

问题描述

我有2个表Table1和Table2，其中两个表中的2列相同

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

update:Table1.col1的类型与Table2.col1相同，并且Table1.col2与Table2.col2相同

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

试图获取table1.col1不在table2.col1且table1.col2不在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 ?

推荐答案

此查询应能完成工作，我根据您的查询运行了一个简单的测试，但未产生预期的结果

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

给出这个

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)

如果我理解您的问题，我们应该给这个 B | 1 | 2

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

这篇关于SQL查询以使用"not in"从两个表中获取数据.健康)状况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！