Mediaspace scheduled maintenance: Aug 25, 2026 07:00 - 12:00 AM. During this time, videos will be temporarily unavailable. Check status updates.

Lecture

Query Operators Part 2

In course

CS-322: Introduction to database systems

This course provides a deep understanding of the concepts behind data management systems. It covers fundamental data management topics such as system architecture, data models, query processing and optimization, database design, storage organization, and t ...

Description

This lecture covers query processing with relational operations, focusing on simple and index nested loops joins, as well as the cost comparison between different join methods. It also explains the two-pass hash join algorithm, hybrid hash join, and the benefits of sort-merge join over hash join. The lecture concludes with a summary of general join conditions, set operations, aggregate operations, and the impact of buffering on query processing.

This video is available exclusively on Mediaspace for a restricted audience. Please log in to MediaSpace to access it if you have the necessary permissions.

Watch on Mediaspace

Instructors (3)

Christoph Koch

Angelos Christos Anadiotis

Anastasia Ailamaki

Official source

Related concepts (32)

Join (SQL)

A join clause in the Structured Query Language (SQL) combines columns from one or more tables into a new table. The operation corresponds to a join operation in relational algebra. Informally, a join stitches two tables and puts on the same row records with matching fields : INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. To explain join types, the rest of this article uses the following tables: Department.DepartmentID is the primary key of the Department table, whereas Employee.DepartmentID is a foreign key.

Hash join

The hash join is an example of a join algorithm and is used in the implementation of a relational database management system. All variants of hash join algorithms involve building hash tables from the tuples of one or both of the joined relations, and subsequently probing those tables so that only tuples with the same hash code need to be compared for equality in equijoins. Hash joins are typically more efficient than nested loops joins, except when the probe side of the join is very small.

Sort-merge join

The sort-merge join (also known as merge join) is a join algorithm and is used in the implementation of a relational database management system. The basic problem of a join algorithm is to find, for each distinct value of the join attribute, the set of tuples in each relation which display that value. The key idea of the sort-merge algorithm is to first sort the relations by the join attribute, so that interleaved linear scans will encounter these sets at the same time.

Nested function

In computer programming, a nested function (or nested procedure or subroutine) is a function which is defined within another function, the enclosing function. Due to simple recursive scope rules, a nested function is itself invisible outside of its immediately enclosing function, but can see (access) all local objects (data, functions, types, etc.) of its immediately enclosing function as well as of any function(s) which, in turn, encloses that function.

For loop

In computer science a for-loop or for loop is a control flow statement for specifying iteration. Specifically, a for loop functions by running a section of code repeatedly until a certain condition has been satisfied. For-loops have two parts: a header and a body. The header defines the iteration and the body is the code that is executed once per iteration. The header often declares an explicit loop counter or loop variable. This allows the body to know which iteration is being executed.

Related lectures (37)

Physics-inspired Computing and Neuromorphic Algorithms

Explores physics-inspired computing, analog computing, optical computing, and the potential of spin waves for physics-based computing.

Vision-Language-Action Models: Training and Applications

Delves into training and applications of Vision-Language-Action models, emphasizing large language models' role in robotic control and the transfer of web knowledge. Results from experiments and future research directions are highlighted.

Scanning Electron Microscopy: Signals and Detectors

Covers the principles of Scanning Electron Microscopy, including SEM signals, detectors, and energy spectrum of electrons, as well as the efficiency of X-ray generation.

ECG: Valsalva Maneuver

Covers the Valsalva maneuver in ECG analysis, focusing on amplitude variations and pulse integration.

ECG Hemodynamics Figure