Is FULL INNER JOIN really such a bad thing here?

Question

In general, I believe that “FULL EXTERNAL CONNECTION is considered harmful” to include the phrase.

Background:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

But I have a specific situation where it would be very convenient:

Given:

CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS
   /* Complicated set of equations, etc. */

and

CREATE TABLE Overrides(c1, c2, c3, fordate)

I need to customize the view above to follow this logic:

• For any Calcs rows whose calculated date does not have a corresponding override, select the calculated values.
• For any Calcs rows whose settlement date matches the override date, select the override values.
• Calcs .

, , :

CREATE VIEW Calcs AS ... (current logic)

CREATE VIEW CalcsWithOverrides AS

   SELECT * FROM Calcs WHERE NOT EXISTS (match in Overrides)

   UNION ALL

   SELECT override.c1, override.c2, override.c3, (other non-overridden columns)
       FROM Calcs INNER JOIN Overrides

   UNION ALL

   SELECT *, (null placeholders for non-overridden columns) FROM Overrides WHERE
       NOT EXISTS (match in Calcs)

, OUTER JOIN:

   SELECT
       COALESCE(Calcs.fordate, Overrides.fordate) AS fordate,
       -- Note that I am not using COALESCE() below on purpose: a null override should still override any calculated value
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c1 ELSE Overrides.c1 END AS C1,
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c2 ELSE Overrides.c2 END AS C2,
       CASE WHEN Overrides.fordate IS NULL THEN Calcs.c3 ELSE Overrides.c3 END AS C3,
       (....other columns from calcs that will be null for override-only rows...)
   FROM
       Calcs
       FULL OUTER JOIN Overrides ON Overrides.fordate = Calcs.fordate

, , OUTER JOIN , ?

Answer 1

, , . . , , , , .

, where join . where , join , .