Detailed Oracle multiple table connections

Reprint: http://database.51cto.com/art/201009/228094.htm

1. Inner connection (natural connection)

2. External connection

(1) Left outer connection (the table on the left is unrestricted)

(2) Right outer connection (no restriction on the right table)

(3) Full-outer connection (no restriction on both the left and right tables)

3. Self-connect (connection within the same table)

Standard syntax for SQL:

1. Select Table1. Column,table2. column
2. from table1 [inner | Left | Right | Full ] join table2 on table1.column1 = table2.column2;

The inner join represents the inner join, the left join represents the outer join, the right join represents the outer join, the full join represents a complete outer join, and the ON clause is used to specify the join condition.

Attention:

If you specify an inner or outer join using the FROM clause, you must specify the join condition using the ON clause;

If you specify an outer join using the (+) operator, you must specify the join condition by using the WHERE clause.

A Internal connection (Inner Join/join)

1.1 Inner 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.

1.2 For example, the following is a clear connection:

1.2.1 First create 2 test tables and insert the data:

1. Sql> select * from Dave;
2. ID NAME
3. ---------- ----------
4. 1 Dave
5. 2 BL
6. 1 bl
7. 2 Dave
8. Sql> SELECT * from bl;
9. ID NAME
10. ---------- ----------
11. 1 Dave
12. 2 BL

1.2.3 Use the internal link to query:

2. Sql> Select a.id,a.name,b.name from the Dave a inner join BL B on a.id=b.id; --standard notation
4. ID name Name
6. ---------- ---------- ----------
8. 1 Dave Dave
10. 2 BL bl
12. 1 BL Dave
14. 2 Dave Bl
16. Sql> Select a.id,a.name,b.name from Dave a joins BL B on a.id=b.id; --The Inner keyword is omitted here
18. ID name Name
20. ---------- ---------- ----------
22. 1 Dave Dave
24. 2 BL bl
26. 1 BL Dave
28. 2 Dave Bl
30. Sql> Select a.id,a.name,b.name from Dave A,bl b where a.id=b.id; --Select multiple table query
32. ID name Name
34. ---------- ---------- ----------
36. 1 Dave Dave
38. 2 BL bl
40. 1 BL Dave
42. 2 Dave Bl

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

1.3 Natural Connection (Natural join)

A natural connection is to look for fields in two tables that have the same data type and column names, and then automatically connect them together and return all results that match the criteria.

Let's take a look at the examples of natural connections:

1. sql> Select ID,name from Dave a natural join BL B;
2. ID NAME
3. ---------- ----------
4. 1 Dave
5. 2 BL

Here we do not specify the conditions for the connection, in fact Oracle has made a move for us, and the ID and name fields in the Dave table are connected to the ID and name fields in the BL table. Which is actually equivalent to

1. Sql> Select dave.id,bl.name
2. From Dave joins BL on dave.id = Bl.id and Dave. Name=bl.name;
3. ID NAME
4. ---------- ----------
5. 1 Dave
6. 2 BL

Therefore, we can also understand the natural connection as one of the inner joins.

Some things to note about natural connections:

(1). If there are multiple fields of the two tables that are connected naturally to the same name and type, then they will be treated as a natural connection condition.

(2). If the two tables that are naturally connected are only the same as the field names, but the data types are different, an error is returned.

