Join expressions



Rating - 3/5
471 views

We introduced the natural join operation. SQL provides other
forms of the join operation, including the ability to specify an explicit join pred?icate, and the ability to include in the result tuples that are excluded by natural
join. We shall discuss these forms of join in this section.

The examples in this section involve the two relations student and takes, shown
in Figures 4.1 and 4.2, respectively. Observe that the attribute grade has a value
null for the student with ID 98988, for the course BIO-301, section 1, taken in
Summer 2010. The null value indicates that the grade has not been awarded yet.

 

These Topics Are Also In Your Syllabus
1 Data Mining Concepts link
2 Data Mining Concepts link
You May Find Something Very Interesting Here. link
3 Nested Subqueries link
4 Nested Subqueries link
5 Types Of Systems link

1. Join Conditions

In Section 3.3.3, we saw how to express natural joins, and we saw the join ...
using clause, which is a form of natural join that only requires values to match
on specified attributes. SQL supports another form of join, in which an arbitrary
join condition can be specified.

The on condition allows a general predicate over the relations being joined.
This predicate is written like a where clause predicate except for the use of the
keyword on rather than where. Like the using condition, the on condition appears
at the end of the join expression.

Consider the following query, which has a join expression containing the on
condition.

select *
from student join takes on student.ID= takes.ID;

These Topics Are Also In Your Syllabus
1 Using High-Level Conceptual Data Models for Database Design link
2 Using High-Level Conceptual Data Models for Database Design link
You May Find Something Very Interesting Here. link
3 Using High-Level Conceptual Data Models for Database Design link
4 Using High-Level Conceptual Data Models for Database Design link
5 Types Of Systems link

The on condition above specifies that a tuple from student matches a tuple from
takes if theirID values are equal. The join expression in this case is almost the same
as the join expression student natural join takes, since the natural join operation also requires that for a student tuple and a takes tuple to match. The one difference is that the result has the ID attribute listed twice, in the join result, once for student and once for takes, even though their ID values must be the same.

In fact, the above query is equivalent to the following query (in other words,
they generate exactly the same results):

select *
from student, takes
where student.ID= takes.ID;

As we have seen earlier, the relation name is used to disambiguate the attribute
name ID, and thus the two occurrences can be referred to as student.ID and takes.ID respectively. A version of this query that displays the ID value only once is as follows:

select student.ID as ID, name, dept_name, tot_cred,
course_id, sec_id, semester, year, grade
from student join takes on student.ID= takes.ID;

These Topics Are Also In Your Syllabus
1 XML: Extensible Markup Language link
2 Schema Diagrams link
You May Find Something Very Interesting Here. link
3 Relational Query Languages link
4 Relational Operations link
5 Types Of Systems link

The result of the above query is shown in Figure 4.3.

The on condition can express any SQL predicate, and thus a join expressions
using the on condition can express a richer class of join conditions than natural
join. However, as illustrated by our preceding example, a query using a join
expression with an on condition can be replaced by an equivalent expression
without the on condition, with the predicate in the on clause moved to the where
clause. Thus, it may appear that the on condition is a redundant feature of SQL.

However, there are two good reasons for introducing the on condition. First,
we shall see shortly that for a kind of join called an outer join, on conditions do
behave in a manner different from where conditions. Second, an SQL query is
often more readable by humans if the join condition is specified in the on clause
and the rest of the conditions appear in the where clause.

2. Outer Joins

Suppose we wish to display a list of all students, displaying their ID, and name,
dept name, and tot cred, along with the courses that they have taken. The following
SQL query may appear to retrieve the required information:

These Topics Are Also In Your Syllabus
1 Transaction Management link
2 Database Architecture link
You May Find Something Very Interesting Here. link
3 Data Mining and Information Retrieval link
4 Specialty Databases link
5 Database Users and Administrators link

select *
from student natural join takes;

Unfortunately, the above query does not work quite as intended. Suppose that
there is some student who takes no courses. Then the tuple in the student relation
for that particular student would not satisfy the condition of a natural join with
any tuple in the takes relation, and that student’s data would not appear in the


result. We would thus not see any information about students who have not taken any courses. For example, in the student and takes relations of Figures 4.1 and 4.2,
note that student Snow, with ID 70557, has not taken any courses. Snow appears
in student, but Snow’s ID number does not appear in the ID column of takes. Thus,
Snow does not appear in the result of the natural join.

More generally, some tuples in either or both of the relations being joined may
be “lost” in this way. The outer join operation works in a manner similar to the
join operations we have already studied, but preserve those tuples that would be
lost in a join, by creating tuples in the result containing null values.

These Topics Are Also In Your Syllabus
1 Example of Other Notation: Representing Specialization and Generalization in UML Class Diagrams link
2 Data Abstraction, Knowledge Representation, and Ontology Concepts link
You May Find Something Very Interesting Here. link
3 Using High-Level Conceptual Data Models for Database Design link
4 Using High-Level Conceptual Data Models for Database Design link
5 Types Of Systems link

For example, to ensure that the student named Snow from our earlier example
appears in the result, a tuple could be added to the join result with all attributes
from the student relation set to the corresponding values for the student Snow,
and all the remaining attributes which come from the takes relation, namely course
id, sec id, semester, and year, set to null. Thus the tuple for the student Snow is
preserved in the result of the outer join.

There are in fact three forms of outer join:

• The left outer join preserves tuples only in the relation named before (to the
left of) the left outer join operation.

