Editing Join (SQL) (section)

=== Inner join and NULL values ===
Programmers should take special care when joining tables on columns that can contain [[Null (SQL)|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 <code> NOT NULL</code> before applying the remaining predicate condition(s). 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. Many [[transaction processing]] relational databases rely on [[ACID|atomicity, consistency, isolation, durability]] (ACID) data update standards to ensure data integrity, making inner joins an appropriate choice.  However, transaction databases usually also have desirable join columns that are allowed to be NULL.  Many reporting relational database and [[data warehouse]]s 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.  The choice to use an inner join depends on the database design and data characteristics.  A left outer join can usually be substituted for an inner join when the join columns in one table may contain NULL values.

Any data column that may be NULL (empty) should never be used as a link in an inner join, unless the intended result is to eliminate the rows with the NULL value. If NULL join columns are to be deliberately removed from the [[result set]], an inner join can be faster than an outer join because the table join and filtering is done in a single step. Conversely, an inner join can result in disastrously slow performance or even a server crash when used in a large volume query in combination with database functions in an SQL Where clause.<ref>Greg Robidoux, "Avoid SQL Server functions in the WHERE clause for Performance", MSSQL Tips, 3 May 2007</ref><ref>Patrick Wolf, "Inside Oracle APEX "Caution when using PL/SQL functions in a SQL statement", 30 November 2006</ref><ref>Gregory A. Larsen, "T-SQL Best Practices - Don't Use Scalar Value Functions in Column List or WHERE Clauses", 29 October 2009,</ref> A function in an SQL Where clause can result in the database ignoring relatively compact table indexes. The database may read and inner join the selected columns from both tables before reducing the number of rows using the filter that depends on a calculated value, resulting in a relatively enormous amount of inefficient processing.

When a result set is produced by joining several tables, including master tables used to look up full-text descriptions of numeric identifier codes (a [[Lookup table]]), a NULL value in any one of the foreign keys can result in the entire row being eliminated from the result set, with no indication of error.  A complex SQL query that includes one or more inner joins and several outer joins has the same risk for NULL values in the inner join link columns.

A commitment to SQL code containing inner joins assumes NULL join columns will not be introduced by future changes, including vendor updates, design changes and bulk processing outside of the application's data validation rules such as data conversions, migrations, bulk imports and merges.

One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.