Joins

1
Joins
2
Relational algebra

• Recall relational algebra was the study of
  actions that are performed on one or more tables
  and give as a result another table.
• The action is called an operation.
• The things acted upon (tables in this case) are
  known as operands.

3
Basic Operations

• The basic operations were
• Selection picking rows that satisfy some
  condition (predicate) from the table.
• Projection picking columns from the table.
• Union, intersection and set difference basic set
  operations that apply to union-compatible tables.
• Cartesian product concatenate two rows, one from
  each table make all such combinations.

4
The Join Operation

• An inner join of two tables is a Cartesian
  product operation followed by a selection
  operation (and possibly followed by a projection
  operation).
• If one straightforwardly implements a join, the
  Cartesian product intermediary can be huge.
• On the other hand, an earlier introduction of the
  selection condition may require a lot of
  searching (for matches).
• This is a reason that relational database
  management systems (RDBMs) can exhibit
  performance problems.

5
Variations of the join operation

• Theta join
• Equijoin (a particular type of Theta join)
• Natural join (a projection of an Equijoin)
• Outer join (handles unmatched records
  differently)
• Semijoin

6
Theta join (?-join)

• The restriction condition selecting from the
  Cartesian product does not have to be an
  equality, it could be any comparison operator
  such as
• Greater than (gt)
• Greater than or equal to (gt)
• Less than (lt)
• Less than or equal to (lt)
• Not equal to (ltgt)
• Using general condition to restrict the Cartesian
  product is known as a Theta join.
• R FS (R and S are tables, F is a condition)

7
Theta Join Example

• You have a table of customers who have a budget.
• You have a table of items which have a price.
• You want to advertise your items to customers who
  can afford them.
• The desired relationship is an inequality, a
  persons budget should be greater than the price
  of the item.

8
Theta Join Example Advertising to Customers who
can afford an item
The tables
Note that both have fields called ID, Access may
be fooled into thinking this is the basis for a
relationship.
9
Theta Join Example Advertising to Customers who
can afford an item
Right click on relationship line to eliminate.
10
Theta Join Example Advertising to Customers who
can afford an item
Choose fields to be displayed (projection).
11
Theta Join Example Advertising to Customers who
can afford an item
No condition imposed yet, just a Cartesian
product with projection.
12
Theta Join Example Advertising to Customers who
can afford an item
Cartesian product projected but not restricted.
13
Theta Join Example Advertising to Customers who
can afford an item
Condition added. Since its an inequality, this
is a Theta Join.
Also added Group By so the results would be
grouped by Item.
14
Theta Join Example Advertising to Customers who
can afford an item
15
Theta Join Example Advertising to Customers who
can afford an item
16
Equijoin

• The Equijoin is a special case of the Theta join
  in which the restriction condition is equality.
• Example a list of orders and the people placing
  them.

17
Equijoin Example a list of orders and the people
that placed them
18
Equijoin Example a list of orders and the people
who placed them
Condition is equality, making this an Equijoin.
19
Equijoin Example a list of orders and the people
who placed them
Order.CustomerID matches Customer.CustomerID even
though Access is showing lastnames instead.
20
The Natural Join

• Note that the previous join had both of the
  matching columns (Order.CustomerID and
  Customer.CustomerID)
• A join that projects out one of the matching
  columns is known as a Natural Join.

21
Natural Join Example (using Wizard)
22
Natural Join Example (using Wizard)
23
Natural Join Example (using Wizard)
24
Natural Join Example (using Wizard)
Projecting out matching column is what makes this
a Natural join.
25
Natural Join Example (using Wizard)
Does counts, totals etc. instead of listing
individual records.
26
Natural Join Example (using Wizard)
27
Natural Join Example (using Wizard)
Wheres Betty Rubble?
28
Semijoin

• Not all of the Customers have matches in the
  Order Table.
• By match we mean they have no order with that
  particular CustomerID.
• If we select out those rows from the Customer
  table that do have a match in the Order table, we
  have a Semijoin.
• Semijoins can be useful in distributed systems.
  You can cut down on the amount of information you
  send across the network.
• There may be more processing at the other end.

29
Semijoin Customer Orders
Two tables joined, but only one displayed in
results. A semijoin.
30
Semijoin Customers who have placed orders
Jane Doe appears twice.
31
Semijoin Customers who have placed orders (SQL
View)
32
Semijoin DISTINCT customers who have placed
orders (SQL View)
33
Semijoin DISTINCT customers who have placed
orders (DataSheet View)
34
Outer Join Bringing Back Betty

• All of the previous Equijoins have been what are
  called Inner Joins.
• If a record from one table does not have a match
  in the other table, it is eliminated.
• If this elimination feature is not desired, then
  you want to use an Outer Join.
• The Outer Join keeps records that do not have
  matches.
• R S

35
Access Help Join Type
36
Inner Join Customers and orders
Inner Join
37
Inner Join Customers and orders
38
Inner Join Customers and orders
Still Inner
39
Converting to Outer Join Right Click on
Relationship Line and choose Join Properties
40
Join Properties dialog box
41
Outer Join Customers and orders
Was a line, now is an arrow
42
Outer Join Customers with or without orders
43
Outer Join Customers and orders
Customers who have not placed orders.
44
References

• Database Systems, Rob and Coronel
• Database Systems, Connolly and Begg