Joining Multiple Tables
Thus far we have discussed how to pull data from a single table. However in most cases, we will need to pull the data from multiple tables in order to get the bigger picture for our analysis or reporting.
Before we can join tables in a SQL query, we need to be familiar with our database structure, which tables there are, what kind of columns are there, and how the tables connect to each other.
In the AdventureWorks, the Person schema contains a-lot of information about a person, the people who are in the database, the person table contains columns that hold different pieces of information of a person.
It holds the Primary Key (PK), which is BusinessEntityID. This means that BusinessEntityID is a unique record that identifies each record.
BusinessEntityID is also the Foreign Key (FK) for this table. This means it is used to connect to other related tables.
There is also a table called Person.EmailAddress, which contains information on each persons email address.
The table has a the Primary Key of BusinessEntityID and each email address has a Primary Key of EmailAddressID, the EmailAddressID allows for a person to have multiple email addresses.
In order for us to pull the firstName, lastName and emailAddress for a person, we need to join these two tables. A way to join them is to take a key that is common between both of them, which in this case is BusinessEntityID.
The syntax for joining tables is as follows:
Select [Column Names | * ]
From [Table Name]
On [Table.primaryKey] = [Table.primaryKey]
We can limit the amount of columns return to us by specifying the columns we want to pull in the select statement.
Notice how when we try to pull the data from BusinessEntityID, SQL throws an error, it will also indicate an error by underlining with a red line. The reason why is we an not being specific from which table we want to pull the BusinessEntityID, since it exist in both tables.
When we specify we want to pull from the person.BusinessEntityID is works perfectly.
Using this same syntax we can join multiple tables. Lets say for instance we need to join another table and it is the Person.PersonPhone table.
We can even limit this further by specifying a Where clause, lets say for instance we need to limit this query to where the firstName is Kim, we can accomplish this as follows:
We are now able to find each person with the first name as Kim, we can see their last name, email address and phone number by joining multiple tables, that is pretty amazing!
We have come to the end of this discussion about joining tables, I really hope you enjoyed reading it, join me in the next episode...
Top comments (0)