MODULE 3: DATING AND RELATIONSHIPS

Joins in Queries

Why do we need Joins?

When all the information you are interested in is contained in a single table you are querying then a join is not necessary.  Take a look at the following example.

SELECT 
 [id]
 ,[person_id]
 ,[start_city]
 ,[dest_city]
 ,[flightDate]
FROM 
 [flight]

1.) What are the values for the "Person_id" column when you run this query?

2.) How can we tell the names of people who have taken flights?

The flight table does not specify the actual names of people who have taken flights, it just references the person_id.  In a situation like this Joins are very useful.  A join would allow us to join the flight table to the person table so that we can provide an answer for question 2 above.

Understanding the Database Structure

In order to work with joins properly we need to understand the structure of your database.  In the case of the person table and the flight table, we would need to understand how these two tables relate to each other.  One way of doing this is to take a look at the Database Diagram. 

Database Diagram showing a relationship between the  person  and  flight  tables

Database Diagram showing a relationship between the person and flight tables

By hovering over the relationship line that connects the person to the flight table we can see the name of the relationship.  Next, to get more information on the relationship we navigate to the Tables folder in the Object Explorer and expand the flight table.  Expand the Keys folder which displays each relationship that the flight table has.  In our example, it displays the Primary Key as the id field.  It also shows the Foreign Keywhich is the person_id field.

By right clicking on the Foreign Key and selecting Modify this opens the Foreign Key Relationships dialogue, here we can discover even more information about the primary and foreign keys.  

By identifying the column id in the person table is related to the column person_id in the flight table, we now have all the information we need to begin writing our joins.

Inner Joins

The query we will compose can now show all the flights that are in the flight table and the names of the people who took these flights.  

SELECT
*
FROM
person AS p
INNER JOIN flight AS f
ON p.id = f.person_id

The above example uses one type of join called an INNER JOIN.  After the FROM clause it first lists the person table and gives it the alias of "p".  The INNER JOIN (the keyword INNER is optional in an INNER Join) is then specified on the next line followed by the flight table which has the alias "f".  Next the keyword ON is used to specify the related fields of the person and flight tables.  We have already identified these two fields in the previous section as the "id" field from the person table and the "person_id" field from the flight table. The following syntax is used to associate the two in a Join p.id = f.person_id.

3.) Run the sample query above.  What is the result?

4.) What is the name of the person who flew to Addis Ababa? What is this person's age?

Adding more tables using Inner Joins

Bringing in additional tables using Inner Joins is now pretty straightforward as it will follow the exact pattern as our first inner join.  Taking a look at our Database Diagram we can see that in addition to the relationship between the person and flight tables, there is also a relationship between the person and phone_contract tables on the person_id field in the phone_contract table and the id field in the phone table.  With this information we can then proceed to add the phone_contract table to our previous query.

Database Diagram showing tables that have relationships with the  person   table.  Both the  phone_contract  table and the  flight  table have relationships with the  person  table.

Database Diagram showing tables that have relationships with the person  table.  Both the phone_contract table and the flight table have relationships with the person table.

SELECT
*
FROM
person AS p
INNER JOIN flight AS f
ON p.id = f.person_id
INNER JOIN phone_contract AS pc
ON p.id = pc.person_id

By executing the sample query we see all of the related data from both the person, flight tables and the phone_contract table.

5.) What is the telephone number of the person who flew to Addis Ababa?

We can reduce the number of columns that is displayed in the result of our query by specifying only the columns we want to see.  To reference a column we must use the alias of the table specified in our FROM clause followed by a period and the column name you want to see. For example "p.age" references the person table using it's alius "p" followed by a period "." then the column to be displayed "age".

6.) Create a query that joins the person, flight and phone_contract tables.  Display only the firstNameresidencestart_cityend_city and phone_number fields.

Using Outer Joins

Inner joins are used when you want to see corresponding information from two different tables.  In the example below the query shows us all people and their corresponding phone numbers.

SELECT
 p.firstName,
 pc.phone_number
FROM
person AS p
INNER JOIN phone_contract AS pc
ON p.id = pc.person_id

