Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL OR in JOIN criteria RE: OR in JOIN criteria

  • December 13, 2017 at 3:10 pm

    #1972046

    r5d4 - Wednesday, December 13, 2017 10:26 AM

    Apologies if this has been asked before, searching for it yields too many results.

    I've been eliminating NOT EXISTS in some code by LEFT JOINing tableX and using a WHERE TableX.JoinColumn IS NULL
    Big gains, I'm pleased with that.

    My next challenge is OR in a JOIN criteria, so...

    SELECT id
    FROM table1
    LEFT JOIN table2
    ON table1.keycolumn = table2.keycolumn
    AND (table1.code1 = table2.code1 OR table1.text1 = table2.text1)

    Performing 2 SELECT queries and UNIONing them to avoid the OR comes to mind,
    The bigger picture is that I want to LEFT JOIN to this subquery to reproduce the NOT EXISTS functionality that performs so badly.
    I'm eliminating records that match the crazy OR join.

    This is a medical application, I can't change the tables, merely report on them.

    Any ideas most appreciated.
    Thank you

    You could use 2 LEFT JOINS and then determine which one of the 2 values you want to keep using COALESCE.


    SELECT ...
          , COALESCE(t2.col2, t3.col2) AS col2
          , COALESCE(t2.col3, t3.col3) AS col3
          , COALESCE(t2.col4, t3.col4) AS col4
    FROM table1 t
    LEFT JOIN table2 t2 ON t2.col1 = t.col1
    LEFT JOIN table2 t3 ON t3.text1 = t.text1

    You cannot utilize a UNION ALL - it would have to be a UNION which will require a sort operation to remove duplicates.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs