16f6c0aecf
Inline adoc files when they are included exactly once. Also fix language tags because this inlining gives us better information on what language the code is written in.
52 lines
1.3 KiB
Plaintext
52 lines
1.3 KiB
Plaintext
== Why is this an issue?
|
|
|
|
``++NATURAL JOIN++`` is a type of equi-join which implicitly compares all identically-named columns of the two tables. While this a feature which may seem convenient at first, it becomes hard to maintain over time.
|
|
|
|
|
|
Consider an EMPLOYEE table with the columns FULL_NAME, and DEPT_ID, and a DEPARTMENT table with the columns DEPT_ID, and NAME. A natural join between those tables will join on the DEPT_ID column, which is the only identically-named column.
|
|
|
|
|
|
But, if a new NAME column is later added to the EMPLOYEE table, then the join will be done on both DEPT_ID and NAME. Natural joins make simple changes such as adding a column complicated and are therefore better avoided.
|
|
|
|
|
|
=== Noncompliant code example
|
|
|
|
[source,sql]
|
|
----
|
|
BEGIN
|
|
SELECT *
|
|
INTO employeeArray
|
|
FROM employee
|
|
NATURAL JOIN departement; -- Non-Compliant, the join predicate is implicit
|
|
END;
|
|
/
|
|
----
|
|
|
|
|
|
=== Compliant solution
|
|
|
|
[source,sql]
|
|
----
|
|
BEGIN
|
|
SELECT *
|
|
INTO employeeArray
|
|
FROM employee
|
|
JOIN departement
|
|
USING (dept_id); -- Compliant, explicit join predicate
|
|
END;
|
|
/
|
|
----
|
|
|
|
ifdef::env-github,rspecator-view[]
|
|
|
|
'''
|
|
== Implementation Specification
|
|
(visible only on this page)
|
|
|
|
=== Message
|
|
|
|
Refactor this query to join explicitly on specific columns.
|
|
|
|
|
|
endif::env-github,rspecator-view[]
|