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
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.
