oracle-Table Connection

Type of table connection: equivalent connection, self-connect, non-equivalent connection, (left and right) outer connection

One, equivalent connection: by two tables with the same meaning of the column, you can establish an equal join condition.

============================================================  

1. Only rows in the connection column that appear in two tables and meet the join criteria will appear in the query results.

eg

CREATE TABLE Emp_1 as SELECT * from EMP;

CREATE TABLE Dept_1 as SELECT * from dept;

Insert into Emp_1 (EMPNO,DEPTNO) values (1231,88);

Insert into dept_1 (deptno,dname) VALUES (n, ' SS ');

Commit

2, ambiguous columns need to add a table name

eg

DESC EMP

DESC Dept Find the same table

Sql> Select Ename,deptno

2 from EMP a,dept b

3 where A.deptno=b.deptno;

Select Ename,deptno

*

ERROR at line 1:

Ora-00918:column ambiguously defined

two, self-connection: Equivalent to an equivalent connection, just a table as two tables

============================================================  

such as: What is the name of the manager who needs to query each employee?

Select E.ename,m.ename

From EMP e,emp m

where E.mgr=m.empno;

Third, not equivalent connection: Two tables in the related two columns for unequal connection, the comparison symbol is generally >,<,..., between. and..

============================================================  

Eg: Find out the salary level for each employee

Select Ename,grade,sal,losal,hisal

From Emp,salgrade

Where Sal between Losal and Hisal;

Four, outer connection

Left OUTER join: Left condition (+) = right condition, in addition to displaying rows that satisfy the join condition, also displays information that does not match the join condition in the table in which the right condition resides

Right outer join: Left condition = right condition (+), in addition to displaying rows that satisfy the join condition, displays information that does not match the join condition in the table where the left condition is located

============================================================

eg

in the Dept table, there is a line 40th department has no way to display, because there is no line of data in the EMP table to match it, such as:

Select Ename,loc

From Emp,dept

where Emp.deptno (+) =dept.deptno;

V. Product of Descartes

When two tables (more than one table) are associated but they do not have a valid connection condition, then a Cartesian product will appear at this time

This article is from "Rookie Talent" blog, please be sure to keep this source http://omphy.blog.51cto.com/7921842/1895369

oracle-Table Connection