SQL SERVER – 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 query? 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.

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.

You can clearly observe that first query with NOT IN takes 20% resources of 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 (http://blog.SQLAuthority.com)

About these ads

62 thoughts on “SQL SERVER – Better Performance – LEFT JOIN or NOT IN?

  1. Pingback: SQL SERVER - Converting Subqueries to Joins Journey to SQL Authority with Pinal Dave

    • It looks like LEFT JOIN gives more accurate results than NOT IN function.

      This query returned 0 records.

      SELECT DISTINCT(A.CSN_ID) FROM TRG_LINKED_ENCS A
      WHERE A.CSN_ID not in (SELECT DISTINCT(CSN_ID) FROM
      TPL_TXDAYS )

      This query retured 8700 records.

      SELECT A.CSN_ID FROM TRG_LINKED_ENCS A
      LEFT JOIN TPL_TXDAYS B ON
      A.CSN_ID =B.CSN_ID
      WHERE B.CSN_ID IS NULL

      • The table TPL_TXDAYS must be having records with CSN_ID as null
        SELECT DISTINCT(A.CSN_ID) FROM TRG_LINKED_ENCS A
        WHERE A.CSN_ID not in (SELECT DISTINCT(CSN_ID) FROM
        TPL_TXDAYS where CSN_ID is not null )

  2. Hi!
    It is bit hard to guess which one is actually most efficient.

    If you turn on the STATISTICS IO and check messages, you’ll notice that the NOT IN -query accesses significantly more data pages with more scans:

    NOT IN:
    Table ‘WorkOrder’. Scan count 504, logical reads 1097, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘Product’. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    LEFT JOIN:
    Table ‘WorkOrder’. Scan count 1, logical reads 101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table ‘Product’. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Compare especially the Scan Count and Logical Reads -values.

    Also, I tried to get the same results with using EXISTS and Query Optimizer produced exactly the same Execution Plan as for the NOT IN -query:
    SELECT ProductID
    FROM Production.Product p
    WHERE NOT EXISTS
    (SELECT ProductID
    FROM Production.WorkOrder w
    WHERE p.ProductID = w.ProductID);

    As EXISTS -query usually outperforms JOINs, the worrying STATISTICS IO -results probably tells about badly optimized indexes for this kind of query.

    Like you said: it depends on the database structure and amount of data. I also recommend not to stare only at the Execution Plans as they don’t always tell the whole truth.

  3. Hmm. I didn’t really get anything out of that. Percentages are useful but what were the overall timings. 20% of 10s against 80% of one second or the other way around?

    What are the effects at different data sizes?

    More information is definitely needed before any conclusion can be made.

  4. 20% and 80% of the complete batch.

    But what about this:

    SELECT p.ProductID
    FROM Production.Product p
    LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID
    AND w.ProductID IS NULL;

  5. Looking at the example it would seem the results would vary based on the number of rows in the WordOrder table and what indexes are set up.
    If ProductID is indexed wouldn’t this be a seek operation while the IN statement is reading the entire table?

  6. Always use SET STATISTICS IO ON. The execution plan shows an “estimate” of cpu usage and those numbers are completely worthless since IO rules all.

    Although the LEFT JOIN may be more processor intensive it is significantly less IO expensive which is the far more important measure.

    The only time to use a subquery and play that little game of “beat the optimizer” is when the tables have normalization or index issues that you have no control over.

  7. Usually it is better to avoid correlated subselects. Looking only at only resources used is a pretty worthless method of evaluating optimization methods. The goal of optimization is to reduce run-time while still giving the correct answer.

  8. I ran this select,
    USE ttst
    GO
    SET SHOWPLAN_ALL ON
    GO
    select * from customer
    where c.record_type = ‘T’
    and c.customer_class_code = ‘LOCAL’
    and c.customer_status_code = ‘ACTIVE’
    SET SHOWPLAN_ALL OFF
    GO
    it gave me a error like below,
    Server: Msg 1067, Level 15, State 1, Line 5
    The SET SHOWPLAN statements must be the only statements in the batch.

    (1 row(s) affected)
    What is the correct way to use the set showplan_all on?

    Jchen

  9. –List names of authors who have contributed in
    –any book title
    SELECT DISTINCT authors.au_id,au_fname
    FROM authors,titleauthor
    WHERE authors.au_id!=titleauthor.au_id

    SELECT au_id, au_fname FROM authors WHERE authors.au_id NOT IN
    (SELECT au_id FROM titleauthor)

    why the above query did not work as same as the below query??
    the above on shows more records then the following.
    why we did not do the same work with JOIN as with NOT IN??

  10. hi Pinal,

    I want to retrieve only limited data from database like limit is keyword in mysql which retrieve limited data according to our arguments. Like I want to load only 10 records from the database at first display in page. but I don’t want to using top. Because its create problem of paging. So please give me alternate solution for that’d have use row index but in that row index I have to write inner query and my data base I s to heavy.

  11. The difference is obvious in the query plans, the Join query is not using the index. You could alter the indices or the query to take advantage of the indices. Also, when you look at the statistics output you can see that the Join query is doing much better than the Not In query based on reads and scans, which in a high volume environment will be a much better indication of performance anyway. My opinion, the percentages shown on query plans are useless, use query plans only to figure out what indices you’re not taking advantage of (i.e. look for table and index scans).

  12. 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

  13. 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

  14. 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

  15. 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)

    • @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.

  16. 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

    • 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

  17. 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)

  18. 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.

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

  20. ‘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,”)=”

  21. 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)))

    • 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. :)

  22. 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

  23. 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.

  24. 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.

  25. 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

  26. can anyone explain me the case when should i use which join.Because most of the time i did normal joins using where clauses so i am not use to any other kind of join

  27. @Dhiraj – your question is confusing. Maybe this will help you:

    Use an INNER JOIN when you want only records that are related in both tables.
    Use a LEFT JOIN when you want all records in the left table.
    Use a RIGHT JOIN when you want all records in the right table.

    Personally, I never use RIGHT JOIN. I’ve written thousands of queries with just INNER or LEFT.

    Hope this helps.

  28. HI pinal, SQL guru, i am your lovable fan in SQL sever 2005
    Can you answer or suggest my question?
    is Any Alter Way of Leftouter join method Available in Sequel?
    Can anyone give me the method of alter way of left outer join Because when we create a indexed view we could not able create index on theat view due to statement contains FULL JOIN or LEFT JOIN or RIGHT JOIN. So i searched the web fully it gives suggestion using UNION Instead of left join,I mentioned query below. So could you give me any other method is available to replace LEFT JOIN with that other than UNION? Is it full to reduce optimization cost when we use UNION instead of LEFT JOIN? Quick suggestions are highly appreciated………….buddys………

    ———————-My original table using left join method—————–

    SELECT tablea.*,
    CASE
    WHEN tableb.tablebcolumn1 IS NULL THEN 0
    ELSE 1
    END IsHosted
    FROM tablea
    LEFT JOIN tableb ON tablea.column=tableb.column

    ———–Example of alter way of using UNIONALL instead of leftouterjoin ———

    SELECT tablea.,
    CASE
    WHEN tableb.tablebcolumn1 IS NULL THEN 0
    ELSE 1
    END IsHosted
    FROM tablea where tablea.column IN (select tableb.column from tableb) UNION ALL SELECT tablea.,
    CASE
    WHEN tableb.tablebcolumn1 IS NULL THEN 0
    ELSE 1
    END IsHosted
    FROM tablea where tablea.column NOT IN (select tableb.column from tableb)

    ————————————————————————————– Note: Due to security reason i did not give original table name. i gave table name of tablea and tableb………as my originaltable name

  29. I am just curious why sometimes ‘IN’ and ‘NOT IN’ give different result than ‘JOIN’

    Sometimes, using IN/NOT IN with the same table work but doesn’t with another table (same table structure)

    select email from [temp].[dbo].SETA where email not in (select email from [temp].[dbo].SETA) — OK

    select email from [temp].[dbo].SETA where email not in (select email from [temp].[dbo].SETB) — return nothing both ‘not in’ and ‘in’

  30. Hi, I am Meenakshi, I have one query
    How to write the query for having more than 1000 static values in IN clause?
    Or any alternative solution to IN clause?

    • In this scenario, I would import the list into a sql table using the Import Wizard, then use an inner join on the table that contains the data. If you need help writing the query you can contact me.

  31. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  32. Hello Panil, one of my query is taking long time to execute.. Can you think of other way to rewrite this query? It used to take like 4 min now it is taking 20 min.. some service pack and indexes were installed on the server and since then we are having this issue…

    SELECT * From #Temp Where ID not in
    (Select CODE from Event where #Temp.ID = Event.ID AND #Temp.Class = Event.Class)

  33. Hello Panil, I have a query, i tried a lot but couldn’t findout the results. Could you please help me on this query. Need UnMatched Locations from Table1 and Table2, But ID should be matched
    Create table #t1(T1ID int,T1Type varchar(10) null,T1Locations varchar(10) null)

    Create table #t2(T2ID int,T2Type varchar(10) null,T2Locations varchar(10) null)

    Insert into #t1 select 101,’Primary’,’HYD’
    Insert into #t1 select 101,’Secondary’,’Calcutta’
    Insert into #t1 select 101,’Secondary’,’Mumbai’
    Insert into #t1 select 102,’Secondary’,’Calcutta’
    Insert into #t1 select 102,’Secondary’,’Banglore’
    Insert into #t1 select 103,’Secondary’,’Delhi’
    Insert into #t1 select 103,’Secondary’,’Mumbai’
    Insert into #t1 select 105,’Secondary’,’Delhi’

    Insert into #t2 select 101,’Primary’,’HYD’
    Insert into #t2 select 101,’Secondary’,’Calcutta’
    Insert into #t2 select 101,’Secondary’,’Banglore’
    Insert into #t2 select 102,’Secondary’,’Banglore’
    Insert into #t2 select 102,’Secondary’,’Delhi’
    Insert into #t2 select 103,’Primary’,’HYD’
    Insert into #t2 select 103,’Secondary’,’Delhi’
    Insert into #t2 select 104,’Secondary’,’Delhi’

    Result should be (Matched with ID but Locations are unmatched)
    101,’Secondary’,’Mumbai’
    101,’Secondary’,’Banglore’
    102,’Secondary’,’Calcutta’
    102,’Secondary’,’Delhi’
    103,’Secondary’,’Mumbai’
    103,’Primary’,’HYD’

    Appreciate your Help.
    Thank you,
    Kranti

  34. If you have a lot of data in the table Production.WorkOrder, the left join query will be the fastest. Only because the seek in Production.WorkOrder take 93% compare to 42% in the the join case.

  35. Works great. I was joining between a daily transaction table with 30 million rows and a master table to check if there are any entries.IN operator took quite a long time to execute , but left join ran in just 2 mins.
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s