locked
Query question RRS feed

  • Question

  • Hello,

    I've below queries,

    USE [Practice]
    GO
    
    CREATE TABLE #TEMP1
    (
    	ID		INT
    )
    
    CREATE TABLE #TEMP2
    (
    	ID		INT
    )
    
    INSERT INTO #TEMP1 VALUES ( 1 )
    INSERT INTO #TEMP1 VALUES ( 2 )
    
    INSERT INTO #TEMP2 VALUES ( 2 )
    INSERT INTO #TEMP2 VALUES ( 4 )
    
    SELECT	*
    FROM	#TEMP1
    WHERE	NOT EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP2 b
    			WHERE	ID = b.ID
    		)
    
    SELECT	a.*
    FROM	#TEMP1 a
    WHERE	NOT EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP2 b
    			WHERE	a.ID = b.ID
    		)
    
    DROP TABLE #TEMP1
    
    DROP TABLE #TEMP2
    

    Why 1st query didn't brought me any results although I specified #TEMP2 as b alias? [I was expecting results as same for both the queries]

    Please help me understand

    Thanks in advance


    Please do let us know your feedback. Thank You - KG, MCTS

    Friday, February 13, 2015 5:56 PM

Answers

  • It is going to interpret the ID with no table identifier as belong to the innermost query. 


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by HunchbackMVP Friday, February 13, 2015 6:01 PM
    • Marked as answer by gk1393 Friday, February 13, 2015 6:14 PM
    Friday, February 13, 2015 6:00 PM
  • The resolution starts from the place it is referenced going up to the most outer. The subquery always return a row, unless #TEMP2 is empty, so the NOT EXISTS fails.

    There is nothing wrong in this statement, right?

    SELECT
        *
    FROM
        (
        VALUES
            (1),(2)
        ) AS T(Col1)
    WHERE
        T.col1 = col1;
    GO

    Same is happenning in your query.

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Edited by HunchbackMVP Friday, February 13, 2015 6:08 PM
    • Marked as answer by gk1393 Friday, February 13, 2015 6:14 PM
    Friday, February 13, 2015 6:05 PM
  • INSERT INTO #TEMP1 VALUES ( 2 )
    
    INSERT INTO #TEMP2 VALUES ( 2 )
    INSERT INTO #TEMP2 VALUES ( 4 )
     
    --SELECT	*
    --FROM	#TEMP1
    --WHERE	NOT EXISTS
    --		(
    --			SELECT	1
    --			FROM	#TEMP2 b
    --			WHERE	ID = b.ID
    --		)
    
    SELECT	a.*
    FROM	#TEMP1 a
    WHERE	NOT EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP2 b
    			WHERE	a.ID = b.ID
    		)
    
    
    		SELECT	a.*
    FROM	#TEMP1 a
    WHERE	NOT EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP2  
    			WHERE	a.ID =  ID
    		)
     DROP TABLE #TEMP1, #TEMP2

    • Marked as answer by gk1393 Friday, February 13, 2015 6:14 PM
    Friday, February 13, 2015 6:06 PM

All replies

  • It is going to interpret the ID with no table identifier as belong to the innermost query. 


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by HunchbackMVP Friday, February 13, 2015 6:01 PM
    • Marked as answer by gk1393 Friday, February 13, 2015 6:14 PM
    Friday, February 13, 2015 6:00 PM
  • The resolution starts from the place it is referenced going up to the most outer. The subquery always return a row, unless #TEMP2 is empty, so the NOT EXISTS fails.

    There is nothing wrong in this statement, right?

    SELECT
        *
    FROM
        (
        VALUES
            (1),(2)
        ) AS T(Col1)
    WHERE
        T.col1 = col1;
    GO

    Same is happenning in your query.

    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas



    • Edited by HunchbackMVP Friday, February 13, 2015 6:08 PM
    • Marked as answer by gk1393 Friday, February 13, 2015 6:14 PM
    Friday, February 13, 2015 6:05 PM
  • INSERT INTO #TEMP1 VALUES ( 2 )
    
    INSERT INTO #TEMP2 VALUES ( 2 )
    INSERT INTO #TEMP2 VALUES ( 4 )
     
    --SELECT	*
    --FROM	#TEMP1
    --WHERE	NOT EXISTS
    --		(
    --			SELECT	1
    --			FROM	#TEMP2 b
    --			WHERE	ID = b.ID
    --		)
    
    SELECT	a.*
    FROM	#TEMP1 a
    WHERE	NOT EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP2 b
    			WHERE	a.ID = b.ID
    		)
    
    
    		SELECT	a.*
    FROM	#TEMP1 a
    WHERE	NOT EXISTS
    		(
    			SELECT	1
    			FROM	#TEMP2  
    			WHERE	a.ID =  ID
    		)
     DROP TABLE #TEMP1, #TEMP2

    • Marked as answer by gk1393 Friday, February 13, 2015 6:14 PM
    Friday, February 13, 2015 6:06 PM