- 3,728,700 Users
- 2,245,675 Discussions
- 7,853,706 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.6K All Categories
- 2.1K Data
- 202 Big Data Appliance
- 1.9K Data Science
- 445.7K Databases
- 220.3K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 499 MySQL Community Space
- 458 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 427 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.2K SQL & PL/SQL
- 20.7K SQL Developer
- 290.9K Development
- 5 Developer Projects
- 114 Programming Languages
- 287.6K Development Tools
- 94 DevOps
- 3K QA/Testing
- 645.1K Java
- 16 Java Learning Subscription
- 36.9K Database Connectivity
- 145 Java Community Process
- 103 Java 25
- 22.1K Java APIs
- 137.6K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 11 Java Essentials
- 135 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 193 Java User Groups
- 22 JavaScript - Nashorn
- Programs
- 161 LiveLabs
- 33 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
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
-
Every row in table EMP is joined with table DEPT rows where table DEPT column DEPTNO is greater than column DEPTNO in table EMP. For example, table EMP row for employee CLARK who works in DEPTN=10 will be joined with table DEPT rows where DEPTNO is 20, 30, 40. Now if table EMP would have rows with DEPTNO=40 such rows wouldn't be selected since there are no rows in table DEPT where DEPTNO>40.
SY.
-
It makes a lot of sense when looking at the results in the following format:
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4 rows selected. SQL> break on ENAME skip 1 SQL> select 2 e.ename, e.deptno, d.deptno 3 from emp e, dept d 4 where e.deptno > d.deptno 5 order by 1,2,3; ENAME DEPTNO DEPTNO ---------- ---------- ---------- ADAMS 20 10 ALLEN 30 10 30 20 BLAKE 30 10 30 20 FORD 20 10 JAMES 30 10 30 20 JONES 20 10 MARTIN 30 10 30 20 SCOTT 20 10 SMITH 20 10 TURNER 30 10 30 20 WARD 30 10 30 20 17 rows selected.
Essentially a join of an EMP row with all DEPT rows smaller than the employee's dept.
Answers
-
Every row in table EMP is joined with table DEPT rows where table DEPT column DEPTNO is greater than column DEPTNO in table EMP. For example, table EMP row for employee CLARK who works in DEPTN=10 will be joined with table DEPT rows where DEPTNO is 20, 30, 40. Now if table EMP would have rows with DEPTNO=40 such rows wouldn't be selected since there are no rows in table DEPT where DEPTNO>40.
SY.
-
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.
-
It makes a lot of sense when looking at the results in the following format:
SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4 rows selected. SQL> break on ENAME skip 1 SQL> select 2 e.ename, e.deptno, d.deptno 3 from emp e, dept d 4 where e.deptno > d.deptno 5 order by 1,2,3; ENAME DEPTNO DEPTNO ---------- ---------- ---------- ADAMS 20 10 ALLEN 30 10 30 20 BLAKE 30 10 30 20 FORD 20 10 JAMES 30 10 30 20 JONES 20 10 MARTIN 30 10 30 20 SCOTT 20 10 SMITH 20 10 TURNER 30 10 30 20 WARD 30 10 30 20 17 rows selected.
Essentially a join of an EMP row with all DEPT rows smaller than the employee's dept.
-
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.
