使用'Not In'在Sql中具有多列的两个表 [英] Using 'Not In' Two tables with Multiple Column in Sql

问题描述

我有两张桌子，



- 表A

- 表B



两个表都有不同的列..如何检查一个表数据不存在于另一个具有多列条件的表中。这是我的代码..但它无法获取不匹配的数据..

 选择代码，名称，尺寸来自 TableA 其中代码   （ 选择代码来自 TableB）和名称< span class =code-keyword> not   in （选择名称来自 TableB）和 
 SIZE 不 中的class =code-keyword>（ Se lect 尺寸来自 TableB）

我还使用'不存在......但没有使用..

 选择代码，名称，大小来自 TableA Tb1 其中 不 存在（选择代码来自 TableB Tb2 其中 Tb1.Code！= Tb2.CODE 和 Tb1.Name！= Tb2.Name 和 Tb1.Size！= Tb2.SIZE）

建议我任何其他功能..谢谢...

解决方案

您可以像这样使用外连接：

 选择代码，名称，大小
  FROM  TableA a 
  left   OUTER   JOIN  TABLEB b 
  ON  a.code = b.code 
  AND  a.Name = b.Name 
  AND  a.SIZE = b.SIZE 
  WHERE  b.code  IS   NULL  
 或 b.name  IS   NULL  
 或 b.SIZE  IS   NULL  

如果您使用过Oracle，则可以使用如下的IN子句：

 选择代码，名称，大小
  FROM  TableA a 
 < span class =code-keyword> WHERE （代码，名称，大小） NOT   IN （ SELECT 代码，名称，SIZE  FROM  TableB）

< edit>我重新访问了Maciejs评论，并意识到你的存在查询出了什么问题，它应该是这样的：< pre lang =SQL> 选择代码，名称，SIZE
FROM TableA Tb1
WHERE NOT EXISTS （
SELECT 1
FROM TableB Tb2
WHERE Tb1.Code = Tb2.CODE
AND Tb1。 Name = Tb2.Name
AND Tb1.Size = Tb2.SIZE
）

最后它可能会得到与连接查询相同的计划，但它是值得测试。< / edit>

Solution1很好，我建议你阅读这篇文章： SQL连接的可视化表示 [ ^ ]。它将帮助您了解联接的工作原理。

尝试合并声明：



http://blog.sqlauthority.com / 2008/08/28 / sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete / [ ^

i have Two tables,

- Table A
- Table B

Both the tables have Different Column..How can i check one table Data not exists in another table with Multi Column Condition.here is my Code..but it cant fetch unmatch data..

Select Code,Name,Size from TableA  where Code Not in (Select Code from TableB) and Name not in (Select Name from TableB) and 
SIZE not in (Select Size from TableB)

Am also using 'Not Exists'..but no Use..

Select Code,Name,Size from TableA Tb1  where not exists (Select Code from TableB Tb2 where Tb1.Code != Tb2.CODE and Tb1.Name != Tb2.Name and Tb1.Size != Tb2.SIZE) 

Suggest me any other Function..Thank You...

解决方案

You can use an outer join like this:

Select  Code,Name,SIZE
FROM    TableA a
left OUTER JOIN TABLEB b
    ON  a.code = b.code
    AND a.Name = b.Name
    AND a.SIZE = b.SIZE
WHERE   b.code IS NULL
OR      b.name IS NULL
OR      b.SIZE IS NULL

If you had used Oracle you could have used an IN clause like this:

Select  Code,Name,SIZE
FROM    TableA a
WHERE   (Code,Name,SIZE) NOT IN (SELECT Code,Name,SIZE FROM TableB)

<edit>I revisited because of Maciejs comment and realized what was wrong with your "Exists" query, it should look like this:

Select  Code,Name,SIZE
FROM    TableA Tb1
WHERE NOT EXISTS (
    SELECT  1
    FROM    TableB Tb2
    WHERE   Tb1.Code = Tb2.CODE
    AND     Tb1.Name = Tb2.Name
    AND     Tb1.Size = Tb2.SIZE
    ) 

In the end it's probably getting the same plan as the join query but it's worth testing.</edit>

Solution1 is good and i would suggest you to read this article: Visual Representation of SQL Joins[^]. It will help you to understand how joins work.

Try out Merge Statement :

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/[^]

这篇关于使用'Not In'在Sql中具有多列的两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！