ioplink.blogg.se

A left outer join can usually be substituted for an inner join when the join columns in one table may contain NULL values.

The choice to use an inner join depends on the database design and data characteristics. Many reporting relational database and data warehouses use high volume Extract, Transform, Load (ETL) batch updates which make referential integrity difficult or impossible to enforce, resulting in potentially NULL join columns that an SQL query author cannot modify and which cause inner joins to omit data with no indication of an error. However transaction databases usually also have desirable join columns that are allowed to be NULL. Many transaction processing relational databases rely on Atomicity, Consistency, Isolation, Durability (ACID) data update standards to ensure data integrity, making inner joins an appropriate choice.

The Inner join can only be safely used in a database that enforces referential integrity or where the join columns are guaranteed not to be NULL. Programmers should take special care when joining tables on columns that can contain NULL values, since NULL will never match any other value (not even NULL itself), unless the join condition explicitly uses a combination predicate that first checks that the joins columns are NOT NULL before applying the remaining predicate condition(s).