How to change this query to use joins (inner or left) but not subquery to get same result

Question

How to change this query to use joins (inner or left) but not subquery

 SELECT * FROM   External_Blk_Itm_Contracts ELC
    WHERE
    NOT EXISTS(SELECT
                    NULL
                FROM        
                    [BUDCONTRACTS] c
                INNER JOIN 
                    BUDTERMINALS t on t.TerminalID = c.TerminalID  AND  t.MBFTERMINALNAME = ELC.TerminalName AND  c.CONTRACTNAME = ELC.ContractName 
                INNER JOIN 
                    BudCustomers ct on ct.CustomerId = c.CustomerId AND ELC.CustomerName = ct.LegalName
               )
                AND ELC.ContractName = '00-000'

I think it should perform better as it is, left joins will make it less efficient **ITHINK** — M.Ali, Nov 12 '15 at 20:44
What is the schema of the tables involved? In particular the primary keys and foreign key columns? Could you post an [SQL fiddle](http://sqlfiddle.com/)? — W.Prins, Nov 12 '15 at 20:45
Possible duplicate of [How to select rows with no matching entry in another table?](http://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table) — Tab Alleman, Nov 12 '15 at 20:48
The answer in the duplicate can be adapted to answer your question. — Tab Alleman, Nov 12 '15 at 20:57

W.Prins · Answer 1 · 2015-11-12T20:58:04.800

Untested (obviously):

SELECT distinct
  ELC.* 
FROM
  External_Blk_Itm_Contracts ELC
LEFT JOIN  
  BUDCONTRACTS c on c.CONTRACTNAME = ELC.ContractName
LEFT JOIN 
  BUDTERMINALS t on t.TerminalID = c.TerminalID and t.MBFTERMINALNAME = ELC.TerminalName
LEFT JOIN 
  BudCustomers ct on ct.CustomerId = c.CustomerId and ELC.CustomerName = ct.LegalName
WHERE   
  c.CONTRACTNAME is NULL 
  and t.TerminalID is NULL
  and ct.CustomerID is NULL
  AND ELC.ContractName = '00-000'

My biggest question about this is why we're joining using name fields to ELC? Isn't there e.g. TerminalID, CustomerID and CotractID available on ELC?

score 0 · Answer 2 · answered Nov 12 '15 at 20:54

I fixed this by

        SELECT ELC.* FROM External_Blk_Itm_Contracts ELC
        INNER JOIN BUDTERMINALS BT ON BT.MBFTERMINALNAME = ELC.TerminalName AND ELC.ContractName = '00-000' 
        INNER JOIN BudCustomers BCUST ON BCUST.LegalName = ELC.CustomerName
        LEFT JOIN BUDCONTRACTS BCON ON BCON.CONTRACTNAME = ELC.ContractName AND BCON.TERMINALID = BT.TERMINALID AND BCON.CUSTOMERID = BCUST.CustomerID  
        WHERE    BCON.CONTRACTID IS NULL

How to change this query to use joins (inner or left) but not subquery to get same result

2 Answers2