Use SQL Like a Badass – Part 3/4

If you have extracted information from your company’s database for analytics, you would have found that no table has all the information you need. The result is you need to join together a set of tables to get all your required information. I will walk you through some basics of the JOIN commands in SQL. To prime you for JOINs, we will start with a joke.

5. JOINs

There are 4 types of joins.

  • INNER JOIN: Returns all rows when there is at least one match in BOTH tables
  • LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table
  • RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table
  • FULL JOIN: Return all rows when there is a match in ONE of the tables

The most common JOIN I use in business analytics is the LEFT JOIN, since I usually have a main table that I want to enrich with data from another. For instance I may have a shipment table (table A in the diagram of a left join) with product id, but I want to add in product name from the product_master table (table B in the diagram of a left join) into my data set. By using a LEFT JOIN, I do not lose any data from my main table and can join new columns from another table.

Before we launch into the code for the different types of joins, if you are using MS SQL Server Management Studio, you can construct all joins visually. Press CTRL + SHIFT + Q to launch the “Query Designer”. This is a very simple way to construct joins visually and you don’t have to remember the SQL code. For more see How Query Designer Represents Joins.

While I recommend using the visual option for creating joins, you need to understand a little more on what each JOIN does first.

5.1 INNER JOIN

The basic structure for the inner join is given below. Note that in some databases INNER JOIN is the same as JOIN.

In our Chuck Norris example, say we have an identifier for who wrote the joke ‘author_id.’

idquotecomment_qtycomment_rangeauthor_id
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.4L1
2Chuck Norris' facial hair is known to cut diamonds.3L1
3Chuck Norris once robbed a gun store with a knife.2L4
4Chuck Norris abducts aliens.15H5
5Chuck Norris once fought superman. The loser had to wear his underwear over his pants.7M1
6Chuck Norris can make a happy meal cry.13H3
7Chuck Norris counted to infinity. Twice.8M3
8In space Chuck Norris can hear you scream.0L4
9Chuck Norris is the reason Waldo is hiding.11H2
10M.C. Hammer learned the hard way that Chuck Norris can touch this.3L2
11The Incredible Hulk is Green because he envies Chuck Norris.8M99

We also have a separate table for the author id and the author name.

idauthor_name
1Jeff Smartypants
2Curly Howard
3Moe Howard
4Larry Howard
5Chuck Norris
6Bruce Lee
7Arnold Shoemaker
8Jim Graham
9Dwain Pipe
10David Hoff

Notice that the Chuck Norris table only has 6 authors (ids: 1,2,3,4,5,99), where as the Author table has 10. Also, notice that the Chuck Norris table has author_id “99”, which is not in the Author table. To do a inner join on our example:

idquoteauthor_name
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.Jeff Smartypants
2Chuck Norris' facial hair is known to cut diamonds.Jeff Smartypants
3Chuck Norris once robbed a gun store with a knife.Larry Howard
4Chuck Norris abducts aliens.Chuck Norris
5Chuck Norris once fought superman. The loser had to wear his underwear over his pants.Jeff Smartypants
6Chuck Norris can make a happy meal cry.Moe Howard
7Chuck Norris counted to infinity. Twice.Moe Howard
8In space Chuck Norris can hear you scream.Larry Howard
9Chuck Norris is the reason Waldo is hiding.Curly Howard
10M.C. Hammer learned the hard way that Chuck Norris can touch this.Curly Howard

You will note that I have used the format “Table.Column” format to reference fields. This is important in JOINs because, you may have columns with the same name in both tables. To ensure the code runs, it is better to explicitly refer to columns.

5.2 LEFT JOIN

The LEFT JOIN is one of the most useful of all JOINs as it allows you to retain all information on one table and enrich it with data in another table. This can be thought of as the SQL equivalent of a VLOOKUP. Also, note that some databases use LEFT OUTER JOIN.

Going back to our Chuck Norris example, we change the join type to LEFT JOIN.

idquoteauthor_name
1When Chuck Norris does a pushup, he isn't lifting himself up, he's pushing the Earth down.Jeff Smartypants
2Chuck Norris' facial hair is known to cut diamonds.Jeff Smartypants
3Chuck Norris once robbed a gun store with a knife.Larry Howard
4Chuck Norris abducts aliens.Chuck Norris
5Chuck Norris once fought superman. The loser had to wear his underwear over his pants.Jeff Smartypants
6Chuck Norris can make a happy meal cry.Moe Howard
7Chuck Norris counted to infinity. Twice.Moe Howard
8In space Chuck Norris can hear you scream.Larry Howard
9Chuck Norris is the reason Waldo is hiding.Curly Howard
10M.C. Hammer learned the hard way that Chuck Norris can touch this.Curly Howard
11The Incredible Hulk is Green because he envies Chuck Norris.Null

Notice that the last row returns “Null”. This is because there is no match on author_id “99” in the Authors table.

5.3 RIGHT JOIN

The RIGHT JOIN works almost the same as the LEFT join, but the main table you want to return data from changes. Also watch out that some databases may use RIGHT OUTER JOIN.

5.4 FULL JOIN

The FULL JOIN gives us data from both tables. I don’t often use this query in analytics, but the structure is as below:

That covers the fundamentals of JOINs. In summary, the most useful join to understand for business analytics is a LEFT join. In practice, if you use a database editor like SQL Managent studio, you can design all your joins visually and not worry about the coding. In the final SQL showdown, we will go through how to delete data and how to construct views in Use SQL Like a Badass – Part 4/4.

Share on LinkedInEmail this to someoneTweet about this on TwitterShare on FacebookPrint this page

Leave a Reply

Your email address will not be published. Required fields are marked *

*