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.