Extended Projection

Like normal projection, but can create new columns

produces 1 row for every row of R, with 2 columns: M and N

NULL Values

• Field values can be unknown or inapplicable.
  • A tree with an unknown species.
  • The street of a tree in a park.
  • The '.' on many of your ID cards
• SQL provides a special NULL value for these cases

NULL makes things more complicated.

What happens if Trees.BORO is NULL?

WHERE clauses eliminate all non-True rows

    SELECT * FROM R
    WHERE A = 2 AND NOT (A = 2)
    

$$UNKNOWN \wedge \neg UNKNOWN$$

$$UNKNOWN \wedge UNKNOWN$$

$$UNKNOWN$$

Outer Join (⟗, ⟕, ⟖)

1. Include all results from the normal (inner) join.
2. Also include rows that don't get joined.

Outer Join

Inner Join

Normal, plain, simple join

Left Outer Join (⟕)

Include un-joined rows from the left hand side

Right Outer Join (⟖)

Include un-joined rows from the right hand side

[Full] Outer Join (⟗)

Include un-joined rows from either side

Streets ⟗ Trees

Only LEFT outer join

Only RIGHT outer join

Sort

Pick your favorite sort algorithm.

What happens if you don't have enough memory?

Key Idea: Merging 2 sorted lists requires $O(1)$ memory.

2-Way Sort

Pass 1

Create lots of (small) sorted lists.

Pass 2+

Merge sorted lists of size $N$ into sorted lists of size $2N$

Pass 1: Create Sorted Runs

Pass 2: Merge Sorted Runs

Repeat Pass 2 As Needed.

What's the bottleneck?

IO Cost: $O(N \cdot \lceil\log_2(N)\rceil)$
(with $N$ blocks)

Using More Memory

Pass 1

Sort Bigger Buffers

Re-Use Memory When Done

Pass 2

Merge $K$ Runs Simultaneously: $O(N \cdot \lceil\log_K(N)\rceil)$ IO

Replacement Sort

Replacement Sort

Replacement Sort

Replacement Sort

Replacement Sort

Replacement Sort

Replacement Sort

Replacement Sort

On average, we'll get runs of size $2 \cdot |WS|$

Normal Aggregates

Basic Aggregate Pattern

This is also sometimes called a "fold"

Init

Define a starting value for the accumulator

Fold(Accum, New)

Merge a new value into the accumulator

COUNT(*)

Init

$0$

Fold(Accum, New)

$Accum + 1$

SUM(A)

Init

$0$

Fold(Accum, New)

$Accum + New$

AVG(A)

Init

$\{ sum = 0, count = 0 \}$

Fold(Accum, New)

$\{ sum = Accum.sum + New, \\\;count = Accum.count + 1\}$

Finalize(Accum)

$\frac{Accum.sum}{Accum.count}$

Basic Aggregate Pattern

Init

Define a starting value for the accumulator

Fold(Accum, New)

Merge a new value into the accumulator

Finalize(Accum)

Extract the aggregate from the accumulator.

Basic Aggregate Types

Grey et. al. "Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals

Distributive

Finite-sized accumulator and doesn't need a finalize (COUNT, SUM)

Algebraic

Finite-sized accumulator but needs a finalize (AVG)

Holistic

Unbounded accumulator (MEDIAN)

Naive Idea: Keep a separate accumulator for each group

What could go wrong?

Alternative Grouping Algorithms

2-pass Hash Aggregate

Like 2-pass Hash Join: Distribute groups across buckets, then do an in-memory aggregate for each bucket.

Sort-Aggregate

Like Sort-Merge Join: Sort data by groups, then group elements will be adjacent.