This tutorial will explain the advanced SQL Joins.

Join the Advanced Class!

In our previous lesson, we took our first small step into the world of joins. We looked at the syntax of the statements and how to join some tables together. Now we'll look at some of the more complex uses of the JOIN clause in T-SQL. SQL joins can help us answer questions about how data is related or even where data is missing.

This lesson will build on what you already learned in our previous entry on the basics of SQL joins. If you aren't clear on the basics of INNER and LEFT JOINs, you might want to review the previous lesson first.

NOTE: During this lesson, we'll introduce new tables without explaining how they get there. That is the subject of a future lesson. At this point, we're focusing on what tables do and not how to create them.

Complicated Relationships

No, we aren't talking about dating here. Some relationships cannot be put into a database using only two tables. Lets take a college class enrollment database program for an example. During this tutorial, you'll see how we use joins to answer questions about our students.

Take a look at this database. It's fairly simple. It has a table for students who go to the school, teachers who teach classes and the classes the school offers.

Advanced Joins

These tables make perfect sense. However, something is definitely missing here. You might think we need to add a ClassID foreign key to teachers and students to link them all together. However, can a teacher teach more than one class? Can a student be enrolled in more than one class? Can there be more than one session of a class at different times of day?

Obviously, we're missing something important in our current data structure. This missing piece is usually called an intersecting table. This is a table that represents a relationship between many objects. In this case, we need an intersecting table to define each instance of a class. Let's add the table.

The Intersecting Table


Advanced Joins

Now we've added a table called ClassInstance. As you can see, it defines which type of class is being held, who is going to teach it and the time and day its held. For the moment, we're still leaving students out of the equation. We'll get to that in a moment. First, lets look at how we can use joins to return information using our new intersecting table.

Let's say we've been asked to provide a report that contains every teacher's schedule and what classes they teach at each time. Although the question is "about" the classes and teachers, it also involves the ClassInstance table since that's what ties it all together.

Let's take a look at what's on the ClassInstance table:

SELECT * FROM CLASSINSTANCE
Advanced Joins

Here, we can obviously see that the ClassID and TeacherID are foreign keys linking over to the Class and Teacher tables. To get the teacher's names and also the names of the classes, we're going to need to join both of these tables to the ClassInstance table. Here's the SQL:

SELECT FirstName, LastName, Class, ClassDay, ClassTime
FROM TEACHERS
INNER JOIN CLASSINSTANCE ON TEACHERS.TEACHERID = CLASSINSTANCE.TEACHERID
INNER JOIN CLASSES ON CLASSES.CLASSID = CLASSINSTANCE.CLASSID

As you can see, doing another join is easy. You just tack it on at the end. You do, however, have to make sure you join the tables in a logical order. For example, you cannot start by joining teachers to classes since their common linking element is the ClassInstance table.

Fully Qualified Name

You probably noticed that we added something else new in the above query. The fields we called out in the JOIN statements are preceded by the table name and a period. This is to differentiate between the ClassID primary key on the Class table and the ClassID foreign key on the ClassInstance table. You can put an in anywhere you need to put a field name. This includes the WHERE clause, ON clause and SELECT statement. We'll see further along why these are so important.

Adding Students

The students still present a unique problem. Can we just add a ClassID foreign key to the Student table? No. This would mean students could only be in one class. We need yet another intersecting table to connect students to classes.

Advanced Joins

The new StudentClasses table is fairly simple. All it really does is join primary keys together. However, since this data is in its own table, you can now tie students to multiple classes.

Advanced Joins

As you can see, student one is enrolled in multiple classes. By joining all the three tables together, you can see clearly how the intersecting table allows you to create a one-to-many relationship.

SELECT FirstName, LastName, ClassName
FROM STUDENTS
INNER JOIN CLASSINSTANCE ON CLASSINSTANCE.STUDENTID = STUDENTS.STUDENTID
INNER JOIN CLASSES ON CLASSINSTANCE.CLASSID = CLASSES.CLASSID
Advanced Joins

Finding Out What's Not There

To answer some questions, we need to know what isn't in our database. For example, we might want to know which teachers aren't teaching any classes. Questions like these require us to have a way to check if no data exists. Fortunately, using a LEFT JOIN and a WHERE clause together allows us to get these kinds of answers.

Take a look at the following example:

SELECT FirstName, LastName, ClassID 
FROM TEACHERS
LEFT JOIN CLASSINSTANCE ON TEACHERS.TEACHERID = CLASSINSTANCE.TEACHERID
Advanced Joins

It's clear from this view that one teacher has no classes. His ClassID is blank. If we wanted to bring back only teachers who have no classes, we just add a WHERE clause to the existing query.

SELECT FirstName, LastName, ClassID
FROM TEACHERS
LEFT JOIN CLASSINSTANCE ON TEACHERS.TEACHERID = CLASSINSTANCE.TEACHERID
WHERE CLASSID IS NULL

Adding this takes away all the classes that matched leaving us with a list of the teachers who aren't currently teaching any classes. Now we're well equipped to answer most data-centric questions. However, we still have a lot to learn. What if someone asks us how many classes each student takes? How do we tally that up programmatically? We'll learn how to answer this question using T-SQL aggregates in our next lesson.



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories