INNER JOIN 和 LEFT SEMI JOIN 的区别 [英] 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))

如果右侧列中有多个匹配的行，则 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.

这篇关于INNER JOIN 和 LEFT SEMI JOIN 的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！