快速熟悉每个工厂
There are many ways to JOIN data from two database tables and filter the information you require. Craig Buckler wrote a popular piece on understanding JOINs; namely INNER, LEFT, RIGHT, and FULL OUTER. This article is an extension of that one.
有很多方法可以联接来自两个数据库表的数据并过滤所需的信息。 克雷格·巴克勒(Craig Buckler)撰写了一篇关于理解联接的流行文章; 即INNER，LEFT，RIGHT和FULL OUTER。 本文是对该文章的扩展。
Let’s recap these real quick. Picture two tables, one for customers and one for books to establish a book loan database.
让我们快速回顾一下这些真实的东西。 图片两个表，一个用于customers ，一个用于books以建立图书借阅数据库。
customers table
customers表
idfirstnamelastnamebook_id1JoeBlow12JaneDoe23HarryCrow24JeffreySnow0 ID 名字 姓 book_id 1个 乔 打击 1个 2 简 母鹿 2 3 哈里 乌鸦 2 4 杰弗里 雪 0books table
books
idTitle1Star Wars2Jurassic Park3Little Women4Tom Sawyer ID 标题 1个 星球大战 2 侏罗纪公园 3 小女人 4 汤姆·索亚The books table has one row for every book.
books表每本书有一行。
The customers table has one row for each customer who can only have one book on loan at a time. If they have no book on loan, the book_id would be 0 or an empty string.
“ customers表为每个只能一次借一本书的客户的每一行。 如果他们没有借出的书，则book_id将为0或一个空字符串。
This is a very simple example to make the JOINs as clear as possible to understand!
这是一个非常简单的示例，使JOIN尽可能清晰易懂！
A LEFT JOIN here would be in the case you want to ask a question such as “show me all customers including any books on loan.”
左连接这里将在情况下，你要问一个问题，如“ 显示我的所有客户，包括贷款的任何书籍。 ”
You can see in the image that ALL data in the left circle, or table, is included in the result set. Only data that overlaps from the books table is included from the right table. This means that with a LEFT JOIN, some data in the right table may be excluded.
您可以在图像中看到，结果集中包含左圆或表格中的所有数据。 右表仅包括books表中重叠的数据。 这意味着使用LEFT JOIN可以排除右表中的某些数据。
A RIGHT JOIN would be like asking “show me all the books in my library, along with any customers that have borrowed them.”
正确的加入就像问“ 向我展示图书馆中的所有书籍，以及所有借用它们的客户一样。 ”
In this image you see that ALL data in the right table is included in the result set. Only data that overlaps from the customers table is included. This means that with a RIGHT JOIN, some data in the left table may be excluded.
在此图像中，您看到右表中的所有数据都包含在结果集中。 仅包含来自customers表的重叠数据。 这意味着使用RIGHT JOIN可以排除左表中的某些数据。
An OUTER JOIN would be like asking “show me all loans and all books, regardless of connections between them.”
外部联接就像问“ 向我展示所有贷款和所有书籍，无论它们之间有什么联系。 ”
In this image, you can see that ALL data from both tables will be included, regardless of whether some data overlaps. When a query like this is done, you will have non-overlapping data in the result, and those fields will be set to NULL.
在此图中，您可以看到两个表中的所有数据都将包括在内，而不管某些数据是否重叠。 完成这样的查询后，结果中将包含不重叠的数据，并且这些字段将设置为NULL。
An INNER JOIN would be like asking “show only customers with a loan.”
INNER JOIN就像问“ 仅显示有贷款的客户。 ”
Here you can see that data may be excluded from both the left and right tables. You won’t see any customers if they DON’T have a book out, and you won’t see any books if they are NOT loaned out!
在这里，您可以看到左侧和右侧表中都可能排除了数据。 如果没有借出的书，您将看不到任何客户；如果没有借出，也将看不到任何书！
This is the most common type of data and is the default behavior when using the keyword JOIN by itself. The added word “INNER” is not usually required.
这是最常见的数据类型，并且是JOIN使用关键字JOIN时的默认行为。 通常不需要添加单词“ INNER”。
You can think of “left” and “right” as nothing more than source order. Look at this query:
您可以将“左”和“右”视为源顺序。 看一下这个查询：
SELECT * FROM customers LEFT JOIN books ON customers.book_id = books.idNotice that I mentioned the table customers before I used the JOIN keyword. This means customers is my “left” table. Another way of thinking about it is to ask which table is left of the JOIN keyword, and which is to the right of it.
注意，在使用JOIN关键字之前，我提到过表customers 。 这意味着customers是我的“左”表。 考虑它的另一种方法是询问哪个表在JOIN关键字的左边，哪个在它的右边。
A couple caveats:
几个警告：
Simply because one table is the “left” one, does not mean you are selecting ALL its records. This is the function of LEFT JOIN or RIGHT JOIN, not source order!
仅仅因为一个表是“左”表，并不意味着您选择了它的所有记录。 这是LEFT JOIN或RIGHT JOIN ，而不是源命令！
The table that is the left one will have its columns listed first in the result set unless you specifically choose columns in the SELECT section.
除非您在SELECT部分中特别选择列，否则左边的表的列将在结果集中列在最前面。
The order of tables after the ON keyword doesn’t matter, it would return the same results if swapped.
ON关键字后的表顺序无关紧要，如果交换，它将返回相同的结果。
Well, they are not “new”, they are just a way to query additional questions. How would you find answers to questions with the words “don’t” or “not” in the query?
好吧，它们不是“新手”，它们只是查询其他问题的一种方式。 您如何在查询中找到带有“不”或“不”字样的问题的答案？
Show me all the customers who DON’T have books on loan.
向我显示所有没有借书的客户。
Show me all the books that are NOT loaned out.
给我看所有不借出去的书。
Take a look at this graphic. Notice the difference from the above JOINs in the shaded area.
看一下这张图。 注意阴影区域与上述JOIN的区别。
This looks like a LEFT JOIN, but no longer has the “overlap” data included. Why? This is the same as asking “show me customers who have no books on loan.” Perhaps you want to select all customers without a book and send them a newsletter with a special discount?
看起来像是LEFT JOIN ，但不再包含“重叠”数据。 为什么？ 这与询问“ 向我显示没有借阅书籍的客户一样” 。 ”也许您想选择没有书籍的所有客户，并向他们发送特别折扣的时事通讯？
If you are clever you might think you can just search the customers table for a book_id of 0 to do the same thing. That would work in this example but most of the time it won’t; it depends how your tables are designed.
如果您很聪明，可能会认为您可以只在customers表中搜索book_id为0来执行相同的操作。 在此示例中，这将起作用，但在大多数情况下不会起作用； 这取决于表的设计方式。
The query looks like this:
查询如下所示：
SELECT * FROM customers LEFT JOIN books ON customers.book_id = books.id WHERE books.id IS NULLWe’ve now included a WHERE clause. Any time you have a WHERE clause, you are excluding data, or filtering it, almost like a search. So why are we searching for books.id being NULL? Why would that be NULL? Let’s run the standard LEFT JOIN and see the data it returns, we’ll have the answer:
现在，我们包含了WHERE子句。 每当您拥有WHERE子句时，您都将在排除数据或对其进行过滤，就像搜索一样。 那么，为什么我们要搜索books.id为NULL？ 为什么会为NULL？ 让我们运行标准的LEFT JOIN并查看它返回的数据，我们将得到答案：
LEFT JOIN return data
LEFT JOIN返回数据
idfirstnamelastnamebook_idid1title1JoeBlow11Star Wars2JaneDoe22Jurassic Park3HarryCrow22Jurassic Park4JeffreySnow0nullnull ID 名字 姓 book_id id1 标题 1个 乔 打击 1个 1个 星球大战 2 简 母鹿 2 2 侏罗纪公园 3 哈里 乌鸦 2 2 侏罗纪公园 4 杰弗里 雪 0 空值 空值Can you see the issue? This is a LEFT JOIN which means ALL data from the customers table is included regardless of overlap with books. Jeffrey Snow is included but he does not have a book on loan, this is why the “id1” and “title” columns are set to NULL. When the tables are JOINed, there would not be a book title or book ID linked to him.
你看到这个问题了吗？ 这是一个LEFT JOIN ，这意味着包括与customers书无关的所有来自customers表的数据。 包括了Jeffrey Snow，但他没有借书，这就是为什么将“ id1”和“ title”列设置为NULL的原因。 联接表后，将没有链接到他的书名或书ID。
If we ask “show me all customers including which books they have”, you would want the above data because Jeffrey is a customer regardless if he has a book on loan or not. That would be the function of a LEFT JOIN in this case.
如果我们问“ 向我显示所有客户，包括他们拥有的书籍 ”，您将需要以上数据，因为Jeffrey是客户，无论他是否有借书。 在这种情况下，这将是LEFT JOIN的功能。
If we ask the question “show me customers with no books on loan”, it now makes perfect sense what to look for. We only need to select customers where we see NULL for the books.id column (when JOINed, would be labeled id1 since there are two columns named “id”). We do this with a standard WHERE clause if we add WHERE books.id IS NULL. Now the result is filtered to just this:
如果我们问“ 向我展示没有借书的客户 ”这个问题，那么寻找什么就变得很有意义了。 我们只需要选择books.id NULL的客户(加入books.id时将被标记为id1因为有两列名为“ id”)。 如果添加WHERE books.id IS NULL我们将使用标准的WHERE子句进行此操作。 现在将结果过滤为：
idfirstnamelastnamebook_idid1title4JeffreySnow0nullnull ID 名字 姓 book_id id1 标题 4 杰弗里 雪 0 空值 空值You now have all customers who do not have books on loan.
现在，您有了所有没有借阅书籍的客户。
Let’s do the same with a RIGHT JOIN. Let’s find all the books which are NOT loaned out to anybody.
让我们使用RIGHT JOIN进行相同的操作。 让我们找到所有不借给任何人的书。
A normal RIGHT JOIN would return every book regardless of whether it is loaned, the result set would look like this:
普通的RIGHT JOIN会返回每本书，而不管是否借书，结果集如下所示：
idfirstnamelastnamebook_idid1title1JoeBlow11Star Wars2JaneDoe22Jurassic Park3HarryCrow22Jurassic Parknullnullnullnull3Little Womennullnullnullnull4Tom Sawyer ID 名字 姓 book_id id1 标题 1个 乔 打击 1个 1个 星球大战 2 简 母鹿 2 2 侏罗纪公园 3 哈里 乌鸦 2 2 侏罗纪公园 空值 空值 空值 空值 3 小女人 空值 空值 空值 空值 4 汤姆·索亚This looks a bit different. First, you might notice that Jurassic Park is listed twice. This is because two people have the book on loan, and the database is returning a row for each match.
这看起来有些不同。 首先，您可能会注意到侏罗纪公园被列出了两次。 这是因为有两个人借了这本书，并且数据库为每个匹配项都返回一行。
Notice all the corresponding columns from the customers table are NULL for Little Women and Tom Sawyer because no one borrowed those titles so there is no overlapping data.
请注意，对于Little Women和Tom Sawyer， customers表中customers表的所有对应列均为NULL，因为没有人借用这些标题，因此没有重叠数据。
If we want to select all the books that are not loaned out, we just use the WHERE clause to find “NULL” in the customers.id column.
如果我们要选择所有未借出的书，我们只需使用WHERE子句在customers.id列中查找“ NULL”。
SELECT * FROM customers RIGHT JOIN books ON customers.book_id = books.id WHERE customers.id IS NULLThe result should be predictable. We get only books that are not loaned out.
结果应该是可预测的。 我们只会得到未借出的书。
idfirstnamelastnamebook_idid1titlenullnullnullnull3Little Womennullnullnullnull4Tom Sawyer ID 名字 姓 book_id id1 标题 空值 空值 空值 空值 3 小女人 空值 空值 空值 空值 4 汤姆·索亚The last JOIN looks like this.
最后的JOIN看起来像这样。
This JOIN is not very useful, but will essentially give you a list of both customers with no loan, and books which are not loaned, at the same time.
这JOIN是不是很有用，但会基本上是给你带来的客户名单，没有贷款， 和书未借给，在同一时间。
A strange JOIN like this may be useful in cases where you literally need to select data with no connection between tables. Perhaps you are hunting for orphan data or looking for inconsistencies in some old database you converted.
在您确实需要选择表之间没有连接的数据的情况下，这种奇怪的JOIN可能很有用。 也许您正在寻找孤立数据或正在转换的某些旧数据库中寻找不一致之处。
In fact, this kind of JOIN is so weird that you can’t even do it in MySQL, it doesn’t support OUTER JOIN. Regular SQL does, and the query would look like this (MSSQL not MySQL):
实际上，这种JOIN非常奇怪，以至于您甚至无法在MySQL中完成，它不支持OUTER JOIN 。 常规SQL确实如此，查询看起来像这样(MSSQL不是MySQL)：
SELECT * FROM customers FULL OUTER JOIN books ON customers.id = books.id WHERE customers.id IS NULL OR books.id IS NULLThe result of this query would return data looking something like this:
该查询的结果将返回如下所示的数据：
idfirstnamelastnamebook_idid1title1JeffreySnow0nullnullnullnullnullnull3Little Womennullnullnullnull4Tom Sawyer ID 名字 姓 book_id id1 标题 1个 杰弗里 雪 0 空值 空值 空值 空值 空值 空值 3 小女人 空值 空值 空值 空值 4 汤姆·索亚You can achieve an OUTER JOIN using the method Craig Buckler explained with a UNION, but that is not without potential issues. The top Google result for simulating a FULL OUTER JOIN in MySQL is from 2006 and can be found here. It can get somewhat complicated.
您可以使用Craig Buckler用UNION解释的方法实现OUTER JOIN ，但这并非没有潜在的问题。 Google在MySQL中模拟FULL OUTER JOIN的最高结果是从2006年开始，可以在这里找到。 它可能会变得有些复杂。
The main thing to notice in the above code is checking for NULL on both sides of the JOIN, because we want to exclude from both tables. Without checking both sides, we’d simply end up with one of the other JOINs just talked about.
在上面的代码中要注意的主要事情是检查JOIN两侧的NULL，因为我们想从两个表中排除。 如果不检查双方，我们只会得出刚才讨论的其他JOIN之一。
Regardless of how bizarre or supported a query like this one is, I wanted to include it because it is a valid type of JOIN, if you can think of a reason to use it.
不管这样的查询多么奇怪或多么受支持，我都想包含它，因为它是有效的JOIN类型，如果您可以考虑使用它的原因。
You should always use your WHERE clause against fields which cannot have NULL as their actual value! We always tested against the ID fields which cannot have NULL as a value. Imagine if our books table had an ISBN field that allowed NULL. If we tested for NULL using that field, it would include rows we may not want!
您应该始终对不能具有NULL作为实际值的字段使用WHERE子句！ 我们总是针对ID字段进行测试，这些ID字段不能使用NULL作为值。 想象一下，如果我们的books表中有一个允许为NULL的ISBN字段。 如果我们使用该字段测试NULL，它将包含我们可能不想要的行！
There is another JOIN called a CROSS JOIN which is also strange and unique. Imagine instead of just matching one user to one book, EVERY user were matched against EVERY book! Yes, this means if you have 20 books and 30 customers, a CROSS JOIN would result in 30*20 rows of data! For an example of how this could be useful, check out this article.
还有一种JOIN称为CROSS JOIN这也是奇怪的和独特的。 想象一下，不仅仅是将一个用户匹配到一本书，而是将每个用户都与每本书匹配！ 是的，这意味着如果您有20本书和30位客户，则CROSS JOIN将导致30 * 20行数据！ 有关如何使用它的示例，请参阅本文 。
Note that in MySQL, the JOIN, INNER JOIN, and CROSS JOIN are syntactical equivalents and can replace each other. This is because JOIN and INNER JOIN do the same thing and must use the ON keyword to match columns. When using a CROSS JOIN, there is no ON keyword, as it is matching every row in table A to every row in table B.
请注意，在MySQL中， JOIN ， INNER JOIN和CROSS JOIN是语法上的等效项，可以相互替换。 这是因为JOIN和INNER JOIN做相同的事情，并且必须使用ON关键字来匹配列。 使用CROSS JOIN ，没有ON关键字，因为它使表A中的每一行与表B中的每一行匹配。
I hope these extra few JOINs made sense to you. Think about using them any time you are asking for data between tables where something “doesn’t” match the other.
我希望这些多余的联接对您有意义。 当您在表之间某些“不匹配”的数据之间查询数据时，请考虑使用它们。
“Find all customers who have NOT ordered before”. “查找所有之前未订购的客户”。 “Find all customers NOT in the blacklist”. “查找不在黑名单中的所有客户”。 “Find all products that have NOT sold”. “查找所有未售出的产品”。 “Find all customers NOT borrowing books”. “找到所有不借书的客户”。 “Find all dogs that HAVEN’T been walked lately”. “找到最近没有走路的所有狗”。 “Find employees who have NEVER sent in a support ticket”. “找到从未发送过支持票的员工”。In summary, depending on how your database and tables are defined, you may need to use the WHERE clause to check for NULL values to exclude the matches rather than include them as with normal JOIN behavior.
总之，根据数据库和表的定义方式，您可能需要使用WHERE子句检查NULL值以排除匹配项，而不是像普通的JOIN行为那样包括它们。
So… have you ever needed a cross join? Any other specific use cases you’d like to tell us about or want to us to cover? Let us know!
所以...您是否曾经需要交叉加入？ 您想告诉我们或希望我们介绍其他任何特定的用例吗？ 让我们知道！
翻译自: https://www.sitepoint.com/3-more-joins-you-should-be-familiar-with/
快速熟悉每个工厂
相关资源：jdk-8u281-windows-x64.exe