Defining SQL Traps

Objectives

After completing this lesson, you will be able to:

  • Analyze SQL traps

SQL Traps

SQL traps are caused by the way in which the parts of a select statement are combined when referencing a relational database. They can cause queries to return inaccurate results, usually by multiplying the results.

Types of SQL Traps

Chasm trap

A chasm trap is a type of join path among tables when many-to-one joins converge on a single table with no context in place to separate the converging join paths.

Fan trap

A fan trap occurs when a one-to-many join links a table, which is in turn linked by another one-to-many join.

In SQL, a SELECT statement processes the SELECT, FROM, and WHERE clauses first (with the exception of any aggregates). This process creates a product of all the tables in the FROM clause based on the joins and restrictions specified in the WHERE clause. This can be thought of as a virtual table. Problems can occur if an aggregate is applied. This results in the wrong output being generated. This is problematic because SQL does not produce an error message; it projects results that are incorrect, usually by multiplying measure object values.

Unlike loops that return fewer rows than expected, chasm traps and fan traps return too many rows.

Log in to track your progress & complete quizzes