Overview of Oracle Table join methods

The connections between Oracle tables are divided into three types:

1. Inner connection (natural connection)

2. Outer connection

(1) Left OUTER join (the table on the left is unrestricted)

(2) Right outer join (the table on the right is unrestricted)

(3) Full outer join (no restriction on both tables)

3. Self-Connection (connection in the same table)

Standard syntax for SQL:

Select Table1.column,table2.column from table1 [Inner | left | right | full] join table2 on table1.column1 = Table2.colum N2;

INNER join denotes inner join;

A LEFT join represents an outer join;

Right join represents a right-hand outer join;

Full join represents a complete outer join;

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 by using the ON clause;

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

One, inner connection (Inner join/join)

1. Inner Join

The Inner join logical operator returns each row that satisfies the first (top) input join with the second (bottom) input. This is the same effect as using a select query for multiple tables, so there is little internal connection.

Another point to note is that the join defaults to the inner join. So we can omit the Inner keyword when we connect in writing.

2, the following examples of the connection in the Ming:

(1) Create 2 test tables and insert data first:

Sql> select * from Dave;

ID NAME

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

1 Dave.

2 BL

1 bl

2 Dave.

Sql> select * from BL;

ID NAME

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

1 Dave.

2 BL

(2) query with the internal link:

Sql> Select A.id,a.name,b.name from the Dave a inner join BL B on a.id=b.id; --Standard wording

ID name Name

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

1 Dave Dave.

2 BL bl

1 BL Dave

2 Dave Bl

Sql> Select A.id,a.name,b.name from Dave A join BL B on a.id=b.id; --The Inner keyword is omitted here

ID name Name

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

1 Dave Dave.

2 BL bl

1 BL Dave

2 Dave Bl

Sql> Select A.id,a.name,b.name from Dave A,bl b where a.id=b.id; --Select multiple table query

ID name Name

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

1 Dave Dave.

2 BL bl

1 BL Dave

2 Dave Bl

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

3. Natural connection (Natural join)

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

Let's take a look at the natural connection example:

Sql> Select Id,name from Dave a natural join BL B;

ID NAME

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

1 Dave.

2 BL

Here we do not specify the conditions of the connection, in fact Oracle for our own free will, the ID and name fields in the Dave table are connected to the ID and Name field in the BL table. Which is actually equivalent to

Sql> Select Dave.id,bl.name from Dave join bl on dave.id = Bl.id and dave.name=bl.name;

ID NAME

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

1 Dave.

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 more than one of the two tables that make a natural connection are satisfied with the same name type, they will be used as a natural connection condition.

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

Second, outer joins (Outer join)

The outer join returns each row that satisfies the first (top) input with the second (bottom) input. It also returns any rows in the first input that do not have a matching row in the second input. The outer joins are divided into three kinds:

Left outer connection, right outer connection, all outer connection. Corresponds to the Sql:left/right/full OUTER JOIN. Usually we omit the outer keyword. Written: 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 the matching of the two tables. If the data for the base table is not recorded in another table. Then in the associated result set row

The column in is displayed as a null value (NULL).

For outer joins, you can also use "(+)" to indicate. Some things to note about using (+):

The 1. (+) operator can only appear in the WHERE clause and cannot be used concurrently with the outer join syntax.

2. When an outer join is performed using the (+) operator, if multiple conditions are included in the WHERE clause, the (+) operator must be included in all conditions

The 3. (+) operator applies only to columns and cannot be used on an expression.

4. (+) operators cannot be used with OR and in operators.

The 5. (+) operator can only be used to implement left and right outer joins, but not for full outer joins.

Before we do the experiment, we'll add some different data to the Dave table and BL. To facilitate 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

1. LEFT OUTER JOIN (outer join/left join)

The left join is based on a left table record, in which Dave can be viewed as the left-hand table, and BL as the right table, and its result set is the data in the Dave table, plus the data that matches the Dave table and the BL table. In other words, left

The table (Dave) records will all be represented, and the right table (BL) will only display records that match the search criteria. The BL table has a low record of 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