{"id":93971,"date":"2022-04-21T18:40:53","date_gmt":"2022-04-21T18:40:53","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93971"},"modified":"2022-04-21T18:40:53","modified_gmt":"2022-04-21T18:40:53","slug":"the-ins-and-outs-of-joining-tables-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/the-ins-and-outs-of-joining-tables-in-sql-server\/","title":{"rendered":"The ins and outs of joining tables in SQL Server"},"content":{"rendered":"

There are times when you need to join multiple tables to retrieve the result set needed. SQL allows for multiple tables to be included in the FROM<\/code> clause along with the criteria for how to join tables. In SQL Server there are a number of different ways to join tables. In this article, I will be discussing the ins and outs of joining two tables in SQL Server using the inner and outer join operators.<\/p>\n

The Three Parts of a Join Operations<\/h2>\n

When two tables are joined using a join operation, the records of the two sets are logically separated into three different buckets. Those different buckets can be represented using a Venn diagram<\/a> like the one in Figure 1.<\/p>\n

\"An<\/p>\n

Figure 1: Three parts of the JOIN Operation<\/strong><\/p>\n

Each section of the Venn diagram is represented by a different color: yellow, brown, and blue. The members of Set A that don\u2019t match with SET B will be contained in the yellow bucket. Members of SET A and B that match end up in the brown bucket. Lastly, the members in the blue bucket are those members in SET B that don\u2019t match with SET A. The Venn diagram can visually represent the resulting sets created when an inner or outer join operation is performed.<\/p>\n

Test Data<\/h2>\n

To demonstrate how the different inner and outer join operations work, the examples use two test data tables created in the tempdb<\/em> database. These two tables are SetA <\/em>and SetB. <\/em>If you want to follow along and execute the examples in this article, you can create the two test tables using the code in Listing 1.<\/p>\n

Listing 1: Creating SetA <\/em>and SetB<\/em><\/strong><\/p>\n

USE tempdb;\r\nGO\r\nCREATE TABLE SetA (Col1 tinyint, Col2 char(1));\r\nINSERT INTO SetA VALUES \r\n\t(1, 'A'), (2, 'B');\r\nCREATE TABLE SetB (ColA tinyint, ColB char(1), ColC char(1));\r\nINSERT INTO SetB VALUES\r\n\t(1, 'A', 'B'), (2, 'C', 'D'), (3, 'E', 'F');\r\nGO<\/pre>\n
The two tables created when Listing 1 is run can be represented by the Venn diagram in Figure 2.<\/p>\n
\"An<\/p>\n
Figure 2: Tables SetA <\/em>and SetB<\/em><\/strong><\/p>\n
Table SetA<\/em> consists of a set with two columns (Col1<\/code> and Col2<\/code>) and two different rows. Table SetB<\/em> has three columns (ColA<\/code>, ColB<\/code>, and ColC<\/code>) and contains three different rows.<\/p>\n
INNER JOIN operator<\/h2>\n
The INNER JOIN operator is probably the most used join operation found in T-SQL code. This operator returns rows that match when two sets are joined based on one or more search criteria. The brown section in Figure 3 represents the result set of an INNER JOIN operation between the two test data tables, SetA <\/em>and SetB, <\/em>based on the search criterion SetA.Col2 = SetB.ColB<\/code>.<\/em><\/p>\n
\"An 
\nFigure 3: Venn Diagram representing an INNER JOIN <\/strong><\/p>\n
The T-SQL code in Listing 2 demonstrates one way to write an INNER<\/code> JOIN<\/code> operation to return the matching rows found in the brown section of the Venn diagram in Figure 3.<\/p>\n
Listing 2: Performing INNER JOIN operation<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT * FROM SetA INNER JOIN SetB\r\nON SetA.Col2 = SetB.ColB;\r\nGO <\/pre>\n
Report 1 shows the results when the code in Listing 2 is executed.<\/p>\n
Report 1: Output produced when Listing 2 is run<\/strong><\/p>\n
<\/p>\n
The code in Listing 2 identifies the two tables, SetA <\/em>and SetB<\/em>, to be joined in the FROM clause, with the INNER<\/code> JOIN<\/code> operator between them, followed by an ON clause. The ON clause identifies the search criteria to be used when joining the two tables. In Listing 2, the ON clause specifies SetA.Col2 = SetB.ColB<\/code> as the search criterion. This criterion specifies that the INNER JOIN operation is to find all the rows in SetA<\/em> that have corresponding matching rows in SetB, <\/em>where Col2<\/code> in SetA <\/em>is equal to ColB<\/code> in SetB<\/em>. Let\u2019s walk through how this join operation works.<\/p>\n
While the technical details of how the engine joins the tables together are beyond the scope of this article, logically, SQL Server has to compare each row in SetA <\/em>with each row in SetB <\/em>to determine which rows match the join condition. To perform this comparison, SQL Server first finds the value for Col2<\/code> in the first row of SetA, <\/em>which has a value of A<\/em>. Next SQL Server scans the rows in SetB<\/em> to determine if any ColB<\/code> values match the value of A<\/em>. SQL Server only finds a single row in SetB <\/em>with a value of A<\/em> in ColB<\/code>. <\/em>SQL server then repeats this compare process between SetA <\/em>and SetB <\/em>for each row in SetA, <\/em>trying to find matches. The second row in SetA <\/em>doesn\u2019t match with any rows in SetB. <\/em>Once all the comparisons between SetA <\/em>and SetB <\/em>are completed, the column values from matching rows between SetA <\/em>and SetB<\/em> are merged, and the final results of the INNER<\/code> JOIN<\/code> are returned to the calling program.<\/p>\n
The matching search criterion used in Listing 2 uses SQL-92 join syntax. This is not the only join syntax supported by SQL Server.<\/p>\n
Comparing join syntax between versions of SQL<\/h2>\n
In the early days of SQL, the various database vendors started implementing SQL code differently. This led to inconsistencies in how SQL code was written. In the 1980s, the American National Standards Institute (ANSI) started working on a SQL language standard. Over time the SQL standards changed the way joins could be written.<\/p>\n
The code in Listing 2 above uses SQL-92 join syntax, which requires a join operator to be specified between the joined sets and an ON clause to identify the join criteria. Prior to SQL-92, a different syntax for joining data was used which is shown in Listing 3.<\/p>\n
Listing 3: Performing INNER JOIN using SQL-86 join syntax<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA, SetB\r\nWHERE SetA.Col2 = SetB.ColB;\r\nGO<\/pre>\n
The pre-SQL-92 syntax required a comma to be placed between tables being joined and required the join criteria be specified in the WHERE<\/code> clause. By moving the join conditions to the ON clause, the SQL-92 standards separated the join conditions from other search conditions a query might have in the WHERE<\/code> clause. SQL Server still supports the older method of doing the INNER<\/code> JOIN<\/code> operation using pre-SQL-92 syntax, but it no longer supports the outer join method using the pre-SQL-92 syntax. SQL-92 standard is now the recommended standard for doing join operations. For the rest of the join examples in this article, only the ANSI SQL-92 syntax will be shown.<\/p>\n
The INNER<\/code> JOIN<\/code> operation returns rows with matching members in both the left and right sides of joined sets. However, there are times when rows might need to be included in result sets even when they don\u2019t match. This is where the outer join operations can help.<\/p>\n
LEFT OUTER JOIN<\/h2>\n
The LEFT<\/code> OUTER<\/code> JOIN<\/code> operation returns all the rows from the table on the left, even when they don\u2019t match any of the rows on the right based on the search criteria. The Venn diagram in Figure 4 logically shows the results of a LEFT<\/code> OUTER<\/code> JOIN<\/code> operation between SetA <\/em>and SetB<\/em> based the search criterion of SetA.Col2 = SetB.ColB<\/code>.<\/em><\/p>\n
\"An<\/p>\n
Figure 4: Venn diagram representing LEFT OUTER JOIN<\/strong><\/p>\n
By comparing Figure 4 to the Venn diagram for the INNER<\/code> JOIN<\/code> found in Figure 3, the matched row of SetA, <\/em>in the brown section, and the unmatched members in the yellow section of SetA <\/em>have been combined into a single yellow circle. The members of this yellow circle are the members of SetA <\/em>and SetB <\/em>that will be returned from the LEFT<\/code> OUTER<\/code> JOIN<\/code> operations. Listing 4 contains the T-SQL code to produce the LEFT<\/code> OUTER<\/code> JOIN<\/code> results represented by Figure 4.<\/p>\n
Listing 4: LEFT OUTER JOIN<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA LEFT OUTER JOIN SetB\r\nON SetA.Col2 = SetB.ColB;\r\nGO<\/pre>\n
Report 2 shows the output when Listing 4 is executed.<\/p>\n
Report 2: Output from running Listing 4<\/strong><\/p>\n
<\/p>\n
The results in Report 2 have one row that matched the search criterium and one that didn\u2019t match. The row that didn\u2019t match the column values for the table on the right side of the LEFT<\/code> OUTER<\/code> JOIN<\/code> operation is set to NULL<\/code>. It is easy to identify those rows that did not match the search criterium by checking to see if any of the columns from the right table is null, as done in Listing 5.<\/p>\n
Listing 5: Finding rows on the left that don\u2019t match rows on the right<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA LEFT OUTER JOIN SetB\r\nON SetA.Col2 = SetB.ColB\r\nWHERE SetB.ColA is null;\r\nGO<\/pre>\n
When the code in Listing 5 is run, only the last row in Report 2 will be returned.<\/p>\n
RIGHT OUTER JOIN<\/h2>\n
The RIGHT<\/code> OUTER<\/code> JOIN<\/code> operation is similar to the LEFT<\/code> OUTER<\/code> JOIN<\/code> operation. The difference is that all the rows in the right table are returned, whether or not they match the rows in the left table. To show this consider the code in Listing 6.<\/p>\n
Listing 6: Right outer join example<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA \r\nRIGHT OUTER JOIN SetB\r\nON SetA.Col2 = SetB.ColB;\r\nGO<\/pre>\n
Report 3 shows the results when Listing 6 is executed.<\/p>\n
Report 3: Results when Listing 6 is run<\/strong><\/p>\n
<\/p>\n
Report 3 shows that all the rows from the right table, SetB<\/em>, are returned. For those rows in SetB<\/em> that don\u2019t match SetA,<\/em> the NULL<\/code> value was returned for the SetA <\/em>columns in the result set.<\/p>\n
If you need to find all the rows in SetB <\/em>that don\u2019t match the search criterion of SetA.Col2 = SetB.ColB<\/code>, <\/em>then the code in Listing 7 can be run.<\/p>\n
Listing 7: Finding rows in SetB <\/em>that don\u2019t match SetA<\/em><\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA \r\nRIGHT OUTER JOIN SetB\r\nON SetA.Col2 = SetB.ColB\r\nWHERE SetA.Col1 is null;\r\nGO<\/pre>\n
Report 4 shows the results produced when Listing 7 is executed.<\/p>\n
Report 4: Results from running Listing 7<\/strong><\/p>\n
<\/p>\n
FULL OUTER JOIN<\/h2>\n
The FULL<\/code> OUTER<\/code> JOIN<\/code> operation combines both the LEFT<\/code> and RIGHT<\/code> OUTER<\/code> JOIN and the INNER<\/code> JOIN<\/code> in a single operation. Listing 8 uses the FULL<\/code> OUTER<\/code> JOIN<\/code> operator to find all the rows that match and don\u2019t match the search criterion SetA.Col2 = SetB.ColB<\/code>.<\/em><\/p>\n
Listing 8: Finding all matching and unmatched rows<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA FULL OUTER JOIN SetB\r\nON SetA.Col2 = SetB.ColB;\r\nGO<\/pre>\n
Report 5 shows the results when Listing 7 is run.<\/p>\n
Report 5: Output from Listing 8<\/strong><\/p>\n
<\/p>\n
The first row in Report 5 is the only row that meets the search criterion identified in Listing 8, hence why each column has a value. The second row is the left row that doesn\u2019t match any rows on the right; it could be found using the LEFT<\/code> OUTER<\/code> JOIN<\/code>. The third and fourth rows are the rows from the right that don\u2019t match the rows from the left; they could be found using a RIGHT<\/code> OUTER<\/code> JOIN<\/code> operation.<\/p>\n
The code in Listing 9 shows how to find only those rows in either the left or right table that don\u2019t match the search criterion.<\/p>\n
Listing 9: Using the FULL OUTER JOIN<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT * FROM SetA FULL OUTER JOIN SetB\r\nON SetA.Col2 = SetB.ColB\r\nWHERE SetA.Col1 is null or SetB.ColA is null;\r\nGO<\/pre>\n
Report 6 shows the output when Listing 9 is executed.<\/p>\n
Report 6: Output when Listing 9 is run<\/strong><\/p>\n
<\/p>\n
The rows from the left table that don\u2019t match rows in the right table will have nulls in the columns from the right table. Inversely, rows from the right table that don\u2019t match rows in the left table will have nulls identified for all the columns from the left table.<\/p>\n
Using Multiple comparison operations<\/h2>\n
The comparison between two sets in a join operation can also have multiple comparison operations, like the code in Listing 10.<\/p>\n
Listing 10: Using multiple comparison operators in a join operation<\/strong><\/p>\n
USE tempdb;\r\nGO\r\nSELECT * FROM SetA INNER JOIN SetB \r\nON SetA.Col1 = SetB.ColA AND SetA.Col2 = SetB.ColB;\r\nGO<\/pre>\n
When Listing 10 is executed the results in Report 7 is produced.<\/p>\n
Report 7: Output when Listing 10 is run<\/strong><\/p>\n
<\/p>\n
In Listing 10 only the rows where both Col1<\/code> from Set1 matches ColA<\/code> from SetB and Col2<\/code> from SetA matches ColB<\/code> from SetB.<\/p>\n
The Ins and Outs of Joining tables<\/h2>\n
Joining tables is a key skill every T-SQL programmer needs to master. In this article, I used Venn diagrams and T-SQL code examples to show on how the INNER<\/code> JOIN<\/code>, LEFT<\/code> OUTER<\/code> JOIN<\/code>, RIGHT<\/code> OUTER<\/code> JOIN<\/code> and FULL<\/code> OUTER<\/code> JOIN<\/code> operations can be used to find different sets of records when joining two sets. Hopefully, next time you need to find the rows in one of the three parts produced when joining two sets you will know which join operation and search criteria to use.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Most database queries require joining tables. In this article, Greg Larsen explains the ins and outs of joining tables in SQL Server.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525],"tags":[5134],"coauthors":[11330],"class_list":["post-93971","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93971"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93971\/revisions"}],"predecessor-version":[{"id":94149,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93971\/revisions\/94149"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93971"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}