sql内连接、外连接区别

骑猪看日落 2022-08-24 14:29 184阅读 0赞

原文链接：http://www.16kan.com/question/detail/320766.html

<table style="margin:0px; padding:0px; line-height:25px; table-layout:fixed; width:655px; word-break:break-all; overflow:hidden; color:rgb(0,0,0); font-family:'Microsoft Yahei',Arial,Helvetica,sans-serif; font-size:13px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 
 <div style="margin:0px; padding:10px 0px; font-size:1.2em; word-wrap:break-word; word-break:normal"> 
 <div style="margin:0px; padding:0px"> 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">Introduction介绍</h2> 
 The first thing we learn to do with SQL is writing a&nbsp;<code style="margin:0px; padding:0px">SELECT</code>&nbsp;statement to get data from one table.第一件事情，我们学会了与SQL写一个<code style="margin:0px; padding:0px">SELECT</code>语句从一个表中获取数据。&nbsp;This kind of statement seems to be straightforward and very close to the language we speak.这样的说法，似乎是简单，非常接近我们讲的语言。 
 But real-world queries are often much more sophisticated than those simple&nbsp;<code style="margin:0px; padding:0px">SELECT</code>statements.但是，现实世界中的查询往往是那些简单的<code style="margin:0px; padding:0px">SELECT</code>语句要比这复杂得多。 
 First of all, usually the data we need is split into several different tables.首先，我们需要的数据被分成几个不同的表。&nbsp;This is a natural consequence of data normalization, which is an essential feature of any well designed database model.这是一个自然的结果数据正常化，这是任何设计良好的数据库模型的一个基本特征。&nbsp;And SQL gives you the power to put that data together.而SQL给你的权力，把这些数据放在一起。 
 In the past, DBAs and developers used to put all necessary tables and/or views in the FROM clause and then use the&nbsp;<code style="margin:0px; padding:0px">WHERE</code>&nbsp;clause to define how the records from each table would combine with the other records.在过去，把所有必要的表和/或视图的FROM子句中，然后使用<code style="margin:0px; padding:0px">WHERE</code>子句来定义如何从每个表中的记录与其他记录相结合的DBA和开发人员使用。&nbsp;(To make this text a bit more readable, from now on, I will simplify things and say "table" instead of "table and/or view").&nbsp;（文字有点更具可读性，从现在开始，我将事情简化，说：“表”，而不是“表和/或视图”）。 
 But there's been a long time since we have a standard for bringing these data together.但也有是一个漫长的时间，因为我们有一个标准的，将这些数据放在一起。&nbsp;And this is done using the JOIN operator (ANSI-SQL 92).这样做是使用JOIN运算符（ANSI-SQL 92）。&nbsp;Unfortunately, there are some details about JOIN operators that remain obscure for many people.不幸的是，有一些细节注册运营商对很多人来说仍不清楚。 
 Below I will show different syntaxes of joins supported by T-SQL (that is SQL Server 2008).下面，我将表现出不同的语法的连接支持的T-SQL（也就是SQL Server 2008中）。&nbsp;I will outline a few concepts I believe they shouldn't be forgot each time we combine the data from two tables or more.我会提出一些概念，我认为他们不应该被忘记，每次我们从两个表或者更多的数据结合起来。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">Getting Started: 1&nbsp;Table&nbsp;, no Join&nbsp;入门：1表中，没有加入</h2> 
 When you have only one object to query, the syntax will be quite simple and no join will be used.当你只有一个对象来查询，语法是很简单的，没有连接将使用。&nbsp;The statement will be the good and old "&nbsp;SELECT fields FROM object&nbsp;" plus any other optional clause you might want to use (that is&nbsp;<code style="margin:0px; padding:0px">WHERE</code>&nbsp;,&nbsp;<code style="margin:0px; padding:0px">GROUP BY</code>&nbsp;,&nbsp;<code style="margin:0px; padding:0px">HAVING</code>&nbsp;, or&nbsp;<code style="margin:0px; padding:0px">ORDER BY</code>&nbsp;).该语句将是良好的和老的“&nbsp;选择字段对象&nbsp;”加任何其他的可选条款，你可能想使用（即<code style="margin:0px; padding:0px">WHERE</code>&nbsp;，&nbsp;<code style="margin:0px; padding:0px">GROUP BY</code>&nbsp;，&nbsp;<code style="margin:0px; padding:0px">HAVING</code>或<code style="margin:0px; padding:0px">ORDER BY</code>&nbsp;）。 
 One thing that end users don't know is that we DBAs usually hide lots of complex joins under one nice and easy-to-use view.最终用户不知道的一件事是，我们的DBA通常会隐藏很多复杂的连接下一个美好的和易于使用的视图。&nbsp;This is done for several reasons, ranging from data security to database performance.这样做有几个原因，包括从数据安全性，数据库的性能。&nbsp;For instance, DBAs can give permissions for the end users to access one single view instead of several production tables, obviously increasing data security.例如，DBA可以授予权限的最终用户访问一个单一的视图，而不是几个生产表，明显地提高了数据的安全性。&nbsp;Or considering performance, DBAs can create a view using the right parameters to join the records from several tables, correctly using database indexes and thus boosting query performance.或者考虑性能，DBA可以创建一个视图使用正确的参数，从几个表中加入记录，正确地使用数据库索引，从而提高查询性能。 
 All in all, joins might be there in the database even when the end users don't see them.所有的一切，加入但有可能会在数据库中，即使最终用户看不到它们。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The Logic Behind&nbsp;Joining&nbsp;Tables&nbsp;其背后的逻辑连接表</h2> 
 Many years ago, when I started working with SQL, I learned there were several types of joins.许多年前，当我开始工作与SQL，我学会了有几种类型的连接。&nbsp;But it took me some time to understand what exactly I was doing when I brought those tables together.但我花了一些时间来了解究竟我在做什么，当我把这些表一起。&nbsp;Maybe because people are so scared of mathematics, it is not frequently said that the whole idea behind joining tables is about&nbsp;<a href="http://translate.googleusercontent.com/translate_c?act=url&amp;depth=1&amp;hl=en&amp;ie=UTF8&amp;prev=_t&amp;rurl=74.125.128.160&amp;sl=en&amp;tl=zh-CN&amp;u=http://en.wikipedia.org/wiki/Set_theory&amp;usg=ALkJrhjiUVwSEBXEQ8b8Yf0VKMSpghnA_A" style="margin:0px; padding:0px; color:rgb(25,85,141); text-decoration:none; line-height:15.600000381469727px" rel="nofollow">Set Theory</a>&nbsp;.也许是因为人们是如此害怕的数学，它是不是经常说，连接表背后的思想是关于<a href="http://translate.googleusercontent.com/translate_c?act=url&amp;depth=1&amp;hl=en&amp;ie=UTF8&amp;prev=_t&amp;rurl=74.125.128.160&amp;sl=en&amp;tl=zh-CN&amp;u=http://en.wikipedia.org/wiki/Set_theory&amp;usg=ALkJrhjiUVwSEBXEQ8b8Yf0VKMSpghnA_A" style="margin:0px; padding:0px; color:rgb(25,85,141); text-decoration:none; line-height:15.600000381469727px" rel="nofollow">设置理论</a>&nbsp;。&nbsp;Despite the fancy name, the concept is so simple we are taught it in elementary school.尽管花哨的名字，这个概念是如此简单，我们的教育则是在小学。 
 The drawing in Figure 1 is quite similar to the ones found in my kids' books from First Grade.图1中的图是相当类似的发现在我的孩子们从小学一年级的书。&nbsp;The idea is to find correspondent objects in the different sets.我们的想法是在不同组中找到对应的对象。&nbsp;Well, this is precisely what we do with SQL JOINs!好了，这正是我们做的与SQL连接！ 
 Once you understand the analogy, things will start to make sense.一旦你理解的比喻，事情就开始变得有意义。 
 Consider that the 2 sets in are tables and the numbers we see are the keys we will use to join the tables.考虑2台表和数字，我们看到的关键是，我们将使用连接表。&nbsp;So in each set, instead of representing the whole records, we are only seeing the key fields from each table.因此，在每一组中，而不是代表整个记录，我们只看到从每个表中的重点领域。&nbsp;The result set of this combination will be determined by the type of join we consider, and this is the topic I will show now.这个组合的结果将决定由加入我们考虑的类型，这是我现在将显示的主题。&nbsp;To illustrate our examples, consider we have 2 tables, shown below:为了说明我们的例子中，我们认为有2个表，如下图所示： 
 <div style="margin:0px; padding:0px"> 
 Table 1表1 
 </div> 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> key1KEY1 </td> 
 <td style="margin:0px; padding:0px"> field1FIELD1 </td> 
 <td style="margin:0px; padding:0px"> field2field2的 </td> 
 <td style="margin:0px; padding:0px"> key2KEY2 </td> 
 <td style="margin:0px; padding:0px"> key3KEY3 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> Erik埃里克· </td> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> John约翰 </td> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Mark马克 </td> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> 7&nbsp;7 </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 7&nbsp;7 </td> 
 <td style="margin:0px; padding:0px"> Peter彼得 </td> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> 5&nbsp;5 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> Harry掠夺 </td> 
 <td style="margin:0px; padding:0px"> 0&nbsp;0 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> 2&nbsp;2 </td> 
 </tr> 
 </tbody> 
 </table> 
 <div style="margin:0px; padding:0px"> 
 Table 2表2 
 </div> 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> key2KEY2 </td> 
 <td style="margin:0px; padding:0px"> field1&nbsp;FIELD1 </td> 
 <td style="margin:0px; padding:0px"> field2field2的 </td> 
 <td style="margin:0px; padding:0px"> field3FIELD3 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> New York纽约 </td> 
 <td style="margin:0px; padding:0px"> A一 </td> 
 <td style="margin:0px; padding:0px"> N&nbsp;Ň </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 2&nbsp;2 </td> 
 <td style="margin:0px; padding:0px"> Sao Paulo圣保罗 </td> 
 <td style="margin:0px; padding:0px"> B乙 </td> 
 <td style="margin:0px; padding:0px"> N&nbsp;Ň </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> Paris巴黎 </td> 
 <td style="margin:0px; padding:0px"> C&nbsp;Ç </td> 
 <td style="margin:0px; padding:0px"> Y&nbsp;Ÿ </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 5&nbsp;5 </td> 
 <td style="margin:0px; padding:0px"> London伦敦 </td> 
 <td style="margin:0px; padding:0px"> C&nbsp;Ç </td> 
 <td style="margin:0px; padding:0px"> Y&nbsp;Ÿ </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Rome罗马 </td> 
 <td style="margin:0px; padding:0px"> C&nbsp;Ç </td> 
 <td style="margin:0px; padding:0px"> Y&nbsp;Ÿ </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> Madrid马德里 </td> 
 <td style="margin:0px; padding:0px"> C&nbsp;Ç </td> 
 <td style="margin:0px; padding:0px"> Y&nbsp;Ÿ </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 0&nbsp;0 </td> 
 <td style="margin:0px; padding:0px"> Bangalore班加罗尔 </td> 
 <td style="margin:0px; padding:0px"> D&nbsp;ð </td> 
 <td style="margin:0px; padding:0px"> N&nbsp;Ň </td> 
 </tr> 
 </tbody> 
 </table> 
 The script to create and populate those tables are available as one attached file (SQLServerCentral.com_JOIN.sql&nbsp;) in the Resources section below.脚本来创建和填充这些表可以作为一个附加的文件（SQLServerCentral.com_JOIN.sql）在下面的参考资料部分。 
 You will notice this script does not fully implement referential integrity.你会发现这个脚本没有完全实现参照完整性。&nbsp;I intentionally left the tables without foreign keys to better explain the functionality of the different types of joins.我有意忽略了外键的表，而更好地解释不同类型的连接功能。&nbsp;But I did so for didactical purposes only.但我这样做是出于教学的目的。&nbsp;Foreign keys are extremely useful to guarantee data consistency and they should not be left out of any real-world database.外键是非常有用的，以保证数据的一致性，他们不应该被排除在任何现实世界的数据库。 
 Well, now we are ready to go.好了，现在我们已经准备好了。&nbsp;Let's check the types of joins we can use in T-SQL, the correspondent syntax and the result set that each one will generate.让我们来看看类型的连接，对应的T-SQL的语法和每个人会产生的结果集，我们可以在。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The Inner Join&nbsp;INNER JOIN</h2> 
 This is the most common join we use in SQL.这是最常见的连接，我们使用SQL。&nbsp;It returns the intersection of two sets.它返回两个集合的交集。&nbsp;Or in terms of tables, it brings only the records from both tables that match a given criteria.或者在表的条款，将只匹配给定的标准，从两个表中的记录。 
 We can see in Figure 2 the Venn diagram illustrating the inner join of two tables.在图2中，我们可以看到，维恩图，图内加入两个表。&nbsp;The result set of the operation is the area in red.设置的操作的结果是在红色的区域。 
 <img width="301" height="222" src="http://www.codeproject.com/KB/database/485777/image001.jpg" alt="" style="margin:0px; padding:0px; border:0px"> 
 <div style="margin:0px; padding:0px"> 
 Figure 2: Representing the INNER JOIN图2：代表INNER JOIN 
 </div> 
 Now check the syntax to combine the data from&nbsp;Table1&nbsp;and&nbsp;Table2&nbsp;using an INNER JOIN.现在检查从表1和表2中的数据结合起来使用INNER JOIN语法。 
 <pre><code> SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, SELECT t1.key1， 作为 T1Key t1.field1 作为名称，t1.key2的， 
 t2.key2 as T2Key, t2.field1 as City 作为城市t2.key2 为 T2Key，t2.field1
