SQL INNER JOIN 和 LEFT SEMI JOIN 的区别

Difference between INNER JOIN and LEFT SEMI JOIN

提问by user3023355

What is the difference between an INNER JOINand LEFT SEMI JOIN?

anINNER JOIN和 和有LEFT SEMI JOIN什么不一样？

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

在下面的场景中，为什么我得到两个不同的结果？

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

该INNER JOIN结果集是很多大。有人可以解释一下吗？我正在尝试获取table_1仅出现在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)

回答by D Stanley

An INNER JOINreturns the columns from both tables. A LEFT SEMI JOINonly returns the records from the left-hand table. It's equivalent to (in standard SQL):

AnINNER JOIN返回两个表中的列。ALEFT SEMI JOIN仅返回左侧表中的记录。它相当于（在标准 SQL 中）：

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

If there are multiplematching rows in the right-hand column, an INNER JOINwill return one row for each match on the right table, while a LEFT SEMI JOINonly 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.

如果右侧列中有多个匹配行，则 anINNER JOIN将为右表中的每个匹配返回一行，而 aLEFT SEMI JOIN仅返回左表中的行，而不管右侧的匹配行数。这就是您在结果中看到不同行数的原因。

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

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

Then a LEFT SEMI JOINis the appropriate query to use.

那么 aLEFT SEMI JOIN是要使用的适当查询。

回答by Abhishek Bansal

Suppose there are 2 tables TableA and TableB with only 2 columns (Id, Data) and following data:

假设有 2 个表 TableA 和 TableB，其中只有 2 列（Id、Data）和以下数据：

TableA:

表A：

+----+---------+
| Id |  Data   |
+----+---------+
|  1 | DataA11 |
|  1 | DataA12 |
|  1 | DataA13 |
|  2 | DataA21 |
|  3 | DataA31 |
+----+---------+

TableB:

表B：

+----+---------+
| Id |  Data   |
+----+---------+
|  1 | DataB11 |
|  2 | DataB21 |
|  2 | DataB22 |
|  2 | DataB23 |
|  4 | DataB41 |
+----+---------+

Inner Joinon column Idwill return columns from both the tables and only the matching records:

列上的内部联接Id将返回来自表和匹配记录的列：

.----.---------.----.---------.
| Id |  Data   | Id |  Data   |
:----+---------+----+---------:
|  1 | DataA11 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA12 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA13 |  1 | DataB11 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB21 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB22 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB23 |
'----'---------'----'---------'

Left Join(or Left Outer join) on column Idwill return columns from both the tables and matching records with records from left table (Null values from right table):

列上的左联接（或左外联接）Id将返回表中的列以及与左表中的记录匹配的记录（右表中的空值）：

.----.---------.----.---------.
| Id |  Data   | Id |  Data   |
:----+---------+----+---------:
|  1 | DataA11 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA12 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA13 |  1 | DataB11 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB21 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB22 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB23 |
:----+---------+----+---------:
|  3 | DataA31 |    |         |
'----'---------'----'---------'

Right Join(or Right Outer join) on column Idwill return columns from both the tables and matching records with records from right table (Null values from left table):

列上的右连接（或右外连接）Id将返回表中的列以及与右表中的记录匹配的记录（左表中的空值）：

┌────┬─────────┬────┬─────────┐
│ Id │  Data   │ Id │  Data   │
├────┼─────────┼────┼─────────┤
│  1 │ DataA11 │  1 │ DataB11 │
│  1 │ DataA12 │  1 │ DataB11 │
│  1 │ DataA13 │  1 │ DataB11 │
│  2 │ DataA21 │  2 │ DataB21 │
│  2 │ DataA21 │  2 │ DataB22 │
│  2 │ DataA21 │  2 │ DataB23 │
│    │         │  4 │ DataB41 │
└────┴─────────┴────┴─────────┘

Full Outer Joinon column Idwill return columns from both the tables and matching records with records from left table (Null values from right table) and records from right table (Null values from left table):

列上的完全外部联接Id将返回表中的列以及与左表中的记录（右表中的空值）和右表中的记录（左表中的空值）匹配的记录：

╔════╦═════════╦════╦═════════╗
║ Id ║  Data   ║ Id ║  Data   ║
╠════╬═════════╬════╬═════════╣
║  - ║         ║    ║         ║
║  1 ║ DataA11 ║  1 ║ DataB11 ║
║  1 ║ DataA12 ║  1 ║ DataB11 ║
║  1 ║ DataA13 ║  1 ║ DataB11 ║
║  2 ║ DataA21 ║  2 ║ DataB21 ║
║  2 ║ DataA21 ║  2 ║ DataB22 ║
║  2 ║ DataA21 ║  2 ║ DataB23 ║
║  3 ║ DataA31 ║    ║         ║
║    ║         ║  4 ║ DataB41 ║
╚════╩═════════╩════╩═════════╝

Left Semi Joinon column Idwill return columns only from left table and matching records only from left table:

Left Semi Joinon columnId将只返回左表中的列，只返回左表中的匹配记录：

┌────┬─────────┐
│ Id │  Data   │
├────┼─────────┤
│  1 │ DataA11 │
│  1 │ DataA12 │
│  1 │ DataA13 │
│  2 │ DataA21 │
└────┴─────────┘

回答by Kumar

Tried in Hive and got the below output

在 Hive 中尝试并得到以下输出

table1

表格1

1,wqe,chennai,india

2,stu,salem,india

3,mia,bangalore,india

4,yepie,newyork,USA

1，wqe，钦奈，印度

2,stu,塞勒姆,印度

3，mia，班加罗尔，印度

4，是的，纽约，美国

table2

表2

1,wqe,chennai,india

2,stu,salem,india

3,mia,bangalore,india

5,chapie,Los angels,USA

1，wqe，钦奈，印度

2,stu,塞勒姆,印度

3，mia，班加罗尔，印度

5,chapie,洛杉矶,美国

Inner Join

内部联接

SELECT * FROM table1 INNER JOIN table2 ON (table1.id = table2.id);

1 wqe chennai india 1 wqe chennai india

2 stu salem india 2 stu salem india

3 mia bangalore india 3 mia bangalore india

SELECT * FROM table1 INNER JOIN table2 ON (table1.id = table2.id);

1 wqe 钦奈印度 1 wqe 钦奈印度

2 斯图塞勒姆印度 2 斯图塞勒姆印度

3 mia 印度班加罗尔 3 mia 班加罗尔印度

Left Join

左加入

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id);

1 wqe chennai india 1 wqe chennai india

2 stu salem india 2 stu salem india

3 mia bangalore india 3 mia bangalore india

4 yepie newyork USA NULL NULL NULL NULL

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id);

1 wqe 钦奈印度 1 wqe 钦奈印度

2 斯图塞勒姆印度 2 斯图塞勒姆印度

3 mia 印度班加罗尔 3 mia 班加罗尔印度

4 是的纽约美国 NULL NULL NULL NULL

Left Semi Join

左半连接

SELECT * FROM table1 LEFT SEMI JOIN table2 ON (table1.id = table2.id);

1 wqe chennai india

2 stu salem india

3 mia bangalore india

note: Only records in left table are displayed whereas for Left Join both the table records displayed

SELECT * FROM table1 LEFT SEMI JOIN table2 ON (table1.id = table2.id);

1 wqe 钦奈 印度

2 stu 塞勒姆 印度

3 mia 班加罗尔 印度

注意：仅显示左表中的记录，而对于左联接，同时显示表记录