SQL Server – Where Clause with Full Outer Join

Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than:

r.roomprovider_id <> br.roomprovider_id

(And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when the right side is NULL?)

My guesses for their motivation include:

• job security through obfuscation
• job security through forcing bad performance, that they could fix later easily, since this will remove the possibility to seek on any of those columns if they are suitably indexed to satisfy the query
• CHECKSUM() was a shiny new function they just learned, and they just had to implement it somewhere
• they believed that CHECKSUM() was less prone to inaccuracy from collisions than other methods, or that - with only one input - it served any purpose whatsoever

To simplify your query slightly you have basically the following:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

Where you have no match in [src_boss_entwicklung].[dbo].[tckopf] the column commission.referenzid will be NULL, meaning this condition:

AND (commission.referenzid = 68 OR commission.referenzid = 68);

will not be true for these rows (because NULL = 68 evaluates to NULL). This effectively makes your RIGHT OUTER JOIN an INNER JOIN

I have never been a big fan of RIGHT JOIN especially when LEFT JOIN appears in the same query. I would rearrange the query to make all the outer joins left outer joins for continuity purposes:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

This does not solve the problem though since you still have the possibility of commission.referenzid being NULL and turning the LEFT OUTER JOIN into an INNER JOIN. You can get around this by moving the predicates to the JOIN conditiion:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND     
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) 
    )

I am also fairly certain this condition can be simplified to:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1

Making your final query:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;

ADDENDUM

As pointed out in a comment, you don't need the three subselects, you can just use the columns in the table aliased originDocForCurrency:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
    originDocForCurrency.poswert AS originDocCommission,
    originDocForCurrency.r_art AS originDocClass,
    originDocForCurrency.waehrung AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;