FROM Table1 t1 从表1 T1
 INNER JOIN Table2 t2 ON t1.key2 = t2.key2; INNER JOIN表2 T2，ON t1.key2 = t2.key2; </code></pre> 
 The result set of this statement will be:这条语句的结果将是： 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> key1KEY1 </td> 
 <td style="margin:0px; padding:0px"> Name名称 </td> 
 <td style="margin:0px; padding:0px"> T1KeyT1Key </td> 
 <td style="margin:0px; padding:0px"> T2KeyT2Key </td> 
 <td style="margin:0px; padding:0px"> City&nbsp;城市 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> Erik埃里克· </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> New York纽约 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> John约翰 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> Paris巴黎 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Harry掠夺 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> Madrid马德里 </td> 
 </tr> 
 </tbody> 
 </table> 
 Notice it returned only the data from records which have the same value for&nbsp;key2&nbsp;on both&nbsp;Table1&nbsp;and&nbsp;Table2&nbsp;.请注意，它返回的数据记录有相同的值，KEY2表1和表2。 
 Opposed to the INNER JOIN, there is also the OUTER JOIN.反对的INNER JOIN，也有OUTER JOIN。&nbsp;There are three types of OUTER JOINs, named full, left and right.有三种类型全面，左，右外连接，命名。&nbsp;We will look at each one in detail below.我们将在下面详细看，在每一个。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The FULL JOIN&nbsp;FULL JOIN</h2> 
 This is also known as the FULL OUTER JOIN (the reserved word OUTER is optional).这也被称为FULL OUTER JOIN（保留字OUTER是可选的）。&nbsp;FULL JOINs work like the union of two sets.&nbsp;FULL连接的工作原理像两个集合。&nbsp;Now we have in&nbsp;Figure 3&nbsp;the Venn diagram illustrating the FULL JOIN of two tables.现在，我们有在图3中示出FULL JOIN两个表的维恩图。&nbsp;The result set of the operation is again the area in red.再次设定的操作的结果是在红色区域。 
 <img width="301" height="222" src="http://www.codeproject.com/KB/database/485777/image002.jpg" alt="" style="margin:0px; padding:0px; border:0px"> 
 <div style="margin:0px; padding:0px"> 
 Figure 3: Representing the FULL JOIN图3：代表FULL JOIN 
 </div> 
 The syntax is almost exactly the same we saw before.的语法几乎是完全一样的，我们以前看到的。 
 <pre><code> SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, SELECT t1.key1， 作为 T1Key t1.field1 作为名称，t1.key2的， 
 t2.key2 as T2Key, t2.field1 as City 作为城市t2.key2 为 T2Key，t2.field1
