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!

Need help in SQL Interview

Discussion in 'SQL PL/SQL' started by venkateshguru, Aug 10, 2014.

  1. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Hi Guru's,


    Below question asked by Technical Guy in interview.

    Question : What is the output of the below query.

    Answer : First Query will give the matching records available in both the table and extra records available in Emp table. Second Query will give the matching records available in both the table and extra records available in Dept table.

    Technical Guy : Then HR Asked what is the purpose of right outer join.

    SELECT EMPNO,ENAME,D.DEPTNO
    FROM EMP E , DEPT D
    WHERE E.DEPTNO=D.DEPTNO(+)

    SELECT EMPNO,ENAME,D.DEPTNO
    FROM EMP E , DEPT D
    WHERE D.DEPTNO=E.DEPTNO(+)

    I don't know whether the question is clear. Please let me know if you need any inputs.

    Thanks in advance for your help.

    Regards,
    Venkatesh.G
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    In the first query there is no purpose to the outer join as it returns all matched records between EMP and DEPT (since no record in EMP lists a department NOT in DEPT). In the second query the right outer join returns the 'unused' departments, those which have no employees associated with them.


    Change the data in EMP to include deptno values not in DEPT and the right outer join has purpose; it shows the employees associated with non-existent departments:


    Code (SQL):

    SQL>  INSERT INTO emp
      2  VALUES (8888, 'FRIMMEL','DOWSER', 7900, sysdate-10, 1250, 0, 50)
      3  /


    1 ROW created.


    SQL> SELECT EMPNO,ENAME,D.DEPTNO
      2   FROM EMP E , DEPT D
      3   WHERE E.DEPTNO=D.DEPTNO(+);


         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7869 JACK               10
          7959 QUEEN              10
          7949 PRINCE             10
          7939 DUKE               10
          7934 MILLER             10
          7839 KING               10
          7782 CLARK              10
          7902 FORD               20
          7876 ADAMS              20
          7788 SCOTT              20
          7566 JONES              20


         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7369 SMITH              20
          7900 JAMES              30
          7844 TURNER             30
          7698 BLAKE              30
          7654 MARTIN             30
          7521 WARD               30
          7499 ALLEN              30
          8888 FRIMMEL


    19 ROWS selected.


    SQL>
    SQL>  SELECT EMPNO,ENAME,D.DEPTNO
      2   FROM EMP E , DEPT D
      3   WHERE D.DEPTNO=E.DEPTNO(+);


         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7369 SMITH              20
          7499 ALLEN              30
          7521 WARD               30
          7566 JONES              20
          7654 MARTIN             30
          7698 BLAKE              30
          7782 CLARK              10
          7788 SCOTT              20
          7839 KING               10
          7844 TURNER             30
          7876 ADAMS              20


         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7900 JAMES              30
          7902 FORD               20
          7934 MILLER             10
          7939 DUKE               10
          7949 PRINCE             10
          7959 QUEEN              10
          7869 JACK               10
                                  40


    19 ROWS selected.


    SQL>
     
     
    venkateshguru likes this.
  3. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Thank you.

    In above example i have just swap where condition to get outer join of both table with left outer join condition itself.

    Then what is use of Right outer join, Instead i can swap the table name condition to get the output.

    Please suggest.................
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I gave you the answer in my previous post; 'reversing' the conditions of each query only makes query 1 into query 2 and query 2 into query 1. The purpose of the outer join does not change.