Two Outer JOIN (Outer 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 using the (+) operator, if more than one condition is included 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.

Before we do the experiment, we'll add some different data to the Dave table and BL. for easy testing.

1. Sql> SELECT * from bl;
2. ID NAME
3. ---------- ----------
4. 1 Dave
5. 2 BL
6. 3 Big Bird
7. 4 EXC
8. 9 Huaining
9. Sql> select * from Dave;
10. ID NAME
11. ---------- ----------
12. 8 Anqing
13. 1 Dave
14. 2 BL
15. 1 bl
16. 2 Dave
17. 3 DBA
18. 4 sf-express
19. 5 DMM

2.1 Left Outer connection (outer join/left join)

The left join is based on the left table record, and in the example Dave can be seen as the right-hand table, and BL can be seen as an rvalue, whose result set is the data in the Dave table, plus the data that matches the Dave table and the BL table. In other words, the records of the left table (Dave) are all represented, and the right table (BL) displays only those records that match the search criteria. Where the BL table is not logged is null.

Example:

2. Sql> select * from Dave a left join BL b on a.id = b.id;
4. ID name ID name
6. --------- ---------- ---------- ----------
8. 1 BL 1 Dave
10. 1 Dave 1 Dave
12. 2 Dave 2 bl
14. 2 BL 2 bl
16. 3 DBA 3 Big Bird
18. 4 sf-express 4 EXC
20. 5 DMM --the B table here is NULL because there is no match to
22. 8 Anqing --Here the B table is null because there is no match to the
24. Sql> select * from Dave a left outer joins BL b on a.id = b.id;
26. ID name ID name
28. ---------- ---------- ---------- ----------
30. 1 BL 1 Dave
32. 1 Dave 1 Dave
34. 2 Dave 2 bl
36. 2 BL 2 bl
38. 3 DBA 3 Big Bird
40. 4 sf-express 4 EXC
42. 5 DMM
44. 8 Anqing

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.

1. Sql> Select * from Dave A,bl b where a.id=b.id (+); --NOTE: Use the keyword WHERE (+)
2. ID name ID name
3. ---------- ---------- ---------- ----------
4. 1 BL 1 Dave
5. 1 Dave 1 Dave
6. 2 Dave 2 bl
7. 2 BL 2 bl
8. 3 DBA 3 Big Bird
9. 4 sf-express 4 EXC
10. 5 DMM
11. 8 Anqing

2.2 Right outer connection (outer join/right join)

The result is the opposite of the left join, which is based on the right table (BL), which shows the BL table's record, plus the results of Dave and BL matching. The place where the Dave table is insufficient is filled with null.

Example:

1. Sql> select * from Dave a right joins BL B on a.id = b.id;
2. ID name ID name
3. ---------- ---------- ---------- ----------
4. 1 Dave 1 Dave
5. 2 BL 2 bl
6. 1 BL 1 Dave
7. 2 Dave 2 bl
8. 3 DBA 3 Big Bird
9. 4 sf-express 4 EXC
10. 9 Huaining --The left table here is insufficient to fill with null

7 rows have been selected.

1. Sql> select * from Dave a Right outer join BL b on a.id = b.id;
2. ID name ID name
3. ---------- ---------- ---------- ----------
4. 1 Dave 1 Dave
5. 2 BL 2 bl
6. 1 BL 1 Dave
7. 2 Dave 2 bl
8. 3 DBA 3 Big Bird
9. 4 sf-express 4 EXC
10. 9 Huaining --The left table here is insufficient to fill with null

7 rows have been selected.

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 left table, the right table is all displayed, so the right connection.

1. Sql> Select * from Dave A,bl b where a.id (+) =b.id;
2. ID name ID name
3. ---------- ---------- ---------- ----------
4. 1 Dave 1 Dave
5. 2 BL 2 bl
6. 1 BL 1 Dave
7. 2 Dave 2 bl
8. 3 DBA 3 Big Bird
9. 4 sf-express 4 EXC
10. 9 Huaining

2.3 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.

Example:

1. Sql> select * from Dave a full join BL b on a.id = b.id;
2. ID name ID name
3. ---------- ---------- ---------- ----------
4. 8 Anqing
5. 1 Dave 1 Dave
6. 2 BL 2 bl
7. 1 BL 1 Dave
8. 2 Dave 2 bl
9. 3 DBA 3 Big Bird
10. 4 sf-express 4 EXC
11. 5 DMM
12. 9 Huaining

9 rows have been selected.

1. Sql> select * from Dave a full outer join BL b on a.id = b.id;
2. ID name ID name
3. ---------- ---------- ---------- ----------
4. 8 Anqing
5. 1 Dave 1 Dave
6. 2 BL 2 bl
7. 1 BL 1 Dave
8. 2 Dave 2 bl
9. 3 DBA 3 Big Bird
10. 4 sf-express 4 EXC
11. 5 DMM
12. 9 Huaining

9 rows have been selected.

Three Self-connect

A self-join is a frequently used connection in an SQL statement, and a self-join allows you to treat one mirror of its own table as another table, allowing you to get some special data.

Example:

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.

Below we need to find out each employee's name and manager's name. What do we do at this time?

If we have two of these tables teaching workers and MGR, then we can write SQL statements very well.

Select Worker.name,

Mgr.name

From Worker,mgr

Where worker.id = mgr.id;

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:

2. sql> Select work.ename worker,mgr.ename Manager from scott.emp work , scott.emp Mgr
4. 2 where work.mgr = mgr.empno (+)
6. 3 Order by Work.ename;
8. WORKER MANAGER
10. ---------- ----------
12. ADAMS SCOTT
14. ALLEN BLAKE
16. BLAKE KING
18. CLARK KING
20. FORD JONES
22. JAMES BLAKE
24. JONES KING
26. KING --Here the right table is insufficient to fill with null
28. MARTIN BLAKE
30. MILLER CLARK
32. SCOTT JONES
34. SMITH FORD
36. TURNER BLAKE
38. WARD BLAKE

Detailed Oracle multiple table connections