This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,901 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

How to identify correlated subquery and what is the replacement of it

Bikram
Bikram Member Posts: 33 Green Ribbon

Hi Team,

I'm trying to explore a correlated subquery, however, it's a little bit confusing for me to recognize a correlated subquery.

I also like to know the alternative to the Correlated subquery.

It would be better if I got an answer with some example


Thanks,

Bikram

Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    Hi, @Bikram

    I'm trying to explore a correlated subquery,

    Post the complete query you're trying to explore, along with a little sample data (CREATE TABLE and INSERT statements) for all the tables involved.

    A correlated sub-query is any query that references a table that is not in its FROM clause. For example: the following query is one way to get information about departments that have at least one employee with a salary of 3000 or more.

    SELECT    d.*
    FROM	  scott.dept  d
    WHERE	  EXISTS (	-- Begin sub-query
    	  	     SELECT  0
    		     FROM    scott.emp  e
    		     WHERE   e.deptno  =  d.deptno
    		     AND     e.sal     >= 3000
    	  	 )	-- End sub-query
    ORDER BY  d.deptno
    ;
    

    In the FROM clause of the sub-query, the only table is scott.emp (with e as its alias). Notice that the sub-query references a column from another table (with d as its alias). Therefore, the sub-query is correlated. If a sub-query uses a table alias that is not defined in the same sub-query, then it is correlated.

    However, if a sub-query does NOT use a table alias that is not defined in the same sub-query, then it is NOT necessarily uncorrelated, because columns are not always qualified with an alias. If you don't know all the columns in all the tables you're using, then you can't always be sure if a sub-query is correlated or not. This is one of the many reasons why you should use table aliases on all columns in a statement that uses multiple tables.

    I also like to know the alternative to the Correlated subquery.

    An uncorrelated sub-query is one alternative. JOIN is another. Sometimes, a corelated sub-query can be replaced with analytic functions.

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Nov 19, 2022 3:10PM Answer ✓

    I also like to know the alternative to the Correlated subquery.

    Here are four different alternatives to using a correlated sub-query. All of the queries below display the deptno, ename and sal of the employee(s) in each department with the highest sal.

    PROMPT	==========  Correlated Sub-Query  ==========
    
    SELECT    deptno, ename, sal
    FROM	  scott.emp  m				-- M is for Main query
    WHERE	  NOT EXISTS (
    	      	         SELECT  0
    			 FROM	 scott.emp  s	-- S is for Sub-query
    			 WHERE	 s.deptno  = m.deptno
    			 AND	 s.sal	   > m.sal
    	      	     )
    ORDER BY  deptno, ename
    ;
    
    
    PROMPT	==========  Un-Correlated Sub-Query  ==========
    
    SELECT    deptno, ename, sal
    FROM	  scott.emp
    WHERE	  (deptno, sal)  IN  (
    	      	                 SELECT    deptno, MAX (sal)
    			     	 FROM	   scott.emp
    			     	 GROUP BY  deptno
    	      	     	     )
    ORDER BY  deptno, ename
    ;
    
    
    PROMPT	==========  JOIN  ==========
    
    SELECT    l.deptno, l.ename, l.sal
    FROM	  scott.emp  l  -- L is for Lower sal
    LEFT JOIN scott.emp  h  -- H is for Higher sal
         	  	        ON   h.deptno  = l.deptno
    			AND  h.sal     > l.sal
    WHERE     h.deptno  IS NULL
    ORDER BY  l.deptno, l.ename
    ;
    
    
    PROMPT  ==========  Analytic Function ==========
    
    WITH    got_max_sal    AS
    (
    	SELECT  deptno, ename, sal
    	,	MAX (sal) OVER (PARTITION BY deptno)	AS max_sal
    	FROM	scott.emp
    )
    SELECT    deptno, ename, sal
    FROM	  got_max_sal
    WHERE	  sal  = max_sal
    ORDER BY  deptno, ename
    ;
    
    
    PROMPT	=========  MATCH_RECOGNIZE  ==========
    
    SELECT    deptno, ename, sal
    FROM	  scott.emp
    MATCH_RECOGNIZE
              (
    	      PARTITION BY  deptno
    	      ORDER BY	    sal  DESC
    	      ALL ROWS PER MATCH
    	      PATTERN       ( ^ top_sal + )
    	      DEFINE   	    top_sal 	 AS  sal  = FIRST (sal)
    	  )
    ORDER BY  deptno, ename
    ;
    

    I'm assuming that deptno and sal cannot be NULL. All of the queries above get the same output, that is:

        DEPTNO ENAME             SAL
    ---------- ---------- ----------
            10 KING             5000
            20 FORD             3000
            20 SCOTT            3000
            30 BLAKE            2850
    


    _jum

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    Hi, @Bikram

    I'm trying to explore a correlated subquery,

    Post the complete query you're trying to explore, along with a little sample data (CREATE TABLE and INSERT statements) for all the tables involved.

    A correlated sub-query is any query that references a table that is not in its FROM clause. For example: the following query is one way to get information about departments that have at least one employee with a salary of 3000 or more.

    SELECT    d.*
    FROM	  scott.dept  d
    WHERE	  EXISTS (	-- Begin sub-query
    	  	     SELECT  0
    		     FROM    scott.emp  e
    		     WHERE   e.deptno  =  d.deptno
    		     AND     e.sal     >= 3000
    	  	 )	-- End sub-query
    ORDER BY  d.deptno
    ;
    

    In the FROM clause of the sub-query, the only table is scott.emp (with e as its alias). Notice that the sub-query references a column from another table (with d as its alias). Therefore, the sub-query is correlated. If a sub-query uses a table alias that is not defined in the same sub-query, then it is correlated.

    However, if a sub-query does NOT use a table alias that is not defined in the same sub-query, then it is NOT necessarily uncorrelated, because columns are not always qualified with an alias. If you don't know all the columns in all the tables you're using, then you can't always be sure if a sub-query is correlated or not. This is one of the many reasons why you should use table aliases on all columns in a statement that uses multiple tables.

    I also like to know the alternative to the Correlated subquery.

    An uncorrelated sub-query is one alternative. JOIN is another. Sometimes, a corelated sub-query can be replaced with analytic functions.

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Nov 19, 2022 3:10PM Answer ✓

    I also like to know the alternative to the Correlated subquery.

    Here are four different alternatives to using a correlated sub-query. All of the queries below display the deptno, ename and sal of the employee(s) in each department with the highest sal.

    PROMPT	==========  Correlated Sub-Query  ==========
    
    SELECT    deptno, ename, sal
    FROM	  scott.emp  m				-- M is for Main query
    WHERE	  NOT EXISTS (
    	      	         SELECT  0
    			 FROM	 scott.emp  s	-- S is for Sub-query
    			 WHERE	 s.deptno  = m.deptno
    			 AND	 s.sal	   > m.sal
    	      	     )
    ORDER BY  deptno, ename
    ;
    
    
    PROMPT	==========  Un-Correlated Sub-Query  ==========
    
    SELECT    deptno, ename, sal
    FROM	  scott.emp
    WHERE	  (deptno, sal)  IN  (
    	      	                 SELECT    deptno, MAX (sal)
    			     	 FROM	   scott.emp
    			     	 GROUP BY  deptno
    	      	     	     )
    ORDER BY  deptno, ename
    ;
    
    
    PROMPT	==========  JOIN  ==========
    
    SELECT    l.deptno, l.ename, l.sal
    FROM	  scott.emp  l  -- L is for Lower sal
    LEFT JOIN scott.emp  h  -- H is for Higher sal
         	  	        ON   h.deptno  = l.deptno
    			AND  h.sal     > l.sal
    WHERE     h.deptno  IS NULL
    ORDER BY  l.deptno, l.ename
    ;
    
    
    PROMPT  ==========  Analytic Function ==========
    
    WITH    got_max_sal    AS
    (
    	SELECT  deptno, ename, sal
    	,	MAX (sal) OVER (PARTITION BY deptno)	AS max_sal
    	FROM	scott.emp
    )
    SELECT    deptno, ename, sal
    FROM	  got_max_sal
    WHERE	  sal  = max_sal
    ORDER BY  deptno, ename
    ;
    
    
    PROMPT	=========  MATCH_RECOGNIZE  ==========
    
    SELECT    deptno, ename, sal
    FROM	  scott.emp
    MATCH_RECOGNIZE
              (
    	      PARTITION BY  deptno
    	      ORDER BY	    sal  DESC
    	      ALL ROWS PER MATCH
    	      PATTERN       ( ^ top_sal + )
    	      DEFINE   	    top_sal 	 AS  sal  = FIRST (sal)
    	  )
    ORDER BY  deptno, ename
    ;
    

    I'm assuming that deptno and sal cannot be NULL. All of the queries above get the same output, that is:

        DEPTNO ENAME             SAL
    ---------- ---------- ----------
            10 KING             5000
            20 FORD             3000
            20 SCOTT            3000
            30 BLAKE            2850
    


    _jum
  • Bikram
    Bikram Member Posts: 33 Green Ribbon

    @Frank Kulash I really appreciate your effort for explaining the concepts and putting all the examples.