Oracle Learning Notes (i)----------internal and external connections

So long finally can calm down to tidy up the work to learn the things (mainly or oneself too lazy. ）

Say less nonsense, the text begins

In the company, the basic database is Oracle, so learn a bit of Oracle's knowledge, now to sort out their own problems and learn some of the things.

First of all, I often confuse the internal connection, the outside connection (the university has not understood. ）

There are many ways to connect data tables, such as: internal and external connections, etc.

One (internal connection):

The inner connection is what we often call the equivalent connection or the natural connection.

The connection inner JOIN keyword joins the table (or directly with the where and the "=" number) within the connection.

Directly on the example:

The following tables are available:

Student table:

Class table, corresponding to the ClassID in the student table:

--self-connect: Returns only matches for two table join columns. --The following three query results are the same. Select *  fromStudent SInner JoinClass C onS.classid=c.id;Select *  fromStudent SJoinClass C onS.classid=c.id;Select *  fromStudent S,class CwhereS.classid=C.id;

Natural Connection Results:

The inner join (natural connection) matches only the records that match the criteria.

Two (outer connection):

The outer joins are divided into left outer connection, right outer connection and full outer connection.

1, left outer connection (the table on the left is unrestricted), with the right out join keyword

-- left Connection: List all of the left table, and the right side of the table to meet the criteria, non-conforming to the null value instead.  -- in the (+) calculation, which band (+) which needs to meet the conditions, the other all. That is, left and right connected, right and left connected.  -- The following result set is the same.  Select*from leftjoins on S.classid=  C.id; Select *  from where s.classid=c.id (+);

Left OUTER JOIN Result:

Left outer joins: Lists the right table's qualifying records and all records of the left table, and the right table does not match the null representation.

Oracle can use (+) to represent an outer join, and when (+) is followed by a table's properties, it indicates which table needs to be eligible. On the left side represents the right outer connection, and on the right side represents the left outer connection.

2. Right outer join (no restriction on the right table), with the key

Look at the following SQL statement:

-- right outer connection: Same as left JOIN, list all of the right table, and the left table meet the conditions, do not meet the criteria -- replaced with a null value  .  --like (+), its position is opposite to the connection.  Select*from rightjoin on s.classid= c.id; Select *  from where s.classid (+)=c.id;

Right outer join Result:

The right outer join is the same as the left outer join, except that the table that meets the criteria is different (the right outer join requires that the left table matches the criteria).

Right outer joins: Lists the records that match the conditions of the left table and all the records in the right table, and the left table does not match the null representation.

Oracle can use (+) to represent an outer join, and when (+) is followed by a table's properties, it indicates which table needs to be eligible. On the left side represents the right outer connection, and on the right side represents the left outer connection.

3, the full outside connection (both the left and right tables are unrestricted), with the complete join keyword

Or an example:

-- Full Connection: Produces a result set of m+n, lists all of the two tables, does not meet the criteria, and replaces them with null values.  Select*from fulljoin on S.classid=c.id;

Full outer JOIN Result:

All outer joins are unrestricted on both sides of the table, listing all records of the left and right tables, without qualifying null overrides.

Oracle Learning Notes (i)----------internal and external connections