If we were searching for a bit of different information, for instance, all people who do NOT have a phone number.  We would search for any person id that has not been used in the person_id field of the phone_contract table.  In order to get these results we need a LEFT OUTER JOIN.

An  INNER JOIN  returns only the data that exists in both tables.  The area labeled as Inner Join in the diagram above

An INNER JOIN returns only the data that exists in both tables.  The area labeled as Inner Join in the diagram above

A  LEFT OUTER JOIN  returns all of the data from the  person  table with matching records (where available) in the phone_contract table.

A LEFT OUTER JOIN returns all of the data from the person table with matching records (where available) in the phone_contract table.

Changing an INNER JOIN to a LEFT OUTER join is as simple as replacing the word INNER to LEFT OUTER.

SELECT
 p.firstName,
 pc.phone_number
FROM
 person AS p
LEFT OUTER JOIN phone_contract AS pc
ON p.id = pc.person_id

7.) Run the query with an INNER JOIN then with a LEFT OUTER join.  Observe the results.  How many rows are returned for each join?

The result set from the LEFT OUTER join now includes all the rows from the person table, including those that do not have a corresponding phone number in the phone_contract table.  See the results below.

Records that do not have a corresponding phone number are displayed as  NULL

Records that do not have a corresponding phone number are displayed as NULL

To fully answer our initial inquiry and get all people who do NOT have a phone number, we would need to add a WHERE clause which specifies only the records that are NULL.

SELECT
p.firstName,
pc.phone_number
FROM
person AS p
LEFT OUTER JOIN phone_contract AS pc
ON p.id = pc.person_id
WHERE
 phone_number IS NULL 

The result displays all people who do not have a corresponding value in the person_id field of the phone_contract table.  The "LEFT" keyword gets its significance because to the LEFT of this keyword is the table person in our example above.  We are interested in all of the information from the table that is specified to the LEFT of this keyword.  Just like the INNER Join the "OUTER" keyword in optional, simply writing LEFT JOIN, produces the same result.

8.) Include the WHERE clause and run the query again.  How have the results changed?

Other Types of Outer Joins

Joins do not necessarily have to involve two tables that have a direct relationship in a database.  For instance we may be interested in the relationship that exists between account and flight

There is no direct relationship between  account  and  flight .

There is no direct relationship between account and flight.

A  RIGHT OUTER JOIN  returns all of the data from the  flight  table with matching records (where available) in the  account  table.

A RIGHT OUTER JOIN returns all of the data from the flight table with matching records (where available) in the account table.

A RIGHT OUTER join would give us all the records from the flight table and only the corresponding records that match in the account table. 

SELECT
account_number_pk AS [Bank Account],
f.person_id,
f.start_city,
f.dest_city,
f.flightDate
FROM
 account AS a
 RIGHT JOIN flight AS f
 ON a.person_id = f.person_id

9.) Run the sample query above and observe the results.

10.) Change the "RIGHT" keyword to LEFT, then change it again to INNER.  What are the results for each?

To answer our inquiry about who are the people that took a flight but do NOT have a bank account, we would need to add a WHERE clause which specifies only the records that are NULL.  


SELECT
account_number_pk AS [Bank Account],
f.person_id,
f.start_city,
f.dest_city,
f.flightDate
FROM
account AS a
RIGHT JOIN flight AS f
ON a.person_id = f.person_id
WHERE
 account_number_pk IS NULL

11.) How would we get these people's names?  Hint: All names are in the person table.

The "RIGHT" keyword gets its significance because to the RIGHT of this keyword is the table flight in our example above.  We are interested all of the information from the table that is specified to the RIGHT of this keyword.  Just like the LEFT Join the "OUTER" keyword in optional, simply writing RIGHT JOIN, produces the same result.

If we wanted to see ALL of the data from both the flight AND the account tables we would use a new join called the FULL OUTER JOIN.

SELECT
account_number_pk AS [Bank Account],
f.person_id,
f.start_city,
f.dest_city,
f.flightDate
FROM
 account AS a
 FULL OUTER JOIN flight AS f
 ON a.person_id = f.person_id

The FULL OUTER JOIN returns all of the data in the account table and all of the data in the flight table.  As with the other join types the OUTER keyword is optional.  

12.) How many records are returned when the FULL OUTER JOIN is used?