Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Thumbs down Unanswered: just answer plz.....

    Hi ,

    I've two tables (myEMP and myDEPT)which are related each other by a particular column(DEPT).


    SELECT EMPID,NAME,DEPTID
    FROM MYEMP,MYDEPT
    WHERE MYEMP.DEPT = MYDEPT.DEPTID;

    myEMP table has more rows than myDEPT table.

    My doubt is if im using a query like this which table i should give in the left side of WHERE condition?
    I tested by giving both tables in both sides(left and right).
    Output is same.

    But i wanna know is there any rule like we should give the table which has more rows in LEFT side or not.

    Thanx in advance. Answers will be always appreciable.

  2. #2
    Join Date
    Nov 2003
    Posts
    19
    Put outer join to the table that has more rows

    SELECT EMPID,NAME,DEPTID
    FROM MYEMP,MYDEPT
    WHERE MYEMP.DEPT(+) = MYDEPT.DEPTID;

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Originally posted by agstamy
    Put outer join to the table that has more rows

    SELECT EMPID,NAME,DEPTID
    FROM MYEMP,MYDEPT
    WHERE MYEMP.DEPT(+) = MYDEPT.DEPTID;
    What are you trying to accomplish? It does not matter which side you put them on assuming you are using 8 or above. Otherwise I believe you want the larger on the right. The output is not the important thing, it is the timing (if efficiency is your concern). If you want to show all the rows in the larger file plus the matches from the smaller then the outer join is what you need.

  4. #4
    Join Date
    Oct 2003
    Posts
    87

    Re: just answer plz.....

    Originally posted by raseena
    Hi ,

    I've two tables (myEMP and myDEPT)which are related each other by a particular column(DEPT).


    SELECT EMPID,NAME,DEPTID
    FROM MYEMP,MYDEPT
    WHERE MYEMP.DEPT = MYDEPT.DEPTID;

    myEMP table has more rows than myDEPT table.

    My doubt is if im using a query like this which table i should give in the left side of WHERE condition?
    I tested by giving both tables in both sides(left and right).
    Output is same.

    But i wanna know is there any rule like we should give the table which has more rows in LEFT side or not.

    Thanx in advance. Answers will be always appreciable.
    Also, you've a normalization problem. Might it be that some emps don't work in a dept, or haven't yet been assigned to a dept? Might it be that some emps work in two or more depts? You need an association between emp and dept.

    emp(empID PK, name)
    dept(deptID PK, name)
    empDept(empID PK, deptID PK)

    select empID, deptID
    from emp, empDept
    where emp.empID(+) = empDept.empID
    Oracle - DB2 - MS Access -

  5. #5
    Join Date
    Nov 2003
    Posts
    87

    Talking

    Thanx for all anserws.

    I understood that where to use OUTER join and all, but still....

    Let me explain u the situation made me to ask that question.
    When i wrote that query, i submitted to my boss and he asked me which table name will you give on the left side of WHERE condition, that iz whether the table which has more rows or less rows( not considering how i need the output).

    Now anybody can answer me ??
    Or if my question is still not clear , just leav it.

    Thanx.

  6. #6
    Join Date
    Dec 2003
    Location
    Mumbai
    Posts
    7
    Originally posted by raseena
    Thanx for all anserws.

    I understood that where to use OUTER join and all, but still....

    Let me explain u the situation made me to ask that question.
    When i wrote that query, i submitted to my boss and he asked me which table name will you give on the left side of WHERE condition, that iz whether the table which has more rows or less rows( not considering how i need the output).

    Now anybody can answer me ??
    Or if my question is still not clear , just leav it.

    Thanx.
    It dose not matter you put the table having more rows to left or right
    what matters is that you should put the outer joining condition accordingly. A left outer join preserves every row in the left-hand table, and a right outer join preserves every row in the right-hand table. In a full outer join, all rows from both tables are preserved.

    I hope this answer will satisfy you.

  7. #7
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    The order of the table does not matter in the WHARE CLAUSE. It matters in the FROM CLAUSE if you have OPTIMIZER_MODE set to RULE. The driving table must be smaller SO it must be LEFT MOST i.e, right after the FROM clause. (tables in increaseing orders in FROM CLAUSE)

    WHERE CLAUSE only restrics the rows so IF you have more then one conditions in the WHERE CLAUSE then put the MOST RESTRICTIVE condition (i.e, the one returns the smallest rows) at the bottom as ORACLE parses WHERE CLAUSE from the bottom.

    HTH

    From More information read orcel documents from SQL tuning.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  8. #8
    Join Date
    Nov 2003
    Posts
    87

    Talking

    ThnQ.

  9. #9
    Join Date
    Sep 2003
    Posts
    156

    Re: just answer plz.....

    Originally posted by raseena
    Hi ,

    I've two tables (myEMP and myDEPT)which are related each other by a particular column(DEPT).


    SELECT EMPID,NAME,DEPTID
    FROM MYEMP,MYDEPT
    WHERE MYEMP.DEPT = MYDEPT.DEPTID;

    myEMP table has more rows than myDEPT table.

    My doubt is if im using a query like this which table i should give in the left side of WHERE condition?
    I tested by giving both tables in both sides(left and right).
    Output is same.

    But i wanna know is there any rule like we should give the table which has more rows in LEFT side or not.

    Thanx in advance. Answers will be always appreciable.
    SELECT e.EMPID,e.NAME,e.DEPTID
    FROM MYEMP e,MYDEPT d
    WHERE m.DEPT = d..DEPTID;

    it doesnt make any difference what side it is on.
    rgs,

    Ghostman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •