SQL SERVER – SQL Basics: Joining Tables – Day 3 of 10

This is the 3rd post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Relational Data

When was the last time we received a vague answer to a question? For most of us, it happens every day. Let’s say we asked someone where they worked. We are anticipating a response that may include a city name or address, except the answer we actually get is, “I work at headquarters”. While this is an accurate answer, it is not the detailed answer we wanted to know.

After detecting a slight New England accent from James Newton, we decide to look him up in the Employee table and discover that he works at LocationID 2. In what city or state is this mysterious LocationID 2 located? A quick query of the Location table shows us the following data.

SELECT *
FROM Location


Now each time we see an employee listed for LocationID 2 we know the street, city and state information for where they work. Why not just store this information in the Employee table? In fact, why not put all of our data in one giant table so we only have one place to go to get our information? That is a common interview question about database design so let’s review a few database design best practices to start building our interviewing arsenal.

One reason is a lot of space in memory is saved by not replicating all three data items for each employee in a table. Another reason for having location fields only in the Location table is that it saves us time as well. For example: What would happen if the office at LocationID 2 physically moved from Boston to a new building in nearby Cambridge? If all the data were in a giant table, then we would have to update every street, city and state for each employee individually. This is a very tedious task that leaves a great deal of room for errors and inconsistency in how the data is entered. Placing the LocationID field only in the Location table means all employees with LocationID 2 map to an update we can make just once.

So, how do we find an employee’s address if the information is spread between two tables? Each table has a LocationID field inside it. We can then use a two-part identifier, to distinguish them as the Employee.LocationID field corresponds to the Location.LocationID field.

Look at Alex Adams and Barry Brown in the following figure. These employees both work at LocationID 1. If we were new to the company and only had access to the Employee table, we would not have enough detailed information to send a parcel to Alex Adams. What if we put two tables next to one another on our screen? By physically drawing a line from the Employee.LocationID field to the Location.LocationID field we can get more location details for each employee. LocationID 1 is located at 111 First ST in Seattle, WA.

What about a global company with locations in all 50 states and over 100 different countries? We will have many records in our Location table and probably will not be able to look at both tables very efficiently on one screen.

How can we effectively see information in two different tables at the same time? Our ultimate goal is to show the Employee and Location information in one result set. Since we have not learned the code on how to do this yet, it is not shown in this figure. The results shown are the goal of the upcoming example.

Inner Joins

So far, we have learned that each query can have only one result set and will only allow a single FROM clause. How can we place two tables in one FROM clause? We can include many tables in one FROM clause by using a JOIN clause. The most common type of a join is called the INNER JOIN.

An INNER JOIN clause allows us to join multiple tables in a single query, although it requires a specific condition in order for it to work correctly. We must ensure that the INNER JOIN statement has two tables with at least one common or overlapping field. We already know the Employee and Location tables share a common field (LocationID). The relationship is between Employee.LocationID and Location.LocationID, so we instruct SQL Server that the INNER JOIN is on this field and voila! We have combined two tables into one result set.

Every time a value is found in Employee.LocationID, the inner join will searche for the matching record in the Location.LocationID field. If a match is found, data from both tables are displayed as a single record. Both tables will show all their fields if we type SELECT * at the beginning of our query.

SELECT *
FROM Employee INNER JOIN Location
ON Employee.LocationID = Location.LocationID

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (https://blog.sqlauthority.com)