1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How Query result changes based on Joins.?~!

Discussion in 'SQL PL/SQL' started by Vicky, May 8, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi frndz.,

    Cud U tel me how these 2 queries differs in result
    And particularly how the 2nd query works./!



    Code (SQL):


    SELECT E.SALARY FROM HR.EMPLOYEES E
    WHERE E.SALARY = (SELECT MIN(SALARY) FROM HR.EMPLOYEES D);




      SALARY
    --------
        2100


     
    and...


    Code (SQL):



    SELECT E.SALARY FROM HR.EMPLOYEES E
    WHERE E.SALARY = (SELECT MIN(SALARY) FROM HR.EMPLOYEES D
    WHERE E.SALARY = D.SALARY AND E.FIRST_NAME = D.FIRST_NAME AND E.EMPLOYEE_ID = D.EMPLOYEE_ID);



     SALARY
    --------
       17000
        6000
        4800
        7700
        2700
        7200
        6400
       11500
        8400
        4100
        4000
        8200
        2200
        2500
       14000
       11000
        7000
        9000
        8800
        3200
        2600
       12000
        8300
       12000
        2900
        2600
        2700
        2200
        3200
        2500
       13500
        7000
        9500
        6800
        7400
        7300
        3600
        2600
        4400
        9000
        2500
        7900
        5800
        3200
        2800
       10000
        9500
        6200
        2800
        3200
       10000
        9000
        7800
        6900
        3100
        2800
        8000
        2400
        3300
        3500
       10000
        6100
        4200
        2900
        3100
       13000
        6000
       17000
        4200
        6500
        2400
        3600
       10500
        9500
       10500
        9600
        8600
        7000
        3900
        3000
        6500
       24000
        4800
        2100
        3300
        2900
        2600
       12000
        8000
        7500
       11000
        2500
        8200
       11000
        2500
        3100
        9000
       10000
        8000
        7500
        6200
        3100
        2500
        3400
        3000
        3800
        2800

     107 ROWS selected


     
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    In the first query, the subquery will always return a single salary value -- 2100 in this case. The inner query will then return a row for every employee with a salary of 2100. Apparently there is only one employee with that salary.

    In the second query, the subquery is correlated to the outer query. For each row in the table accessed by the outer/main query, the inner/sub query runs once and locates the minimum salary for the employee record of the outer query. Since the EMPLOYEE_ID *should* be unique (unless you've been altering the sample schema data), the minimum salary for each EMPLOYEE_ID should equal the value of the record being evaluated. Put another way, the WHERE condition for the outer query will evaluate to true for every record in HR.EMPLOYEES.
     
    Vicky likes this.