Oracle Table Connection Summary

1 description

1) The connection of two tables is established by linking one or more columns in one table to columns in another table. The expressions used to connect two tables make up the join condition. When the connection succeeds, the data in the second table is connected to the first table and a composite result set is formed

2) There are 5 basic types of connection, inner connection, outer connection, natural connection, cross connection, self-connection. The following are the next two Isaac.

2 Internal connection (Inner Join/join)

The Inner join logical operator returns each row that satisfies the first (top) input and the second (bottom) input join. This is the same effect as querying multiple tables with SELECT, so there are few internal connections. Another point to note is that join is inner join by default. So we can omit the Inner keyword when we write the inner connection. Note: Equivalent to the most common equivalent connection

The following examples illustrate the inner connection:

Ø standard notation: INNER JOIN

SELECT* FROM EMP INNER JOIN DEPT ONEMP.DEPTNO = DEPT.DEPTNO

Ø The inner keyword is omitted here

SELECT* FROM EMP JOIN DEPT ONEMP.DEPTNO = DEPT.DEPTNO

Øselect Multi-Table query

SELECT* FROM EMP,DEPT WHEREEMP.DEPTNO = DEPT.DEPTNO

The results of the implementation are as follows:

From the results of these three SQL we can also see that their role is the same.

3 outer joins (out join)

Outer join returns a row for each join that satisfies the first (top) input and the second (bottom) input. It also returns any row in the first input that does not have a matching row in the second input. The outer connection is divided into three kinds: Left outer connection, right outer connection, full outer connection. Corresponds to Sql:left/right/full OUTER JOIN. Usually we omit the outer keyword. Written as: Left/right/full JOIN.

A table is the base table for both the left and right outer joins, and the contents of the table are all displayed, followed by two tables that match the contents. If the data in the base table is not recorded in another table. Then the columns in the associated result set row are displayed as null values (NULL).

For outer joins, you can also use "(+)" to represent them. Some things to note about using (+):
1. The (+) operator can only appear in the WHERE clause and cannot be used in conjunction with the outer join syntax.
2. When an outer join is performed by the (+) operator, if there are multiple conditions in the WHERE clause, the (+) operator must be included in all conditions
3. The (+) operator applies only to columns, not to expressions.
4. The (+) operator cannot be used with the OR and in operators.
5. The (+) operator can only be used to implement left outer and right outer joins, not for full outer joins.

3.1 Left Outer connection (outer join/left join)

The left outer join is based on the equivalent connection with unmatched data from the main table, and the left join is based on left table records, the example EMP can be seen as an lvalue, dept can be seen as the right table, its result set is the data in the EMP table, plus the EMP table and Dept table matches the data. In other words, the records of the left table (EMP) will all be represented, and the right table (DEPT) will only display records that match the search criteria. The Dept table has insufficient records where it is null.

SELECT * from EMP left JOIN dept on EMP. DEPTNO = DEPT. DEPTNO

With (+) to achieve, the + number can be understood as follows: + to supplement, that is, which table has a plus, this table is a matching table. So the plus sign is written in the right table, the left table is all displayed, so it is left connected. Note: Use the keyword WHERE (+)

SELECT* FROM EMP,DEPT WHEREEMP.DEPTNO = DEPT.DEPTNO(+)

3.3 Right outer connection (outer join/right join)

The right outer join is based on the equivalent connection and the mismatch data of the joined table and the result of the left join is just the opposite, it is based on the right table (BL), showing the BL table so the record, in addition to the results of Dave and BL match. The place where the Dave table is insufficient is filled with null.

SELECT* FROM emp RIGHT JOIN dept ONEMP.DEPTNO = DEPT.DEPTNO

With (+) to achieve, the + number can be understood as follows: + to supplement, that is, which table has a plus sign, this table is a matching table. So the plus sign is written in the left table, the right table is all displayed, so the right connection.

SELECT * from Emp,dept WHERE EMP. DEPTNO (+) = DEPT. DEPTNO

3.4 All-out connection (full outer join/full join)

Neither the left table nor the right table is restricted, and all the records are displayed, where two tables are insufficient to fill with null. The full outer join does not support (+) this notation.

SELECT* FROM EMP FULL JOIN DEPT ONEMP.DEPTNO = DEPT.DEPTNO

4. Natural connection

The natural connection is to select the same name attribute on the generalized Cartesian product RxS to match the equal conditional tuple, then the projection, remove duplicate the same name attribute, compose the new relation. That is, the natural connection is to look for those fields that have the same data type and column names in both tables, and then automatically connect them and return all results that match the criteria.

SELECT * from EMP NATURAL JOIN DEPT

Note: No qualification is allowed, otherwise an error will be added.

SQL Error: The column used in the Ora-25155:natural join cannot have qualifier 25155. 00000-  "column used in NATURAL join cannot has qualifier" *cause:    Columns that is used for a named-join (either A NATURAL join           or a join with a USING clause) cannot has an explicit qualifier.*action:   Remove the qualifier.

5. Cross-Connect

The cross join does not have an ON clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set is equal to the number of data rows in the first table that meet the query criteria multiplied by the number of data rows in the second table that meet the query criteria.

SELECT* FROM emp CROSS JOINdept;

Equivalent to

SELECT* FROMemp,dept;

6. Self-connect

Connected tables are the same table, and using a self-join allows one mirror of its own table to be treated as another table, allowing for some special data.

One of the tables in the Oracle's Scott schema is EMP. Each of the employees in the EMP has their own MGR (manager), and each manager himself is a company employee and has his own manager.

But now we have only one EMP table. So we can use self-connection. The intention of self-connection is to treat a table as more than one table to make a connection. We can write SQL statements like this:

Select Work.ename worker,mgr.ename from scott.emp work, scott.emp mgr where work.mgr = mgr.empno order by Work.ename;

A diagram of the various connections:

Oracle Table Connection Summary