Learn SQL: Microsoft SQL Server - Episode 5: Joining Tables
Tags: sql, database, beginners, datascience
Published: 2021-10-29 03:46:37

![goran-and-cat-greets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rqs1t876uy30hbty6nbt.png)

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

![skid-14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zuhccgyk7ddmkgjcsib9.gif)

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 <u>unique record</u> 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.

![sql-select-query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ju6060muhkka8iya3j9g.gif)
  
There is also a table called **Person.EmailAddress**, which contains information on each persons _email address_. 

![skid-15](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0pocki1vzwvoyfb1g4wj.gif)

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.

![sql-select-email-address](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pq9yo667pdjfam6e29pq.gif)

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

![skid-14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zuhccgyk7ddmkgjcsib9.gif)

The syntax for joining tables is as follows:
```
Select [Column Names | * ]
From [Table Name]
On [Table.primaryKey] = [Table.primaryKey]
```

![Joining-tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gafhtkwz7iiq1chdjbed.gif)

We can limit the amount of columns return to us by specifying the columns we want to pull in the select statement.

![limit-query-and-error-checking](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dku8l2im8y4rrgxbkin3.gif)

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.

![skid-15](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0pocki1vzwvoyfb1g4wj.gif)

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.

![join-multiple-tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nxop7go1vcneu08kc5yn.gif)

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:

![adding-a-where-clause](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mlzfd5zxuseps5cjmfts.gif)

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!

![skid-1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/avgigriowxz6i6241uvl.png)

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

![calming-mind](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bkpp1xl6761axpfzu7y0.gif)