Page 129 - SQL

,MAP_CTCOU_DateTo
FROM T_MAP_Contacts_Ref_OrganisationalUnit
WHERE MAP_CTCOU_SoftDeleteStatus = 1
AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID

/*
AND
(
(@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
AND
(@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
)
*/
ORDER BY MAP_CTCOU_DateFrom
) AS FirstOE

FULL JOIN

One type of JOIN that is less known, is the FULL JOIN.
(Note: FULL JOIN is not supported by MySQL as per 2016)

A FULL OUTER JOIN returns all rows from the left table, and all rows from the right table.

If there are rows in the left table that do not have matches in the right table, or if there are rows in
right table that do not have matches in the left table, then those rows will be listed, too.

Example 1 :

SELECT * FROM Table1

FULL JOIN Table2
ON 1 = 2

Example 2:

SELECT
COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear
,COALESCE(T_Budget.Value, 0.0) AS RPT_Value
FROM T_Budget

FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear
ON tYear.Year = T_Budget.Year

Note that if you're using soft-deletes, you'll have to check the soft-delete status again in the
WHERE-clause (because FULL JOIN behaves kind-of like a UNION);
It's easy to overlook this little fact, since you put AP_SoftDeleteStatus = 1 in the join clause.

Also, if you are doing a FULL JOIN, you'll usually have to allow NULL in the WHERE-clause;
forgetting to allow NULL on a value will have the same effects as an INNER join, which is
something you don't want if you're doing a FULL JOIN.

Example:

https://riptutorial.com/ 111