Learn about SQL joins using Excel

Why do we join tables in SQL?

Tables that logically represent information are the foundation of relational databases. The ancient rules of database normalization remind us to arrange database tables to reduce redundancy and increase flexibility.

An adequately normalized database saves disk space and alleviates the need to maintain the same value in multiple places.

By design, an individual table will not have the answers to your inquiries. Luckily we defined the relationships between our tables, and using them together can get us the answers we need.

Please join us In this article as we use our knowledge of Excel to explore valid table joins.

SQL Join Us!

 

How many types of Joins are there?

First, Let’s go over the types of Joins that connect one table to another in SQL.

The Type of Join Determines how one table connects to another and, therefore, which results are displayed

Here’s a quick overview of the types of Joins

Inner Join – Most restrictive; it only pulls rows where the join key matches in both tables

Join Only where Primary Keys Match

SELECT Currency_Code, Country_Name, Currency_Name, Full Name, Email

FROM Currency, Contact

INNER JOIN Contact on Currency.Currency_Code = Contact.Currency_Code

 

Left  Join – Left Join Pulls everything from the first table (Left) and matches rows from the table on the right.

SELECT Currency_Code, Country_Name, Currency_Name, Full Name, Email

FROM Currency, Contact

LEFT JOIN Contact on Currency.Currency_Code = Contact.Currency_Code

 

Right Join – Right Join Pulls everything from the second (Right) and only rows from the table on the left if there is a match.

Returns everything from second table, only matches from First table

SELECT Currency_Code, Country_Name, Currency_Name, Full Name, Email

FROM Currency, Contact

RIGHT JOIN Contact on Currency.Currency_Code = Contact.Currency_Code

Full Outer Join – Pull the Join Key from both tables, show everything that exists to each

FULL Outer Join SQL

Return Everything from Both Tables

SELECT Currency_Code, Country_Name, Currency_Name, Full Name, Email

FROM Currency, Contact

OUTER JOIN Contact on Currency.Currency_Code = Contact.Currency_Code

 

That wasn’t easy to follow. Let’s lock that knowledge in with Excel Examples.

Join Puzzle Shows Registration Online And Applications

 

A demonstration of SQL Joins operating in Excel

The spreadsheet below has four tabs. Each one demonstrates how a different SQL join works.   For our examples, the tables are connected by the field currency code.

Go through each tab of the report below to see them in action.  I actually used different Excel formulas in the worksheet to connect the data the same way SQL would handle them. Check out the formulas if you are curious.

Let’s see how our demo data behaves in each example

Inner Join – Inner Most restrictive; it only pulls rows there the join key matches.

Only eight rows matched on currency code. Therefore an inner join will only display data for these eight rows.  The rest of the data from the source tables are omitted when forming our joined table.

Left  Join – Left Join Pulls everything from the first table (Left) and matches rows from the table on the right.

There are thirteen rows in the first table. The left join commands us to bring in all thirteen. Then we only bring in data from the second table where we have a match. So we have eight complete rows and five with left table information and Null values on the left side

Right Join – Right Join Pulls everything from the second (Right) and only rows from the table on the left if there is a match.

The second table has fourteen rows, so we start by pulling the primary key for all fourteen rows.   Our second table is the primary table since this is a RIGHT JOIN so we will pull in all the data from the second table. However, we only pull the first table data for our eight matches.

Full Outer Join – Pull the Join Key from both tables, show everything that exists to each

The FULL outer join will pull the primary keys from both tables, whether or not we have a match. This results in 19 total rows.  Thirteen of these rows match completely and will have a full record.
However, we will also pull in the non-matching rows from both LEFT and right. THis results in a mixture of records.

 

In this brief tutorial, you strengthened your knowledge of database join clauses using your foundation in excel. Practice writing SQL joins in code to lock in this wisdom.

keepersecurity.com

Latest Blog Posts

Super Bowl Squares Template

elink.io | See Original The Superbowl is almost here. If you are tasked with creating blocks for a pool, there's still time to pull out your ruler, draw up a grid, and wrangle a bunch of friends into writing their names in the squares. Or, spare yourself some time...

How to Return Multiple Matches in one Cell Using a lookup?

VLOOKUP is great for looking up a value and returning another corresponding value. But what happens when the first match just isn't enough. I've been tasked with finding a way to put return not just the first but the second, and third match. At first, I tried to...

How to Delete every other Row in Excel

How to Delete every other Row in Excel

Have you ever needed to delete every other row in Excel? You are not alone.   There are a few ways to accomplish it. It's a pretty good candidate for a VBA solution, but I'm going to show you how to quickly delete every other row without using VBA or a plugin. I...

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Data Defender: Play this Excel Game and Learn Excel Shortcuts Fast

Microsoft Excel has roughly 750 million users all over the world. It is an essential, all-purpose business tool. This spreadsheet software can fill a wide range of business needs. Every business professional needs to get familiar with Excel. Your proficiency level...

Select the Email Service Provider that is right for you.

Select the Email Service Provider that is right for you.

  Excelshortcut.com commits to continuous improvement. I've wondered if our Communication distribution process is lacking. Is there a more effective tool to help us share valuable content? Mailchimp has been ExcelShortcut.com's tool for email creation and...

Insert Multiple Rows into SQL table Using Excel as a Template

Insert Multiple Rows into SQL table Using Excel as a Template

I hope you followed the previous tutorial where we checked Created some brand new database tables using Excel as a template. Let's populate these tables with meaningful data. Okay, it might not be the most meaningful dataset since I just made this data up using...

Share This