FROM Table1 t1 从表1 T1
 FULL JOIN Table2 t2 ON t1.key2 = t2.key2 ; FULL JOIN表2 T2，ON t1.key2 = t2.key2; </code></pre> 
 The result set of this statement will be:这条语句的结果将是： 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> key1KEY1 </td> 
 <td style="margin:0px; padding:0px"> Name名称 </td> 
 <td style="margin:0px; padding:0px"> T1KeyT1Key </td> 
 <td style="margin:0px; padding:0px"> T2KeyT2Key </td> 
 <td style="margin:0px; padding:0px"> City&nbsp;城市 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> Erik埃里克· </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> New York纽约 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> John约翰 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> Paris巴黎 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Mark马克 </td> 
 <td style="margin:0px; padding:0px"> 7&nbsp;7 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 7&nbsp;7 </td> 
 <td style="margin:0px; padding:0px"> Peter彼得 </td> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> Harry掠夺 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> Madrid马德里 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 2&nbsp;2 </td> 
 <td style="margin:0px; padding:0px"> Sao Paulo圣保罗 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 5&nbsp;5 </td> 
 <td style="margin:0px; padding:0px"> London伦敦 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Rome罗马 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 0&nbsp;0 </td> 
 <td style="margin:0px; padding:0px"> Bangalore班加罗尔 </td> 
 </tr> 
 </tbody> 
 </table> 
 The FULL JOIN returns all records from&nbsp;Table1&nbsp;and&nbsp;Table2&nbsp;, without duplicating data.FULL JOIN返回所有的记录从表1和表2中，不复制数据。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The LEFT JOIN&nbsp;LEFT JOIN</h2> 
 Also known as LEFT OUTER JOIN, this is a particular case of the FULL JOIN.也被称为LEFT OUTER JOIN，这是一个特殊的情况下，FULL JOIN。&nbsp;It brings all requested data from the table that appears to the left of the JOIN operator, plus the data from the right table which intersects with the first one.它带来的所有请求的数据从显示的表中左侧的JOIN运算符，加右表中的数据与第一个相交。&nbsp;Below we have a Venn diagram illustrating the LEFT JOIN of two tables in Figure 4.下面，我们有一个文氏（Venn）图，示出的图4中的两个表的LEFT JOIN。 
 <img width="301" height="222" src="http://www.codeproject.com/KB/database/485777/image003.jpg" alt="" style="margin:0px; padding:0px; border:0px"> 
 <div style="margin:0px; padding:0px"> 
 Figure 4: Representing the LEFT JOIN图4：代表LEFT JOIN 
 </div> 
 See the syntax below.请参阅下面的语法。 
 <pre><code> SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, SELECT t1.key1， 作为 T1Key t1.field1 作为名称，t1.key2的， 
 t2.key2 as T2Key, t2.field1 as City 作为城市t2.key2 为 T2Key，t2.field1
