SQL SERVER – Join Better Performance – LEFT JOIN or NOT IN?

First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing a query? The answer is: It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN. If you need to refer the query which demonstrates the mentioned clauses, review following two queries for Join Better Performance.

USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO
SELECT p.ProductID
FROM Production.Product p
LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID
WHERE w.ProductID IS NULL;
GO

Now let us examine the actual execution plan of both the queries. Click on image to see larger image.
SQL SERVER - Join Better Performance - LEFT JOIN or NOT IN? executionplansmall

You can clearly observe that first query with a NOT IN takes 20% resources of the execution plan and LEFT JOIN takes 80% resources of execution plan. It is better to use NOT IN clause over LEFT JOIN in this particular example. Please note that this is not generic conclusion and applies to this example only. Your results may vary on many factors. Let me know your comments if you have guessed this correct or not.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Surface Area Configuration Tools Reduce Exposure To Security Risks
Next Post
SQL SERVER – Converting Subqueries to Joins

Related Posts

70 Comments. Leave new

  • Thanks a lot!!!

    Reply
  • You are a star, three days of banging my head against the monitor have come to an end as a result of reading this post.

    Thanks

    Reply
  • hey Dave great thread, can you please tell me how did analyse the preform of the query,whats the meaning of 80% of the batch,and whats the tool you are using for the analysis??…
    hope for the quick reply.
    bbye

    Reply
  • I am not aware of how you measured resource percentage given “20% resources of execution plan and LEFT JOIN takes 80% resources of execution plan”. I need little more explanation.

    Regards,
    Pradip

    Reply
  • When you execute both query at one time it will show relative cost for each query.

    Reply
  • Thanks a lotI consider this site a very good one for me to face the interview questions ahead of me. ,

    Reply
  • I have a table, and there is a date column in it, and many records in it.
    My query is, i want to retrieve all the dates of a particular calender year which are not there in the table.
    i.e., i want to know the dates which are not present in my table and which has a gap of more than 24 hrs

    To put it in simple, i want to know the transaction of a particular inventory for which there is no transaction on that particular date, i want to know all the dates of year(with 24 hrs gap)

    Reply
    • @Jalan

      Either give sample data or give us more details. When you say 24 Hours, is this 24 hours between two successful dates ? Please help us to help you by providing more information.

      ~ IM.

      Reply
  • DATE table
    01/01/09 01.00 AM
    02/01/09 12.00 PM
    04/01/09 02.00 PM
    05/01/09 05.00 PM
    06/01/09 06.00 PM
    07/01/09 09.00 PM
    08/01/09 09.00 PM
    12/01/09 04.00 PM

    Consider these as sample dates, now my output should be
    03/01/09
    09/01/09
    10/01/09
    11/01/09

    Reply
    • Huh! This was a tricky one. I’m not sure if I still understood this correctly though since I’m not getting that last date 11/01/09 in the results.

      CREATE FUNCTION fn_get_days_forward
      (
      @FirstDay datetime
      )
      RETURNS @dates TABLE
      (
      dt DATETIME
      )
      AS
      BEGIN
      DECLARE @nextDay DATETIME
      SELECT TOP 1 @nextDay = DATEADD(DAY, -1, dt) FROM dates WHERE dt > @FirstDay ORDER BY dt ASC

      SET @FirstDay = DATEADD(DAY, 1, @FirstDay)
      WHILE @FirstDay < @nextDay
      BEGIN
      INSERT INTO @dates SELECT @FirstDay

      SET @FirstDay = DATEADD(DAY, 1, @FirstDay)
      END

      RETURN
      END
      GO

      SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, days.dt))
      FROM dates CROSS APPLY fn_get_days_forward(dt) AS days

      Reply
  • Nice Post, I am looking for a method to replace IN/NOT IN

    Most of the time I heard Join is better than subqueries

    Reply
  • Hey All,
    Can anybody tell me how to use not in clause with more tan 1 parameter or any other way.

    e..g
    select mobileno from table1 where (boxno,fileno) not in(select boxno,fileno from tabel2)

    Reply
  • Hi the other day i make a equivalence between compare JOINS, WHERE, NOT IN, AND EXIST, this was in ORACLE 10G:

    I- CREACIÓN TABLAS:
    CREATE TABLE TB_DEMO_A( ID NUMBER, DESCRIPCION VARCHAR2( 10 ) );
    CREATE TABLE TB_DEMO_B( ID NUMBER, DESCRIPCION VARCHAR2( 10 ) );

    II- CREACIÓN INSERTS:
    INSERT INTO TB_DEMO_A VALUES( 1, ‘PRIMERO’ );
    INSERT INTO TB_DEMO_A VALUES( 2, ‘SEGUNDO’ );
    INSERT INTO TB_DEMO_A VALUES( 3, ‘TERCERO’ );
    INSERT INTO TB_DEMO_A VALUES( 4, ‘CUARTO’ );

    INSERT INTO TB_DEMO_B VALUES( 1, ‘PRIMERO’ );
    INSERT INTO TB_DEMO_B VALUES( 2, ‘SEGUNDO’ );
    INSERT INTO TB_DEMO_B VALUES( 5, ‘QUINTO’ );
    INSERT INTO TB_DEMO_B VALUES( 6, ‘SEXTO’ );

    III- SELECTS:
    SELECT a.* FROM TB_DEMO_A a;
    SELECT b.* FROM TB_DEMO_B b;

    IV.- AMARRES X CASO:

    4.1.- “CONJUNTO A” INTERSECCION “CONJUNTO B”:

    4.1.1.- FORMA #1:
    SELECT a.*, b.*
    FROM TB_DEMO_A a,
    TB_DEMO_B b
    WHERE a.ID = b.ID;

    4.1.1.- FORMA #2:
    SELECT a.*, b.*
    FROM TB_DEMO_A a
    INNER JOIN TB_DEMO_B b
    ON a.ID = b.ID;

    4.2.- (“CONJUNTO A” INTERSECCION “CONJUNTO B”) + ( “CONJUNTO A” MENOS CONJUNTO B” ):

    4.2.1.- FORMA #1:
    SELECT a.*, b.*
    FROM TB_DEMO_A a,
    TB_DEMO_B b
    WHERE a.ID = b.ID(+);

    4.2.2.- FORMA #2:
    SELECT a.*, b.*
    FROM TB_DEMO_A a
    LEFT JOIN TB_DEMO_B b
    ON a.ID = b.ID;

    4.3.- (“CONJUNTO A” INTERSECCION “CONJUNTO B”) + ( “CONJUNTO B” MENOS CONJUNTO A” ):

    4.3.1.- FORMA #1:
    SELECT a.*, b.*
    FROM TB_DEMO_A a,
    TB_DEMO_B b
    WHERE a.ID(+) = b.ID;

    4.3.2.- FORMA #2:
    SELECT a.*, b.*
    FROM TB_DEMO_A a
    RIGHT JOIN TB_DEMO_B b
    ON a.ID = b.ID;

    4.4.- ( “CONJUNTO A” MENOS CONJUNTO B” ):

    4.4.1.- FORMA #1:
    SELECT a.*, b.*
    FROM TB_DEMO_A a,
    TB_DEMO_B b
    WHERE a.ID = b.ID(+) AND
    b.ID IS NULL;

    4.4.2.- FORMA #2:
    SELECT a.*, b.*
    FROM TB_DEMO_A a
    LEFT JOIN TB_DEMO_B b
    ON a.ID = b.ID
    WHERE b.ID IS NULL

    4.4.3.- FORMA #3:
    SELECT a.*
    FROM TB_DEMO_A a
    WHERE a.ID
    NOT IN ( SELECT b.ID
    FROM TB_DEMO_B b
    );

    4.4.4.- FORMA #4:
    SELECT a.*
    FROM TB_DEMO_A a
    WHERE NOT EXISTS( SELECT b.ID
    FROM TB_DEMO_B b
    WHERE a.ID = b.ID
    );

    4.5.- ( “CONJUNTO B” MENOS CONJUNTO A” ):

    4.5.1.- FORMA #1:
    SELECT a.*, b.*
    FROM TB_DEMO_A a,
    TB_DEMO_B b
    WHERE a.ID(+) = b.ID AND
    a.ID IS NULL;

    4.5.2.- FORMA #2:
    SELECT a.*, b.*
    FROM TB_DEMO_A a
    LEFT JOIN TB_DEMO_B b
    ON a.ID = b.ID
    WHERE a.ID IS NULL

    4.5.3.- FORMA #3:
    SELECT b.*
    FROM TB_DEMO_B b
    WHERE b.ID
    NOT IN ( SELECT a.ID
    FROM TB_DEMO_A a
    );

    4.5.4.- FORMA #4:
    SELECT b.*
    FROM TB_DEMO_B b
    WHERE NOT EXISTS( SELECT a.ID
    FROM TB_DEMO_A a
    WHERE b.ID = a.ID
    );

    Diferents Style to Make the same.

    Reply
  • Thanks for the advice – very practical performance analysis approach. Any idea how this might correlate to the set size in the subquery or join?

    Reply
  • neeraj prasad sharma
    January 18, 2011 6:12 pm

    good article………………….

    Reply
  • ‘Show result
    Select ’02’,ccd01emp,ccd01ano,ccd01mes,ccd01subd,
    ccd01numer,ccd01ord,ccd01cta,ccd01deb,ccd01hab,ccd01con
    from ccd Where
    ccd01emp=’01’
    And ccd01ano=’2011′
    And ccd01mes=’05’
    And ccd01cta Not in (Select ccm01cta from ccm01cta Where
    ccm01emp=’01’
    And ccm01aa=’2011′)

    ‘Not Show result
    Select ’02’,ccd01emp,ccd01ano,ccd01mes,ccd01subd,
    ccd01numer,ccd01ord,ccd01cta,ccd01deb,ccd01hab,ccd01con
    from ccd left outer Join ccm01cta
    On ccd01cta = ccm01cta
    Where
    ccd01emp=’01’
    And ccm01emp=’01’
    And ccd01ano=’2011′
    And ccm01aa=’2011′
    And ccd01mes=’05’
    And isnull(ccm01cta,”)=”

    Reply
  • I’m trying to change this subquery into a join and was hoping I might get a comment back here. :)

    CODE:
    SELECT col1, col2, col3
    FROM dbo.myTable AS T1
    WHERE (col2 = 2) AND (col3 = 1) OR
    (col2 = 1) AND (col3 = 2)
    AND (col1 NOT IN (SELECT col1
    FROM dbo.myTable
    WHERE (col2 = 2) AND (col3 = 1)))

    Reply
    • Figured it out, was simpler than I thought :)

      CODE:
      SELECT col1, col2, col3
      FROM dbo.myTable AS T1
      WHERE (col2 = 2) AND (col3 = 1) OR
      (col2 = 1) AND (col3 = 2)
      AND (col1 NOT IN (SELECT col1
      FROM dbo.myTable
      WHERE (col2 = 2) AND (col3 = 1)))

      Is the same as…

      CODE:
      SELECT col1, col2, col3
      FROM dbo.myTable AS T1 LEFT OUTER JOIN
      dbo.myTable AS T2 ON T1.col1 = T2.col1
      WHERE (T1.col2 = 2) AND (T1.col3 = 3) OR
      (T1.col2 = 1) AND (T2.col3 = 2) AND (T2.col1 IS NULL)

      The reason I wanted to find a way to convert this subquery into a join was that I am trying to create this as an indexed view. Figured I’d post this response in hopes that it may help someone else in the future. :)

      Reply
  • Bit tricky.

    Reply
  • if exists(select a.PRODH from MVKE as a inner join ZSD_FREIGHT_BOL as b on SUBSTRING(a.PRODH,1,18)=b.PRODH and REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material)
    begin
    if not exists(select VRKME from MVKE where REPLACE(LTRIM(REPLACE(MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material and VRKME=”)
    begin
    if exists(select KUNNR from KNMT where cast(cast(KUNNR as int) as varchar(20))=@CustCode and REPLACE(LTRIM(REPLACE(MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material)
    begin
    select distinct(c.BSSKZ) as NominalVar,REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′) as MaterialNo,a.MAKTX as MaterialDesc,
    b.KDMAT as CustPartNo,d.VRKME as SkuUM,e.MEINH as ExtUM from MAKT as a
    inner join KNMT as b on REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(b.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MLGN as c on REPLACE(LTRIM(REPLACE(c.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MVKE as d on REPLACE(LTRIM(REPLACE(d.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join ZSD_FREIGHT_BOL as e on e.PRODH=SUBSTRING(d.PRODH,1,18)
    inner join MARM as f on REPLACE(LTRIM(REPLACE(f.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    where a.SPRAS=’E’ and CAST(CAST(b.KUNNR as int) as varchar(20))=@CustCode and REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material
    end
    else
    begin
    select distinct(c.BSSKZ) as NominalVar,REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0’) as MaterialNo,a.MAKTX as MaterialDesc,
    ‘—-‘ as CustPartNo,d.VRKME as SkuUM,e.MEINH as ExtUM from MAKT as a
    –inner join KNMT as b on REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(b.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MLGN as c on REPLACE(LTRIM(REPLACE(c.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MVKE as d on REPLACE(LTRIM(REPLACE(d.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join ZSD_FREIGHT_BOL as e on e.PRODH=SUBSTRING(d.PRODH,1,18)
    inner join MARM as f on REPLACE(LTRIM(REPLACE(f.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    where a.SPRAS=’E’ and REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material
    end
    end
    else
    begin
    if exists(select KUNNR from KNMT where cast(cast(KUNNR as int) as varchar(20))=@CustCode and REPLACE(LTRIM(REPLACE(MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material)
    begin
    select distinct(c.BSSKZ) as NominalVar,REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′) as MaterialNo,a.MAKTX as MaterialDesc,
    b.KDMAT as CustPartNo,M.MEINS as SkuUM,e.MEINH as ExtUM from MAKT as a
    inner join KNMT as b on REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(b.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MLGN as c on REPLACE(LTRIM(REPLACE(c.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MVKE as d on REPLACE(LTRIM(REPLACE(d.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join ZSD_FREIGHT_BOL as e on e.PRODH=SUBSTRING(d.PRODH,1,18)
    inner join MARM as f on REPLACE(LTRIM(REPLACE(f.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MARA as M on REPLACE(LTRIM(REPLACE(M.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    where a.SPRAS=’E’ and CAST(CAST(b.KUNNR as int) as varchar(20))=@CustCode and REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=@Material
    end
    else
    begin
    select distinct(c.BSSKZ) as NominalVar,REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0’) as MaterialNo,a.MAKTX as MaterialDesc,
    ‘—-‘ as CustPartNo,M.MEINS as SkuUM,e.MEINH as ExtUM from MAKT as a
    –inner join KNMT as b on REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(b.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MLGN as c on REPLACE(LTRIM(REPLACE(c.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MVKE as d on REPLACE(LTRIM(REPLACE(d.MATNR,’0′,’ ‘)),’ ‘,’0′)=REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join ZSD_FREIGHT_BOL as e on e.PRODH=SUBSTRING(d.PRODH,1,18)
    inner join MARM as f on REPLACE(LTRIM(REPLACE(f.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    inner join MARA as M on REPLACE(LTRIM(REPLACE(M.MATNR,’0′,’ ‘)),’ ‘,’0′)= REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0′)
    where a.SPRAS=’E’ and REPLACE(LTRIM(REPLACE(a.MATNR,’0′,’ ‘)),’ ‘,’0’)=@Material
    end
    end
    end
    ____________________________-

    Any one Please help me to simplify this query and i joined more than 6 tables it will take more time to execute the process. Anyone Please help me to favour……..

    Thanks in advance

    Reply
  • I do agree with you that we need to use not in instead left join yet Sometime I came across situation where not in gives incorrect result I prefer to use not exist construct as it works as per one can think.
    Further left join give correct result in case if you use not in and inner query column contain a null then we doesn’t get any result in outer query.

    Reply
  • Spain is different...
    January 18, 2012 3:36 pm

    Very very good !!!!

    It helps me alot, the answer to your cuestion is: “depens of the amount of data” in my case I compare two tables with some hundreds of million of records…. if is your case, JOIN must be your choice.

    Reply
  • Hi,

    Just curious is find out how would one optimize this query then:-

    Update INVOICE_MASTER
    SET DISCOUNT_AMOUNT = 0 , DISCOUNT_PERCENTAGE = 0
    where TENDERED in (2,4)
    and SALES_TYPE = ‘Cash’
    and INVOICE_NO not in (select INVOICE_NO from INVOICE_TENDERS where TENDER_TYPE = ‘DISCOUNT’)

    Many Thanks in advance

    Reply

Leave a Reply

Menu