Using Exotic Joins in SQL – Part 2
Chris Cubley, MCSD
In the previous article, you saw how the BETWEEN operator
could be used in joins to solve problems dealing with range-based data. In this article, I will show you how to take
joins even further by using multiple criteria in joins as well as using the
greater than, less than, and not equals operators in joins.
Compound joins are joins which use multiple criteria
combined with a logical operator such as AND.
This is a relatively simple concept and is commonly
used in database systems that employ compound primary keys.
For a simple example of a database schema in which compound
joins are necessary, consider a school management system where one of the
features is tracking which classes are taught in which
classrooms. The system must match up the
features of the classrooms to the needs of the classes. In order to perform these functions, the
following two tables are defined:
CREATE TABLE tb_Classroom(
BuildingName char(10) NOT
RoomNumber int NOT
RoomCapacity int NOT
HasLabEquip smallint NOT NULL,
PK_Classroom PRIMARY KEY(BuildingName, RoomNumber)
CREATE TABLE tb_ClassSection(
CourseID char(5) NOT NULL,
SectionNumber smallint NOT
BuildingName char(10) NOT
RoomNumber int NOT
InstructorID int NOT
ScheduleID int NOT
SectionCapacity int NOT
RequiresLabEquip smallint NOT
PK_ClassSection PRIMARY KEY(CourseID, SectionNumber),
In this example, the tb_Classroom table defines a list of
classrooms in which classes are taught. The tb_ClassSection table contains instances
of various courses taught at the school.
A class section is taught in a particular classroom by
an instructor according to a standard class schedule. Both the tb_Classroom and tb_ClassSection
tables use natural compound primary keys.
One of the reports in the school management system lists the
class sections being taught along with the capacity of
their respective classrooms. In order to
construct this report, the tb_ClassSection table must be joined the tb_Classroom table based upon the compound primary key of
the tb_Classroom table. This can be accomplished by using a compound join to return rows
where both the BuildingName AND RoomNumber columns match.
This query is relatively straightforward. If you’ve been using
SQL for a while, chances are you’ve seen queries like it. The query is a simple equijoin
that uses the AND logical operator to include multiple criteria. Despite its simplicity, this example provides
the basis for a much more powerful query construction tool.
Joins Using Inequality Comparison Operators
The school management system from the first example also
contains a report listing all class sections in which the classroom is not
large enough to accommodate the maximum number of students for the class
section. To determine which class
sections meet these criteria, the system must compare the class section’s
capacity to the capacity of the classroom in which it is
being taught. If the classroom’s
capacity is less than the class section’s capacity, then the class section
should be included in the result set.
With this query, the trick is to first join each class
section to the classroom in which is being taught and then add the additional
criterion that the classroom’s capacity is less than that of the class
section. To do this, simply take the
query from the last example and add the additional criterion.
A common mistake when constructing queries such as this is
not including the equijoin criteria necessary to
match up the rows to be compared by the inequality
operator. If only the inequality
comparison is included in the criteria, the query returns all the rows where a
classroom’s capacity is less than that of any class section, regardless of
whether or not the class section was taught in that classroom.
Not Equals Joins
You may be wondering how in the world it could be useful to
use the not equals operator in a join.
For an example, consider another report in the school management system
in which you must the misallocation of laboratory-equipped classrooms. This report must list all of the class
sections that require laboratory equipment, but are scheduled
to be taught in classrooms that do not have it. The report should also include all non-lab
class sections being taught in laboratory
classrooms. In the tables, the class
sections that require laboratory equipment are indicated with a
RequiresLabEquip value of 1, and the classrooms equipped with laboratory
equipment are indicated with a HasLabEquip value of 1.
This problem follows a similar pattern to that of the
capacity problem. The only difference is
the use of the not equals operator in place of the less than operator. After matching the class section with the
classroom in which it is being taught, the value of the RequiresLabEquip column
must be compared with the HasLabEquip column.
If these values are not equal, there is a laboratory equipment
allocation problem and the class section should be included on the report. Applying these criteria result in the
When using the not equals operator in joins, it is even more
vital to remember to use additional join criteria than it is when using the greater
than and less than operators. In this case, if only the not equals
criterion was specified, the query would perform a cross join
and then exclude only the class section-classroom pairs where the laboratory
indicator was not equal. If there were
100 classrooms and 500 class sections, this could possibly return a result set
of 25,000 - 50,000 rows – definitely not what was intended.
Beyond the Basics
Compound joins, especially those employing the inequality
and not equals operators, can be used with other advanced SQL techniques to
construct queries that are even more powerful.
The key to leveraging these advanced joins is to spell out the
requirements of the problem clearly in terms of relating the available sets of
data. Once the relationships between the
sets of data are understood, these relationships can
be encoded into join criteria. This
technique, along with testing and validation of the output, will enable you to
solve complicated problems effectively.