FROM Table1 t1 从表1 T1
 LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 ; LEFT JOIN表2 ON t1.key2 T2 = t2.key2; </code></pre> 
 The result set of this statement will be:这条语句的结果将是： 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> key1KEY1 </td> 
 <td style="margin:0px; padding:0px"> Name名称 </td> 
 <td style="margin:0px; padding:0px"> T1KeyT1Key </td> 
 <td style="margin:0px; padding:0px"> T2KeyT2Key </td> 
 <td style="margin:0px; padding:0px"> City&nbsp;城市 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> Erik埃里克· </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> New York纽约 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> John约翰 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> Paris巴黎 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Mark马克 </td> 
 <td style="margin:0px; padding:0px"> 7&nbsp;7 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 7&nbsp;7 </td> 
 <td style="margin:0px; padding:0px"> Peter彼得 </td> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> Harry掠夺 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> Madrid马德里 </td> 
 </tr> 
 </tbody> 
 </table> 
 The third and forth records (&nbsp;key1&nbsp;equals to 6 and 7) show&nbsp;<code style="margin:0px; padding:0px">NULL</code>&nbsp;values on the last fields because there is no information to be brought from the second table.第三和第四记录（KEY1等于6和7）的最后领域，因为没有任何信息所带来的第二个表显示<code style="margin:0px; padding:0px">NULL</code>值。This means we have a value in field&nbsp;key2&nbsp;in&nbsp;Table1&nbsp;with no correspondent value inTable2&nbsp;.这意味着我们有一个价值领域KEY2在表1与表2&nbsp;中没有对应的值。&nbsp;We could have avoided this "data inconsistency" in case we had a foreign key on field&nbsp;key2&nbsp;in&nbsp;Table1.我们就可能避免这种“数据不一致”的情况下，我们有一个现场KEY2表的外键。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The RIGHT JOIN&nbsp;RIGHT JOIN</h2> 
 Also known as RIGHT OUTER JOIN, this is another particular case of the FULL JOIN.也被称为RIGHT OUTER JOIN，这是另一种特殊情况下的FULL JOIN。&nbsp;It brings all requested data from the table that appears to the right of the JOIN operator, plus the data from the left table which intersects with the right one.它带来的所有请求的数据从表中出现的JOIN操作的权利，再加上左表中的数据与正确的相交。&nbsp;The Venn diagram for the RIGHT JOIN of two tables is in Figure 5.为RIGHT JOIN两个表是的维恩图在图5中。 
 <img width="301" height="222" src="http://www.codeproject.com/KB/database/485777/image004.jpg" alt="" style="margin:0px; padding:0px; border:0px"> 
 <div style="margin:0px; padding:0px"> 
 Figure 5: Representing the RIGHT JOIN图5：代表RIGHT JOIN 
 </div> 
 As you can see, syntax is very similar.正如你可以看到，语法是非常相似的。 
 <pre><code> SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, SELECT t1.key1， 作为 T1Key t1.field1 作为名称，t1.key2的， 
 t2.key2 as T2Key, t2.field1 as City 作为城市t2.key2 为 T2Key，t2.field1
