Join

Abstract

This chapter is about joins and their execution in in-memory database systems. In general, joins are a way to combine tuples of two or more tables. There are two general categories of joins which can be than further specialized

Self Test Questions

 

• 1. Hash-Join Complexity

  What is the complexity of the Hash-Join?

  • (a) O(n+m)

  • (b) O(n$^2$/m$^2$)

  • (c) O(n $\cdot$ m)

  • (d) O(n $\cdot$ log(n)+m+log(m))

• 2. Sort-Merge Join Complexity

  What is the complexity of the Sort-Merge Join?

  • (a) O(n+m)

  • (b) O(n$^2$/m$^2$)

  • (c) O(n $\cdot$ m)

  • (d) O(n $\cdot$ log(n)+m $\cdot$ log(m))

• 3. Join Algorithm Small Data Set

  Given is an extremely small data set. Which join algorithm would you choose in order to get the best performance?

  • (a) All join algorithms have the same performance

  • (b) Nested-Loop Join

  • (c) Sort-Merge Join

  • (d) Hash-Join

• 4. Join Algorithm Large Data Set

  Imagine a large data set with an index. Which join algorithm would you choose in order to get the best performance?

  • (a) Nested-Loop Join

  • (b) Sort-Merge Join

  • (c) All join algorithms have the same performance

  • (d) Hash-Join

• 5. Equi-Join

  What is the Equi-Join?

  • (a) If you select tuples from both relations, you use only one half of the join relations and the other half of the table is discarded

  • (b) If you select tuples from both relations, you will always select those tuples, that qualify according to a given equality predicate

  • (c) It is a join algorithm that ensures that the result consists of equal amounts from both joined relations

  • (d) It is a join algorithm to fetch information, that is probably not there. So if you select a tuple from one relation and this tuple has no matching tuple on the other relation, you would insert their NULL values there.

• 6. One-to-One-Relation

  What is a one-to-one relation?

  • (a) A one-to-one relation between two objects means that for each object on the left side, there are one or more objects on the right side of the joined table and each object of the right side has exactly one join partner on the left

  • (b) A one-to-one relation between two objects means that for exactly one object on the left side of the join exists exactly one object on the right side and vice versa

  • (c) A one-to-one relation between two objects means that each object on the left side is joined to one or more objects on the right side of the table and vice versa each object on the right side has one or more join partners on the left side of the table

  • (d) Each query which has exactly one join between exactly two tables is called a one-to-one relation, because one table is joined to exactly one other table.