Join expressions




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.

Join expressions

Join expressions

Topics You May Be Interested In
What Is Database Management System Overview Of The Sql Query Language
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Using High-level Conceptual Data Models For Database Design Additional Basic Operations-the Rename Operation
Database Architecture Additional Basic Operations-string Operations
Introduction To Database Security Issues Er Diagrams, Naming Conventions, And Design Issues

 

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.

Topics You May Be Interested In
Modeling Of Union Types Using Categories Structure Of Relational Databases
Database Design Sql Data Definition
Data Storage And Querying Additional Basic Operations-the Rename Operation
Database Users And Administrators Ordering The Display Of Tuples
History Of Database Systems Set Operations-introduction

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

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

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):

Topics You May Be Interested In
Database-system Applications Introduction To Database Security Issues
Constraints And Characteristics Of Specialization And Generalization Hierarchies Chapter 11 Object And Object-relational Databases
Using High-level Conceptual Data Models For Database Design Overview Of Object Database Concepts
Relational Databases Initial Conceptual Design Of The Company Database
Database Users And Administrators Relationship Types Of Degree Higher Than Two

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;

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

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Structure Of Relational Databases
Specialization And Generalization Keys
View Of Data Chapter 11 Object And Object-relational Databases
Relational Databases Initial Conceptual Design Of The Company Database
Specialty Databases Sensitive Data And Types Of Disclosures

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:

Topics You May Be Interested In
What Is Database Management System Database Schema
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Attributes, And Keys-1
Database Design Relationship Types, Relationship Sets, Roles, And Structural Constraints
Database Architecture Relationship Types Of Degree Higher Than Two
History Of Database Systems Aggregate Functions

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

Join expressions


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.

Topics You May Be Interested In
Characteristics Of The Database Approach Initial Conceptual Design Of The Company Database
Data Abstraction, Knowledge Representation, And Ontology Concepts Example Of Notation- Uml Class Diagrams
Database Design Relationship Types Of Degree Higher Than Two
Relational Query Languages Ordering The Display Of Tuples
Overview Of The Sql Query Language Aggregate Functions

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.

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.

Topics You May Be Interested In
Specialization And Generalization Database Schema
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Keys
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Attributes, And Keys-1
Using High-level Conceptual Data Models For Database Design Attribute Specification In Select Clause
Relational Databases Refining The Er Design For The Company Database

• 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.

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:

Topics You May Be Interested In
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Transaction Management
Data Abstraction, Knowledge Representation, And Ontology Concepts Data Mining And Information Retrieval
View Of Data Keys
Relational Databases Basic Structure Of Sql Queries
Relational Databases Where Clause Predicates

• 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:

select *
from student natural left outer join takes;

Topics You May Be Interested In
Constraints And Characteristics Of Specialization And Generalization Hierarchies Sql Data Definition
Data Abstraction, Knowledge Representation, And Ontology Concepts A Sample Database Application
View Of Data Relationship Types, Relationship Sets, Roles, And Structural Constraints
Introduction To Transaction Processing Sensitive Data And Types Of Disclosures
Specialty Databases Aggregate Functions

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:

Topics You May Be Interested In
Constraints And Characteristics Of Specialization And Generalization Hierarchies Structure Of Relational Databases
Using High-level Conceptual Data Models For Database Design Overview Of The Sql Query Language
Purpose Of Database Systems Initial Conceptual Design Of The Company Database
Database Languages Relationship Types, Relationship Sets, Roles, And Structural Constraints
Relational Databases Ordering The Display Of Tuples

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).

Join expressions

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.

Topics You May Be Interested In
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Additional Basic Operations-the Rename Operation
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Attributes, And Keys-1
Introduction To Transaction Processing Initial Conceptual Design Of The Company Database
Relational Databases Refining The Er Design For The Company Database
Basic Structure Of Sql Queries Er Diagrams, Naming Conventions, And Design Issues

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:

select *
from (select *

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

Join expressions

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Basic Structure Of Sql Queries
Database-system Applications A Sample Database Application
Introduction To Transaction Processing Example Of Notation- Uml Class Diagrams
Relational Databases Control Measures
Discretionary Access Control Based On Granting And Revoking Privileges Set Operations-introduction

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
;

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;

Join expressions

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).



Frequently Asked Questions

+
Ans: We have restricted our attention until now to the extraction of information from the database. Now, we show how to add,remove, or change information with SQL. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: SQL provides a mechanism for nesting subqueries. A subquery is a select-from where expression that is nested within another query. view more..
+
Ans: We introduced the natural join operation. SQL provides other forms of the join operation, including the ability to specify an explicit join predicate, 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. view more..
+
Ans: In our examples up to this point, we have operated at the logical-model level. That is, we have assumed that the relations in the collection we are given are the actual relations stored in the database. view more..
+
Ans: A transaction consists of a sequence of query and/or update statements. view more..
+
Ans: Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. view more..




Rating - 3/5
466 views

Advertisements