FROM Table1 t1 从表1 T1
 RIGHT JOIN Table2 t2 ON t1.key2 = t2.key2 ; RIGHT JOIN表2 T2，ON t1.key2 = t2.key2; </code></pre> 
 The result set of this statement will be:这条语句的结果将是： 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> key1KEY1 </td> 
 <td style="margin:0px; padding:0px"> Name名称 </td> 
 <td style="margin:0px; padding:0px"> T1KeyT1Key </td> 
 <td style="margin:0px; padding:0px"> T2KeyT2Key </td> 
 <td style="margin:0px; padding:0px"> City&nbsp;城市 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 0&nbsp;0 </td> 
 <td style="margin:0px; padding:0px"> Bangalore班加罗尔 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 3&nbsp;3 </td> 
 <td style="margin:0px; padding:0px"> Erik埃里克· </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> 1&nbsp;1 </td> 
 <td style="margin:0px; padding:0px"> New York纽约 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 2&nbsp;2 </td> 
 <td style="margin:0px; padding:0px"> Sao Paulo圣保罗 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> John约翰 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> 4&nbsp;4 </td> 
 <td style="margin:0px; padding:0px"> Paris巴黎 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 5&nbsp;5 </td> 
 <td style="margin:0px; padding:0px"> London伦敦 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> null空 </td> 
 <td style="margin:0px; padding:0px"> 6&nbsp;6 </td> 
 <td style="margin:0px; padding:0px"> Rome罗马 </td> 
 </tr> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> 8&nbsp;8 </td> 
 <td style="margin:0px; padding:0px"> Harry掠夺 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> 9&nbsp;9 </td> 
 <td style="margin:0px; padding:0px"> Madrid马德里 </td> 
 </tr> 
 </tbody> 
 </table> 
 Observe now that records with&nbsp;key1&nbsp;equal to 6 and 7 no longer appear in the result set.观察KEY1等于6和7的记录不再出现在结果集中。&nbsp;This is because they have no correspondent record in the right table.这是因为他们没有记者在右表中的记录。&nbsp;There are 4 records showing NULL values on the first fields, because they are not available in the left table.有4条记录的第一个字段显示NULL值，因为它们不是在左表中。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The CROSS JOIN&nbsp;CROSS JOIN</h2> 
 A CROSS JOIN is in fact a Cartesian product.实际上是一个笛卡尔积的交叉连接。&nbsp;Using CROSS JOIN generates exactly the same output of calling two tables (separated by a comma) without any JOIN at all.使用CROSS JOIN产生完全相同的输出没有任何连接在所有调用两个表（以逗号分隔）。&nbsp;This means we will get a huge result set, where each record of&nbsp;Table1&nbsp;will be duplicated for each record in&nbsp;Table2&nbsp;.这意味着我们将得到一个巨大的结果集，其中每个记录的表1，表2中的每个记录将被复制。&nbsp;If&nbsp;Table1&nbsp;has N1 records and&nbsp;Table2&nbsp;has N2 records, the output will have N1 times N2 records.&nbsp;如果Table1有N1记录和&nbsp;Table2有N2个记录，输出将有N1次N2记录。 
 I don't believe there is any way to represent this output in a Venn diagram.我不相信有什么办法来表示这个输出在维恩图中。&nbsp;I guess it would be a three dimensional image.我想这将是一个三维图像。&nbsp;If this is really the case, the diagram would be more confusing than explanatory.如果真的是这样的情况下，图是比解释更令人困惑。 
 The syntax for a CROSS JOIN will be:一个CROSS JOIN的语法是： 
 <pre><code> SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, SELECT t1.key1， 作为 T1Key t1.field1 作为名称，t1.key2的， 
 t2.key2 as T2Key, t2.field1 as City 作为城市t2.key2 为 T2Key，t2.field1
