SQL Join是什么意思？ (What does a SQL Join mean?)

A SQL join describes the process of merging rows in two different tables or files together.

SQL連接描述了將兩個不同表或文件中的行合并在一起的過程。

Rows of data are combined based on values in a selected column.

根據選定列中的值組合數據行。

In the example above, this is the Item column.

在上面的示例中，這是“ 項目”列。

An Item, Apple, has both a price and a quantity, but in different tables.

一個我TEM，蘋果，既有價格和數量，但在不同的表。

Using a SQL join, these values become merged into one row!

使用SQL連接，這些值將合并為一行！

那么，為什么需要有關此內容的整篇文章？ (So why do I need an entire article about this?)

In most cases, joining data won’t be as simple as the previous example. Oftentimes, we’ll have rows that can’t be joined because there isn’t the same value in the joining column for both tables.

在大多數情況下，合并數據不會像前面的示例那樣簡單。 通常，我們會有無法連接的行，因為兩個表的連接列中的值都不相同。

For instance, what if there wasn’t a quantity for apple in the example above? How we handle what we do with rows like this depends on the type of SQL Join we choose.

例如，如果上面的示例中沒有蘋果數量，該怎么辦？ 處理此類行的方式取決于我們選擇SQL Join的類型。

There are four main types of joins: inner join, left join, right join, and full join. In this article, I’ll explain to you what each type entails.

聯接主要有四種類型：內部聯接，左聯接，右聯接和完全聯接。 在本文中，我將向您解釋每種類型的含義。

Before going into everything, I think it’d be nice to mention that our tool Merge Spreadsheets is an easy to use tool that will easily perform all of this joining for you.

在介紹所有內容之前，我想很高興地提到我們的工具“ 合并電子表格”是易于使用的工具，可以輕松地為您執行所有這些連接。

You can experiment with your spreadsheets to learn how these joins will look.

您可以嘗試使用電子表格來了解這些聯接的外觀。

什么是內部聯接？ (What is an inner join?)

An inner join results in a table with rows where the values in the joining column are in both tables.

內部聯接生成的表中具有行，其中聯接列中的值都在兩個表中。

To better understand this, let’s take a look at this example being joined on the item column:

為了更好地理解這一點，讓我們看一下在item列上加入的示例：

Both of the starting tables have a lot of different items, but only Apple and Orange are in both. So, the final result will only include Apple and Orange with their price and quantity values merged into the same row.

兩個起始表都有很多不同的項目，但是只有Apple和Orange都在。 因此，最終結果將僅包括將價格和數量值合并到同一行的Apple和Orange。

什么是左聯接？ (What is a left join?)

A left join will include all of the rows in the left table (file one), regardless of if they’re in the right table.

左聯接將包括左表(文件一)中的所有行，無論它們是否在右表中。

Let’s take a look at the same tables as before, also being merged on the item column:

讓我們看一下與以前相同的表，它們也被合并在item列上：

Like with inner join, the final product of this join includes Apple and Orange. However, it also includes all the other rows from file one, even if they don’t have quantity values (those cells will be left blank)!

與內部聯接一樣，此聯接的最終產品包括Apple和Orange。 但是，它也包括文件一中的所有其他行，即使它們沒有數量值(這些單元格將留為空白)！

什么是正確的聯接？ (What is a right join?)

A right join is the exact opposite of a left join; it includes all the values in the right table (file two) regardless of what’s in the first table.

右連接與左連接完全相反； 它包括右表(文件2)中的所有值，而不管第一個表中的內容如何。

So, in the same example as before, this will include Apple and Orange while also including any other rows in file two!

因此，在與之前相同的示例中，這將包括Apple和Orange，同時還包括文件2中的任何其他行！

什么是完全加入？ (What is a full join?)

A full join will combine all the data from both spreadsheets while merging the rows that can be merged.

完全聯接將合并兩個電子表格中的所有數據，同時合并可以合并的行。

Full join is definitely the way to go if you don’t want to exclude any data in your final result.

如果您不想在最終結果中排除任何數據，完全連接絕對是正確的選擇。

As always, using the same two tables as before will yield a different result when using a full join.

與往常一樣，使用完全連接時，使用與以前相同的兩個表將產生不同的結果。

All the rows from both tables are included and Apple and Orange become merged (as those are the only common items)!

兩個表中的所有行都包括在內，Apple和Orange合并(因為這是唯一的常見項目)！

有趣的是，這會變得更加復雜嗎？ (Interesting, so does this get more complicated?)

It gets a little more complicated. There are a couple special cases that I’ll go into so that you’ll have a better understanding of how joining works!

它變得有點復雜。 我將介紹幾種特殊情況，以便您對加入的工作方式有更好的了解！