• The right outer join preserves tuples only in the relation named after (to the
right of) the right outer join operation.

• The full outer join preserves tuples in both relations.

These Topics Are Also In Your Syllabus
1 Nested Subqueries link
2 Nested Subqueries link
You May Find Something Very Interesting Here. link
3 Modification of the Database link
4 Join expressions link
5 Types Of Systems link

In contrast, the join operations we studied earlier that do not preserve nonmatched
tuples are called inner join operations, to distinguish them from the outer-join
operations.

We now explain exactly how each form of outer join operates.We can compute
the left outer-join operation as follows. First, compute the result of the inner join
as before. Then, for every tuple t in the left-hand-side relation that does not match
any tuple in the right-hand-side relation in the inner join, add a tuple r to the
result of the join constructed as follows:

• The attributes of tuple r that are derived from the left-hand-side relation are
filled in with the values from tuple t.

• The remaining attributes of r are filled with null values.

Figure 4.4 shows the result of:

These Topics Are Also In Your Syllabus
1 View of Data link
2 Introduction to Transaction Processing link
You May Find Something Very Interesting Here. link
3 Database Security link
4 Database Languages link
5 Types Of Systems link

select *
from student natural left outer join takes;

That result includes student Snow (ID 70557), unlike the result of an inner join,
but the tuple for Snow includes nulls for the attributes that appear only in the
schema of the takes relation.

As another example of the use of the outer-join operation, we can write the
query “Find all students who have not taken a course” as:

select ID
from student natural left outer join takes
where course_id is null;

The right outer join is symmetric to the left outer join. Tuples from the right?hand-side relation that do not match any tuple in the left-hand-side relation are
padded with nulls and are added to the result of the right outer join. Thus, if we
rewrite our above query using a right outer join and swapping the order in which
we list the relations as follows:

These Topics Are Also In Your Syllabus
1 SQL Data Definition link
2 Basic Structure of SQL Queries link
You May Find Something Very Interesting Here. link
3 Introduction to Database Security Issues link
4 Introduction to Database Security Issues link
5 Types Of Systems link

select *
from takes natural right outer join student;

we get the same result except for the order in which the attributes appear in the
result (see Figure 4.5).

The full outer join is a combination of the left and right outer-join types.
After the operation computes the result of the inner join, it extends with nulls
those tuples from the left-hand-side relation that did not match with any from the right-hand side relation, and adds them to the result. Similarly, it extends with
nulls those tuples from the right-hand-side relation that did not match with any
tuples from the left-hand-side relation and adds them to the result.

As an example of the use of full outer join, consider the following query:
“Display a list of all students in the Comp. Sci. department, along with the course
sections, if any, that they have taken in Spring 2009; all course sections from Spring
2009 must be displayed, even if no student from the Comp. Sci. department has
taken the course section.” This query can be written as:

These Topics Are Also In Your Syllabus
1 Database Security link
2 Database Languages link
You May Find Something Very Interesting Here. link
3 Database Design link
4 Relational Databases link
5 Types Of Systems link

select *
from (select *

from student
where dept_name= ’Comp. Sci’)
natural full outer join
(select
*
from takes
where semester = ’Spring’ and year = 2009);

The on clause can be used with outer joins. The following query is identical
to the first query we saw using “student natural left outer join takes,” except that
the attribute ID appears twice in the result.

select *
from
student left outer join takes on student.ID= takes.ID
;

These Topics Are Also In Your Syllabus
1 chapter 11 Object and Object-Relational Databases link
2 XML: Extensible Markup Language link
You May Find Something Very Interesting Here. link
3 Schema Diagrams link
4 Relational Query Languages link
5 Types Of Systems link

As we noted earlier, on and where behave differently for outer join. The reason
for this is that outer join adds null-padded tuples only for those tuples that do not
contribute to the result of the corresponding inner join. The on condition is part
of the outer join specification, but a where clause is not. In our example, the case
of the student tuple for student “Snow” with ID 70557, illustrates this distinction.
Suppose we modify the preceding query by moving the on clause predicate to
the where clause, and instead using an on condition of true.

select *
from student left outer join takes on true
where student.ID= takes.ID;

The earlier query, using the left outer join with the on condition, includes a tuple
(70557, Snow, Physics, 0, null, null, null, null, null, null ), because there is no tuple
in takes with ID = 70557. In the latter query, however, every tuple satisfies the join
condition true, so no null-padded tuples are generated by the outer join. The outer
join actually generates the Cartesian product of the two relations. Since there is
no tuple in takes with ID = 70557, every time a tuple appears in the outer join with
name = “Snow”, the values for student.ID and takes.ID must be different, and such
tuples would be eliminated by the where clause predicate. Thus student Snow
never appears in the result of the latter query.

3. Join Types and Conditions

To distinguish normal joins from outer joins, normal joins are called inner joins in
SQL. A join clause can thus specify inner join instead of outer join to specify that
a normal join is to be used. The keyword inner is, however, optional. The default
join type, when the join clause is used without the outer prefix is the inner join.
Thus,

select *
from
student join takes using (ID);

is equivalent to:

select *
from
student inner join takes using (ID);

Similarly, natural join is equivalent to natural inner join.
Figure 4.6 shows a full list of the various types of join that we have discussed.
As can be seen from the figure, any form of join (inner, left outer, right outer, or
full outer) can be combined with any join condition (natural, using, or on).


Rating - 3/5
512 views

Advertisements
Rating - 3/5