FROM Table1 t1 从表1 T1
 CROSS JOIN Table2 t2 ; CROSS JOIN表2 T2; </code></pre> 
 As&nbsp;Table1&nbsp;has 5 records and&nbsp;Table2&nbsp;has another 7, the output for this query will have 35 records (5 x 7).&nbsp;表1有5条记录，&nbsp;表2有另外7个，将有35条记录，这个查询的输出（5×7）。 
 Please check the attached file (&nbsp;SQLServerCentral.com_JOIN_CrossJoin.rpt&nbsp;).请检查的附加 的文件（SQLServerCentral.com_JOIN_CrossJoin.rpt）。 
 Quite honestly, I don't remember at this very moment not a single real-life situation that I do need to generate a Cartesian product of two tables.坦率地说，我不记得在这个非常时刻没有任何一个现实生活中的情况，我需要生成两个表的笛卡尔乘积。&nbsp;But whenever you need, CROSS JOIN is there, anyway.但是，只要你需要，CROSS JOIN在那里，反正。 
 Besides, you should be concerned about performance.此外，你应该关心的是性能。Say you accidentally run in your production server a query with a CROSS JOIN over two tables with 1 million records.说你不小心运行在生产服务器上的交叉连接两个表100万条记录的查询。&nbsp;This is surely something that will give you a headache.这是肯定的东西，将让你头痛。&nbsp;Probably your server will start showing performance problems, as your query might run for some time consuming a considerable amount of the server resources.也许你的服务器将开始显示性能方面的问题，您的查询可能运行了一段时间，消耗了大量的服务器资源。 
 <h2 style="margin:20px 0px 11px; padding:0px; font-size:20px">The SELF JOIN自联接</h2> 
 The JOIN operator can be used to combine any pair of tables, including combining the table to itself.&nbsp;JOIN运算符可以使用任意组合对表，包括结合表本身。&nbsp;This is the "self join".这是“自我加入”。&nbsp;Self joins can use any JOIN operator.自联接可以使用任何JOIN操作。 
 For instance, check this classical example of returning an employee's boss (based onTable1&nbsp;).例如，检查返回雇员的老板（&nbsp;表1）这个经典的例子。&nbsp;In this example, we consider that the value in&nbsp;field2&nbsp;is in fact the boss' code number, therefore related tokey1&nbsp;.在这个例子中，我们认为，在field2的值实际上是在老板的码数，因此，相关的KEY1。 
 <pre><code> SELECT t1.key1, t1.field1 as Name, 选择在名字t1.key1，t1.field1， 
 t1.field2, mirror.field1 as Boss t1.field2，mirror.field1 当老板
FROM Table1 t1 从表1 T1
 LEFT JOIN Table1 mirror ON t1.field2 = mirror.key1; LEFT JOIN表镜上 t1.field2 = mirror.key1; </code></pre> 
 And this is the output to this query.这是这个查询的输出。 
 <table style="margin:0px; padding:0px; line-height:25px"> 
 <tbody style="margin:0px; padding:0px"> 
 <tr style="margin:0px; padding:0px"> 
 <td style="margin:0px; padding:0px"> &lt;span class="google-src-text" left"="" style="margin: 0px; padding: 0px; direction: ltr;"&gt;来源：<a href="http://www.codeproject.com/Articles/485777/SQL-Joins" style="margin:0px; padding:0px; color:rgb(25,85,141); text-decoration:none; line-height:15.600000381469727px" rel="nofollow">http://www.codeproject.com/Articles/485777/SQL-Joins</a> </td> 
 </tr> 
 </tbody> 
 </table> 
 </div> 
 </div> </td> 
 </tr> 
 </tbody> 
</table>