how does the where clause work here? — oracle-tech

    Forum Stats

  • 3,715,600 Users
  • 2,242,807 Discussions
  • 7,845,456 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

how does the where clause work here?

SELECT ename, e.deptno, dname, loc

FROM emp e, dept d

WHERE e.deptno > d.deptno;

Best Answers

Answers

  • Paulzip
    Paulzip Member Posts: 8,083 Gold Crown

    No idea why you would do this on emp and dept, but this is called a non-equijoin. A non-equijoin is a join which matches column values from different tables based on an inequality expression (instead of the usual equal sign). So something like >, <, >=, <=.

    The value of the join column in each row in the source table is compared to the corresponding values in the target table. A match is found if the expression based on an inequality operator used in the join, evaluates to true. So in your case, it evaluates to true for all cases where the emp.deptno is greater than a department no in the dept table. When it's true, it returns the values from the tables you specified.

    User_68DIP
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 39,966 Red Diamond

    Hi,

    If you didn't have the join condition

    SELECT ename, e.deptno, dname, loc
    FROM emp e, dept d
    -- WHERE e.deptno > d.deptno
    ;
    

    then the results would contain all possible combinations. Every row in table e would be joined to every row in table d.

    Including the join condition means you only get a sub-set of those output rows. The only rows included will be the rows where the condition

    WHERE e.deptno > d.deptno
    

    is true.

    User_68DIP
Sign In or Register to comment.