Oracle multi-Table query, oracle

Oracle multi-Table query, oracle

Basic multi-Table query syntax

The implementation of cartesian products in SQL is not only Cross Join ). All join methods are converted into temporary Cartesian Product tables. Cartesian product is a concept in relational algebra, which indicates any combination of each row of data in two tables.

-- Cartesian product select * from emp, dept;

-- Remove the Cartesian product select * from emp, dept where emp. deptno = dept. deptno using public fields;

-- Query the name, work, and immediate superior leadership of each employee -- MGR In the emp table indicates the number of the superior leadership of an employee, if you want to query the name of an employee's superior leader, you need to use the emp table and the emp table for the Self-Join Operation select e. ename, e. job, s. ename from emp s, emp e where s. empno = e. mgr; -- query the name, job, name of the employee's direct superior, and select e. ename, e. job, s. ename mgr_name, d. dname from emp e, emp s, dept d where e. mgr = s. empno and e. deptno = d. deptno; -- query the name, salary, Department name, salary level in the company, the name of the leader, the salary of the leader, and SELECT e. ename, e. sal, d. dname, decode (g. grade, 5, 'first-level wage ', 4, 'second-level wage', 3, 'third-level wage ', 2, 'Fourth-level wage', 1, 'Wait 5') e_grade, s. ename mgr_name, s. sal mgr_sal, decode (g2.grade, 5, 'First wage ', 4, 'second wage', 3, 'third wage ', 2, 'Fourth wage ', 1, 'fifth-level wage ') m_gradeFROM emp e, dept d, salgrade g, emp s, salgrade g2WHERE (e. deptno = d. deptno) AND (e. sal BETWEEN g. losal AND g. hisal) AND (s. empno = e. mgr) AND (s. sal BETWEEN g2.losal AND g2.hisal );

Left and right connections

-- Use emp and dept for a connection query. The query results include employee ID, employee name, department ID, Department name, and department location select e. empno, e. ename, d. deptno, d. dname, d. locfrom emp e, dept dwhere e. deptno = d. deptno;

-- Through observation, it is not difficult to find that the Department number 40 does not appear in the above results, because there are both sides in the results during the normal connection, and there is no Department 40 in the emp table, so there is no 40 select e in the result. empno, e. ename, d. deptno, d. dname, d. locfrom emp e, dept dwhere e. deptno (+) = d. deptno;

Department 40 is displayed in the result. In this case, we use the right connection.

(+) On the left side of = indicates the right connection.
(+) At the right of = indicates the left join

The left join uses the left table as the benchmark, and the right join uses the right table as the benchmark.

-- The left join uses the left table as the reference, and the right join uses the right table as the reference select e. empno, e. ename, d. deptno, d. dname, d. locfrom emp e, dept dwhere e. deptno = d. deptno (+ );

-- Query the employee ID, name, and leader ID, and name select e. empno, e. ename, s. empno, s. enamefrom emp e, emp swhere e. mgr = s. empno;

-- However, after query, we know that the emp table should have 14 records, and KING is missing in the above results. Because KING is the highest leader, his leader number is blank, therefore, it cannot be queried. to display the data, we use the left/right join to implement select e. empno, e. ename, s. empno, s. enamefrom emp e, emp swhere e. mgr = s. empno (+ );

-- The connection method of the left and right connections is not fixed. The left or right connections depend on the left or right of the benchmark conditions in the equation. The left connections above place the benchmark conditions on the right, to right join select e. empno, e. ename, s. empno, s. enamefrom emp e, emp swhere s. empno (+) = e. mgr;

We get the same result value. We will not repeat the texture here.

SQL: 1999SQL Definition

Syntax format

SELECT table1.column, table2.column

FROM table1 [cross join table2] |

[Natural join table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2 ON (table1.column _ name = table2.column _ name)] |

[LEFT | RIGHT | full outer join table2 ON (table1.column _ name = table2.column _ name)]

-- Cross join: select * from emp cross join dept;

select * from emp cross join dept where emp.empno = 7369;

-- Naturally connect NATURAL JOINselect * from emp natural join dept;

-- According to the results, we can find that the natural connections are connected by equal public fields, and the results will automatically remove repeated columns select * from emp e, dept dwhere e. deptno = d. deptno;

-- USING (column_name) is used to specify the join field between two tables select * from emp join dept using (deptno );

-- ON (tab1.column _ name = tab2.column _ name) is used to specify the join condition select * from emp join dept on emp. deptno = dept. deptno;

-- LEFT [OUTER] join left join outer can have select e. empno, e. ename, d. deptno, d. dname, d. locfrom emp e left join dept don e. deptno = d. deptno; select e. empno, e. ename, d. deptno, d. dname, d. locfrom emp e left outer join dept don e. deptno = d. deptno;

-- Right join right join select e. empno, e. ename, d. deptno, d. dname, d. locfrom emp e right join dept don e. deptno = d. deptno;

-- Inner join returns the intersection create table empbak as select * from emp where emp. empno in (7369,749 9, 7521,756 6, 7654,769 8); select e1.empno, e1.empno, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno from emp e1 inner join empbak e2 on e1.empno = e2.empno;

-- Full outer join fetch Union set update empbak e set e. empno. = 8369 where e. empno = 7369; update empbak e set e. empno. = 8499 where e. empno = 7499; update empbak e set e. empno. = 8521 where e. empno = 7521; select * from emp e full join empbak s on e. empno = s. empno;

-- For records that do not match, select * from emp e full join empbak s on e. empno = s. empnowhere e. empno is not null and s. empno is not null;

-- Select * from empunionselect * from empbak;

-- Union all: select * from empunion allselect * from empbak;

The three records at the bottom are repeated records. In the above union join, the extra three repeated records are removed.