如果我的數據重復了怎么辦？ (What if I have duplicates in my data?)

If you have duplicates in one table, they’ll also show up in your joined table depending on the join type.

如果您在一個表中有重復項，則它們還將顯示在聯接表中，具體取決于聯接類型。

If there is a single row in one file with the same value in the joining column as multiple rows in the second file, those data in that single row will be repeated for each instance.

如果一個文件中的單行與第二個文件中的多行在連接列中具有相同的值，則將針對每個實例重復該單行中的那些數據。

For example, let’s look at the tables below being Inner joined on the Item column. In file one, there are two Orange rows while there’s only one in file two.

例如，讓我們看一下在“ 項目”列上內部連接的下表。 在文件一中，有兩個橙色行，而在文件二中只有一個。

Joining this will include both of the unique rows from file one while repeating the data for quantity from file two!

加入此文件將包括文件1的兩個唯一行，同時重復文件2的數量數據！

By the way, a LEFT join would also produce the same result

順便說一句， LEFT連接也會產生相同的結果

我可以共同加入多個列嗎？ (Can I join with multiple columns in common?)

Yes! If you want to join a table based on multiple columns, each grouping will be classified as its own unique entity.

是! 如果要基于多個列聯接表，則每個分組都將分類為自己的唯一實體。

For instance, in the example below, we’ll be Full joining on the Item and Price columns.

例如，在下面的示例中，我們將在“ 項目”和“ 價格”列上進行“ 完全連接”。

The Apple row is the only one that’s merged because it’s the only one with the same Item and Price in both tables.

蘋果行是唯一被合并的行，因為它是兩個表中唯一具有相同項目和價格的行 。

Instead of both of the Orange rows being merged, they are treated as different rows because their price values are different.

由于它們的價格值不同，因此不會將兩個橙色行都合并，而是將它們視為不同的行。

So, everything is the same as before but now we look for the same values in two columns rather than just one.

因此，一切都與以前相同，但是現在我們在兩列中尋找相同的值，而不僅僅是一列。

如果我的公用欄中有空白值怎么辦？ (What if I have a blank value in my common column?)

Each blank value in the column you’re joining on will be treated like any normal value. In other words, you can join on a blank value as usual.

您要加入的列中的每個空白值都將被視為任何常規值。 換句話說，您可以照常加入空白值。

For example, in the tables above, there are blank values in both files in the item column. These will be treated as the same string and become joined! In the case of the example above, all types of joining will result in the same product.

例如，在上表中，項目列中的兩個文件都有空白值。 這些將被視為相同的字符串并加入！ 在上面的示例中，所有類型的連接都將產生相同的產品。

NOTE: While a blank value will be treated as any other string, a NULL value is different. NULL values denote nothing and are typically used in code. Each NULL value is treated as a unique entity and can’t be joined on. For more information, check out this article.

Let’s also take a look at a more comprehensive example with all of these special cases in it.

讓我們也看看其中包含所有這些特殊情況的更全面的示例。

We will be performing a Left join on the Item and Price columns.

我們將在項目和價格列上執行左連接。

When performing a left join, all the rows from the left file will be in the final product.

執行左聯接時，左文件中的所有行都將在最終產品中。

The Apple,$1 row is in both files, so the ID# and quantity for them will be merge.

Apple，$ 1行在兩個文件中，因此它們的ID＃和數量將合并。

Orange,$2 is only in the left file, so its quantity will remain blank.

橙色，$ 2僅在左側文件中，因此其數量將保持空白。

Finally, we have a blank/blank row in the left file and two blank/blank rows in the second file. This means the the ID # for blank/blank in file one will be repeated twice to go with each quantity value in the second table!

最后，左側文件中有一個空白行，第二個文件中有兩個空白行。 這意味著文件1中空白/空白的ID＃將重復兩次，以與第二張表中的每個數量值對應！

And the fully blank row at the bottom will be ignored!

底部的空白行將被忽略！

所以...我們涵蓋了所有內容嗎？ (So… have we covered everything?)

Yep! We just went over all the basics of SQL joins so you should be able to join tables with no problems now.

是的 我們僅介紹了SQL連接的所有基礎知識，因此您現在應該可以毫無問題地連接表。

Feel free to experiment with your data in spreadsheets at Merge Spreadsheets because there is nothing like trying this out to really understand it.

可以在Merge Spreadsheets的電子表格中隨意嘗試數據，因為沒有什么可以像嘗試這種方法那樣真正理解它了。

If you still have questions, don’t hesitate to reach out to info@lovespreadsheets.com!

如果您仍有疑問，請隨時聯系info@lovespreadsheets.com！

Want to learn more about how to Merge Spreadsheets? Check out our ultimate guide here!

想更多地了解如何合并電子表格？ 在這里查看我們的最終指南！