Oracle database internal and external connection differences and external connection details

INNER JOIN (INNER join): Returns a fully qualified record in 2 tables, with the field contents of each table in the result set coming from the respective table;
Outer JOIN (outer join): Returns the fully qualified record in 2 tables, plus the individual records in the 2 tables, when the fields in the result set have records in only one table, the fields in the other table are filled with null values.

Oracle External connections

(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)

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.

Sql> select * from BL;

ID NAME

---------- ----------

1 Dave

2 BL

3 Big Bird

4 EXC

9 Huaining

Sql> select * from Dave;

ID NAME

---------- ----------

8 Anqing

1 Dave

2 BL

1 bl

2 Dave

3 DBA

4 sf-express

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:

Sql> SELECT * FROM Dave a LEFT join BL b on a.id = b.ID;

ID Name ID Name

--------- ---------- ---------- ----------

1 BL 1 Dave

1 Dave 1 Dave

2 Dave 2 bl

2 BL 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM--the B table here is NULL because there is no match to

8 Anqing--Here the B table is null because there is no match to the

Sql> SELECT * FROM Dave a left outer joins BL B on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

1 BL 1 Dave

1 Dave 1 Dave

2 Dave 2 bl

2 BL 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

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.

Sql> Select * from Dave A,bl b where a.id=b.id (+); -NOTE: Use the keyword WHERE (+)

ID Name ID Name

---------- ---------- ---------- ----------

1 BL 1 Dave

1 Dave 1 Dave

2 Dave 2 bl

2 BL 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

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:

Sql> SELECT * from Dave a right joins BL B on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

9 Huaining--The left table here is insufficient to fill with null

7 rows have been selected.

Sql> SELECT * FROM Dave a right outer join BL B on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

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.

Sql> Select * from Dave A,bl b where a.id (+) =b.id;

ID Name ID Name

---------- ---------- ---------- ----------

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

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:

Sql> SELECT * FROM Dave a full join BL b on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

8 Anqing

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

9 Huaining

9 rows have been selected.

Sql> SELECT * FROM Dave a full outer join BL B on a.id = b.ID;

ID Name ID Name

---------- ---------- ---------- ----------

8 Anqing

1 Dave 1 Dave

2 BL 2 bl

1 BL 1 Dave

2 Dave 2 bl

3 DBA 3 Big Bird

4 sf-express 4 EXC

5 DMM

Oracle database internal and external connection differences and external connection details