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.
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.
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.
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 firstName, residence, start_city, end_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.
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.
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
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?