Outer joins: left, right, full

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. How many tuples are in STUDENT ⋈ COURSE (natural), and why? from Lesson 3.1.3
2. What is R ∩ S written with − only? from Lesson 3.1.2
3. What is NULL + 1, and what is NULL = NULL? from Lesson 2.1.2

1 · The Hook

"List every department with the names of its students." A plain join drops a department that has no students, yet the dean wants it in the list, with an empty space. Outer joins keep the tuples that a join would drop, and pad the missing side with NULL.

2 · Predict First

R(A, B) = {(1, x), (2, y), (3, z)} and S(B, C) = {(x, p), (y, q), (w, r)}. R ⋈ S has 2 tuples. If every tuple of R and every tuple of S must appear somewhere in the result, how many tuples will it have?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

4: the 2 matched tuples, plus (3, z, NULL) for R's unmatched z, plus (NULL, w, r) for S's unmatched w. That is the full outer join.

Three outer joins

Start from the natural (or theta) join, then add the dangling tuples (those with no partner):

With R and S from the predict:

R ⟕ S

R ⟖ S

R ⟗ S has all four rows.

Sizes

• |R ⟕ S| = |R ⋈ S| + (number of unmatched R tuples) ≥ |R|.
• |R ⟖ S| = |R ⋈ S| + (unmatched S tuples) ≥ |S|.
• |R ⟗ S| = |R ⋈ S| + (unmatched R) + (unmatched S).
• R ⟖ S gives the same tuples as S ⟕ R (only the column order differs).

On the college data

DEPT(dname, building) holds CS, EE, ME and a new department CE with no students. DEPT ⟕dname = dept STUDENT gives 4 matched rows (CS twice, EE, ME) plus (CE, …, NULL, NULL, NULL, NULL): 5 rows. A plain join would give 4 and hide CE.

Outer joins and NULL

Padding NULLs behave like any NULL: a later condition such as σcgpa > 8 gives UNKNOWN on them and drops them. A condition "is null" on a column from the padded side finds exactly the dangling tuples: "departments with no students" = σroll is null(DEPT ⟕dname = dept STUDENT).

4 · Visual

        R ⟕ S                R ⋈ S               R ⟖ S
  keep all of R        keep only matches      keep all of S
  ┌──────────┐         ┌──────────┐          ┌──────────┐
  │ R match  │         │ R match  │          │ R match  │
  │ R alone  │+NULL    └──────────┘          │  NULL+   │S alone
  └──────────┘                               └──────────┘

R ⟗ S = matches + R alone (NULL right) + S alone (NULL left)

5 · Worked Examples

Example 1 Easy

R(A, B) = {(1, a), (2, b)}, S(B, C) = {(a, 5), (c, 6), (d, 7)}. Sizes of R ⋈ S, R ⟕ S, R ⟖ S, R ⟗ S?

Solution

Matches: (1, a, 5) → 1. Unmatched R: (2, b) → 1. Unmatched S: c, d → 2. ⋈ 1, ⟕ 2, ⟖ 3, ⟗ 4.

Example 2 Medium

Write "departments with no students" with an outer join.

Solution

πdname(σroll is null(DEPT ⟕dname = dept STUDENT)). The left outer join pads CE with NULLs; the selection keeps only those. (Equivalently: πdname(DEPT) − πdept(STUDENT).)

Example 3 GATE

R has 10 tuples, S has 8. R ⋈ S has 6 tuples; 4 tuples of R and 3 tuples of S have no partner. How many tuples are in R ⟗ S?

Solution

6 + 4 + 3 = 13.

8 · Connect the Dots

Lesson 3.1.3 joined relations and lost the unmatched tuples; this lesson kept them with outer joins. Next: the hardest RA operator, division, for "for all" questions such as "students who took every CS course".

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · NAT · 1 mark(s) — Left outer join

R(A, B) = {(1, a), (2, b), (3, c)}, S(B, C) = {(a, x), (a, y)}. How many tuples in R ⟕ S?

Numerical answer: ________

Q2 · NAT · 2 mark(s) — Full outer join

R has 5 tuples, S has 6. R ⋈ S has 4 tuples; 2 R tuples and 3 S tuples are unmatched. How many tuples in R ⟗ S?

Numerical answer: ________

Q3 · MCQ · 1 mark(s) — Definitions

In R ⟖ S, the NULLs appear in:

1. (A) R's attributes for unmatched S tuples
2. (B) both sides for every tuple
3. (C) S's attributes for unmatched R tuples
4. (D) nowhere

Q4 · MCQ · 2 mark(s) — Query

Which expression gives the courses that nobody has enrolled in?

1. (A) πcid(σroll is null(COURSE ⟕ ENROLL))
2. (B) πcid(σroll is null(COURSE ⟖ ENROLL))
3. (C) πcid(COURSE ⋈ ENROLL)
4. (D) πcid(ENROLL) − πcid(COURSE)

Q5 · MSQ · 2 mark(s) — Sizes

For any R and S, which are always true?

1. (A) |R ⟕ S| = |R|
2. (B) |R ⟕ S| ≥ |R ⋈ S|
3. (C) |R ⟕ S| ≥ |R|
4. (D) |R ⟗ S| ≥ |R ⟕ S|

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

dangling tupleleft outer join ⟕right outer join ⟖full outer join ⟗NULL padding