Benjamin's blog

SQL & Inventory tips, tricks, and random thoughts (generally geared toward the Configuration Manager Admin)

SQL Tip: JOINs

SQL Tip: JOINs

  • Comments 1
  • Likes
Joins are an essential part to writing queries. When creating a result set from 2 or more tables the tables are joined. This tip will help describe the types of joins and why you’d use one over another. The JOIN types we will talk about are:
  • INNER JOIN (aka JOIN)
  • LEFT OUTER JOIN (aka LEFT JOIN)
  • RIGHT OUTER JOIN (aka RIGHT JOIN)
  • FULL OUTER JOIN (aka FULL JOIN)
  • CROSS JOIN
 
Parts of a JOIN:
A JOIN is made up of the Keyword and the Predicate. The Keyword can be thought of as the type of join, such as INNER, LEFT, etc. The Predicate tells SQL what column from each table to use in the comparison in order to determine which row(s) should be joined/matched. Note: it is possible and sometimes even necessary to have more than one predicate defined (i.e., ON t1.column = t2.column AND t1.column2 = t2.column2).
  FROM dbo.TableOne tb1
       INNER JOIN dbo.TableTwo tb2 -- The JOIN KEYWORD
          ON tb1.ColID = tb2.ColumnID -- The JOIN PREDICATE(S)
 
For simplicity I’ll just be using two tables for our examples.
 
The example tables:
We will use the “ID” columns for all our JOINs in the examples.
 
TableOne
TableTwo
ColID
ColValue
ColumnID
ColumnValu1
1
Shitanshu
2
Naveen
2
Benjamin
6
Andy the Change Master
5
Arun
7
Dharmendra
6
Chuck Norris
8
Satish
7
Karthik
 
INNER JOIN
When this type of join is used the values from both tables are returned IF the value(s) defined in the predicate (the ON statement) are the same in both tables. The value defined in the join is evaluated for each value in each table and where the predicates (the IDs in our example) are equal then the row will be returned. Therefore, the data from both tables will create one record for the values 2, 6, and 7. Since there isn’t an ID of 1 or 5 in the right table (TableTwo) these values are not returned; likewise, because there isn’t an ID of 8 in the left table (TableOne) the value is not returned.
SELECT  *
  FROM dbo.TableOne tb1
       INNER JOIN dbo.TableTwo tb2
          ON tb1.ColID = tb2.ColumnID;
GO
ColID
ColValue
ColumnID
ColumnValu1
2
Benjamin
2
Naveen
6
Chuck Norris
6
Andy the Change Master
7
Karthik
7
Dharmendra
 
LEFT OUTER JOIN
This join will return all the records from the ‘left’ table (in our example TableOne) regardless of whether there is a matching ID in the right table. It will, however, return the results from the right table IF there is a matching ID in the table. Therefore, we will return all the IDs from TableOne and the data for IDs 2, 6, and 7. A “NULL” value will be returned for the records that do not have a match (1 and 5).
SELECT  *
  FROM dbo.TableOne tb1
        LEFT OUTER JOIN dbo.TableTwo tb2
          ON tb1.ColID = tb2.ColumnID;
GO
ColID
ColValue
ColumnID
ColumnValu1
1
Shitanshu
2
Benjamin
2
Naveen
5
Arun
6
Chuck Norris
6
Andy the Change Master
7
Karthik
7
Dharmendra
 
RIGHT OUTER JOIN
A right outer join behaves just like the left outer join except that all data from the right table will be returned regardless of whether there’s a match on the left side. (You could get the same results by using a left outer join by switching the tables around). We’ll finally see ID 8 (Satish) show up (because all values from the right table, aka TableTwo, will be returned) but we won’t see ID 1 and 5.
SELECT  *
  FROM dbo.TableOne tb1
       RIGHT OUTER JOIN dbo.TableTwo tb2
          ON tb1.ColID = tb2.ColumnID;
GO
ColID
ColValue
ColumnID
ColumnValu1
2
Benjamin
2
Naveen
6
Chuck Norris
6
Andy the Change Master
7
Karthik
7
Dharmendra
 
 
8
Satish
 
Let’s re-write the RIGHT OUTER JOIN to a LEFT OUTER JOIN to show that the behavior is the same based on which table is the left and which is the right! (this is based on the actual order of tables listed NOT what is on the left or right side of the ON Predicate).
SELECT  * -- to get results listed in same order as the right join: tb1.*,tb2.*
  FROM dbo.TableTwo tb2
        LEFT OUTER JOIN dbo.TableOne tb1
          ON tb2.ColumnID = tb1.ColID;
GO
ColumnID
ColumnValu1
ColID
ColValue
2
Naveen
2
Benjamin
6
Andy the Change Master
6
Chuck Norris
7
Dharmendra
7
Karthik
8
Satish
 
 
 
FULL OUTER JOIN
Never heard of a FULL OUTER JOIN? Good, then this will be worthwhile. :) A full outer join essentially performs a left and a right outer join at the same time. Therefore, all records from both tables are returned; where no matching ID was found NULLs are shown for the record
SELECT  *
  FROM dbo.TableOne tb1
        FULL OUTER JOIN dbo.TableTwo tb2
          ON tb1.ColID = tb2.ColumnID;
GO
ColID
ColValue
ColumnID
ColumnValu1
1
Shitanshu
 
 
2
Benjamin
2
Naveen
5
Arun
 
 
6
Chuck Norris
6
Andy the Change Master
7
Karthik
7
Dharmendra
 
 
8
Satish
 
CROSS JOIN
Hopefully you’ve never heard of this join. I don’t even like to call this a join and I hesitate to tell you about it because there are VERY few instances where you’d actually use this. As a best practice, DO NOT USE THIS. In fact, if you find yourself ever using this before you actually execute the query ask another query writing colleage if there’s a different (aka better) way to do what you’re trying to do. This “join” creates a Cartesian product; meaning for every record in the left table every record in the right table will be returned. In our small example of 5 records in TableOne and 4 records in TableTwo the rowcount returned will be 20 (5 records from TableOne * 4 records from TableTwo). You can see why this can be a bad thing; suppose you wrote a Cartesian/Cross Join query with the two views “v_R_System” and “v_ClientAdvertisementStatus”, (at the time of writing this in my CM hierarchy) the return rowcount would be 1,548,626,110,885! The other thing to know about the CROSS JOIN is that it has no “ON” predicate. Because I don’t want to paste a 20 record table into this blog post, I’m going to add a WHERE condition to my CROSS JOIN. Because the WHERE only asks to return one ID from the left table we will get 4 records – 1 * 4 records in right table.
SELECT  *
  FROM dbo.TableOne tb1
        CROSS JOIN dbo.TableTwo tb2
 WHERE tb1.ColID = 1;
GO
ColID
ColValue
ColumnID
ColumnValu1
1
Shitanshu
2
Naveen
1
Shitanshu
6
Andy the Change Master
1
Shitanshu
7
Dharmendra
1
Shitanshu
8
Satish
 

Hopefully you've learned something other than not to use CROSS JOINs in this post. :) 

Comments
  • Nice one ...Thanks Benjamin!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment