如何从右表中找不到的左表返回行? [英] How to return rows from left table not found in right table?

问题描述

我有两个具有相似列名的表，我需要从左表返回在右表中找不到的记录?我有一个主键(列)，它将帮助我比较两个表.首选哪种连接?

I have two tables with similar column names and I need to return records from the left table which are not found in the right table? I have a primary key(column) which will help me to compare both tables. Which join is preferred?

推荐答案

如果您要使用T-SQL，请先了解基础知识.这里有三种类型的联接，每种联接都有其自己的逻辑处理阶段集:

If you are asking for T-SQL then lets look at fundamentals first. There are three types of joins here each with its own set of logical processing phases as:

1. cross join最简单.它仅实现一个逻辑查询处理阶段，即Cartesian Product.此阶段在作为连接输入提供的两个表上进行操作，并产生两个表的笛卡尔积.即，来自一个输入的每一行都与来自另一输入的所有行匹配.因此，如果在一个表中有m行，在另一个表中有n行，则结果中将得到m×n行.
2. 然后是Inner joins:它们应用两个逻辑查询处理阶段:在交叉连接中在两个输入表之间使用A Cartesian product，然后根据您在ON子句中指定的谓词在filters行中进行操作(也称为Join condition).

3. 接下来是第三种联接类型，Outer Joins:

1. A cross join is simplest of all. It implements only one logical query processing phase, a Cartesian Product. This phase operates on the two tables provided as inputs to the join and produces a Cartesian product of the two. That is, each row from one input is matched with all rows from the other. So if you have m rows in one table and n rows in the other, you get m×n rows in the result.
2. Then are Inner joins : They apply two logical query processing phases: A Cartesian product between the two input tables as in a cross join, and then it filters rows based on a predicate that you specify in ON clause (also known as Join condition).

3. Next comes the third type of joins, Outer Joins:

在outer join中，通过在表名称之间使用关键字LEFT OUTER JOIN，RIGHT OUTER JOIN或FULL OUTER JOIN将表标记为preserved表. OUTER关键字是optional. LEFT关键字表示left table的行被保留； RIGHT关键字表示right table中的行被保留；并且FULL关键字表示both，left和right表中的行被保留.

In an outer join, you mark a table as a preserved table by using the keywords LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN between the table names. The OUTER keyword is optional. The LEFT keyword means that the rows of the left table are preserved; the RIGHT keyword means that the rows in the right table are preserved; and the FULL keyword means that the rows in both the left and right tables are preserved.

outer join的第三逻辑查询处理阶段基于ON谓词识别保留表中未在另一个表中找到匹配项的行.此阶段将这些行添加到联接的前两个阶段生成的结果表中，并使用NULL标记作为占位符，表示那些外部行中联接非保留端的属性.

The third logical query processing phase of an outer join identifies the rows from the preserved table that did not find matches in the other table based on the ON predicate. This phase adds those rows to the result table produced by the first two phases of the join, and uses NULL marks as placeholders for the attributes from the nonpreserved side of the join in those outer rows.

现在，如果我们看一个问题:要从左表中返回在右表中找不到的记录，请使用Left outer join并从联接的右侧过滤出属性的值为NULL的行.

Now if we look at the question: To return records from the left table which are not found in the right table use Left outer join and filter out the rows with NULL values for the attributes from the right side of the join.

这篇关于如何从右表中找不到的左表返回行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！