DatabasesLesson 2 of 2 in the course Relational model and SQL

SQL Joins: Matching, Row Preservation, and Execution

5 flashcards · 5 quiz questions · 5 match pairs · 3 fill-in sentences · English

Save course
Lesson progress
0 / 5

Your progress is kept in this browser only.Sign up to save it

Summary

A SQL join combines columns from tables into a result table. Joins are essential when related information is stored separately, such as employee names in one table and department names in another.

A join combines rows from tables according to a specified relationship. For example, an Employee table may store a DepartmentID foreign key that refers to the Department table's primary key. Joining on that key can place employee and department information in the same result row.

All flashcards

What is an equi-join?
A join whose predicate compares values for equality.
What does a full outer join preserve?
Matching rows and unmatched rows from both inputs.
What is a nested-loop join?
An algorithm that searches one input for matches for rows from the other.
What is a join index?
An index structure that can help answer recurring queries involving joins.
What does a bushy query plan look like?
A plan in which a join can take two inputs that are themselves join results.

Quiz questions

  1. What does a left outer join do when a left-side row has no match on the right?Answer: It retains the row and fills right-side columns with NULL.
  2. Why can a NATURAL JOIN change behavior after a schema change?Answer: A newly shared column name may become an additional join condition.
  3. Why do self-join queries commonly give the two references to a table different aliases?Answer: The aliases distinguish the table's two roles in the query.
  4. How does a hash join commonly find matches for an equality predicate?Answer: It builds a hash structure from one input and probes it with the other.
  5. What characterizes a bushy query plan?Answer: A join can take two inputs that are themselves join results.

Based on the Wikipedia article Join (SQL), licensed CC BY-SA 4.0. Adapted into a summary, flashcards and quizzes.

More in Databases

All Databases lessons
DatabasesSQL Foundations: Language Model, History, Types, and Semantics5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesDatabase Query Optimization: Plans, Costs, and Estimation5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesDatabase Index Structures, Query Patterns, and Trade-Offs5 flashcards · 5 quiz questions · 4 gamesPlay free →DatabasesDatabase Transaction Isolation: Anomalies, Levels, and Concurrency Control5 flashcards · 5 quiz questions · 4 gamesPlay free →