Oracle_sql92_ Connection Query

Oracle_sql92_ connection Query

Cartesian product

-- Cartesian product Select * from emp; ---- Select * from dept; ----4 Select * from emp,dept; ----

Equivalent connection

-- equivalent connection (equivalent connection with columns that exist in both tables) Select * from emp, dept where emp.deptno = dept.deptno;  --Query employee's name, salary, department number, department nameSelect ename, sal, emp.deptno, dname from emp,dept where emp.deptno = dept.deptno;   -- alias The table (cannot add as), you can not use the original name Select ename, sal, e.deptno, dname from emp e, dept D where e.deptno = d.deptno;

Non-equivalent connection

--Non-equivalent connections (non-equivalent connections with columns that exist in both tables)--Check employee's name, position, salary, salary levelSelect e.ename, e.job, e.sal, s.grade from emp e, salgrade s /c0>where e.sal>=s.losal and e.sal<=s.hisal;   Select e.ename, e.job, e.sal, s.grade from emp e, salgrade s /c0>where e.sal between s.losal and s.hisal;   --Query employee's number, name, department name, salary level (using equivalent connections and non-equivalent connections)Select e.empno, e.ename, d.dname, s.grade from emp e, dept D , salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

Self-connect

-- self-connected --Query the employee's number, name, leader's number, leader's nameSelect e1.empno, e1.ename, e1.mgr, e2.ename from EMP E1,EMP E2 where e1.mgr = e2.empno;

Left outer connection/right outer connection

Use an outer join to see a record of a party participating in a connection that does not meet the join condition, not just the data that satisfies the join condition Select * from emp; Select e1.empno, e1.ename, e1.mgr, e2.ename from EMP E1, EMP E2 where e1.mgr = e2.empno(+); -- left outer JOIN, showing all rows on the left table   Select e1.empno, e1.ename, e1.mgr, e2.ename from EMP E1, EMP E2 where e1.mgr(+) = e2.empno; -- right outer join, display all rows of the right table

Oracle_sql92_ Connection Query