内联接和左半联接之间的区别 [英] Difference between INNER JOIN and LEFT SEMI JOIN

问题描述

INNER JOIN和LEFT SEMI JOIN有什么区别?

在以下情况下，为什么我得到两个不同的结果?

In the scenario below, why am I getting two different results?

INNER JOIN结果集要大得多.有人可以解释吗?我试图获取table_1中仅出现在table_2中的名称.

The INNER JOIN result set is a lot larger. Can someone explain? I am trying to get the names within table_1 that only appear in table_2.

SELECT name
FROM table_1 a
    INNER JOIN table_2 b ON a.name=b.name

SELECT name
FROM table_1 a
    LEFT SEMI JOIN table_2 b ON (a.name=b.name)

推荐答案

INNER JOIN可以从两个表的列中返回数据，并且可以重复任一侧记录的值具有多个匹配项. LEFT SEMI JOIN只能返回左侧表中的列，并从左侧表中产生每个记录之一，其中右侧表中有一个或多个匹配项(与匹配项的数量无关).等效于(在标准SQL中):

An INNER JOIN can return data from the columns from both tables, and can duplicate values of records on either side have more than one match. A LEFT SEMI JOIN can only return columns from the left-hand table, and yields one of each record from the left-hand table where there is one or more matches in the right-hand table (regardless of the number of matches). It's equivalent to (in standard SQL):

SELECT name
FROM table_1 a
WHERE EXISTS(
    SELECT * FROM table_2 b WHERE (a.name=b.name))

如果右侧列中有 multiple 个匹配行，则INNER JOIN将为右侧表中的每个匹配项返回一行，而LEFT SEMI JOIN仅返回该行中的行.左边的表格，无论右边匹配的行数是多少.这就是为什么您在结果中看到不同数量的行的原因.

If there are multiple matching rows in the right-hand column, an INNER JOIN will return one row for each match on the right table, while a LEFT SEMI JOIN only returns the rows from the left table, regardless of the number of matching rows on the right side. That's why you're seeing a different number of rows in your result.

我正在尝试获取table_1中仅出现在table_2中的名称.

I am trying to get the names within table_1 that only appear in table_2.

然后LEFT SEMI JOIN是适合使用的查询.

Then a LEFT SEMI JOIN is the appropriate query to use.

这篇关于内联接和左半联接之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！