Multiple table Connection Methods in Oracle

1. inner connection (natural connection)

2. External Connection

(1) left Outer Join (the table on the left is not restricted)

(2) Right Outer Join (the table on the right is not restricted)

(3) Full outer join (no restrictions are imposed on both the left and right tables)

3. Self-join (connections in the same table)

Standard SQL Syntax:

Select table1.column, table2.column from table1 [inner | left | right | full] join table2 on table1.column1 = table2.column2; inner join indicates inner join;
Left join indicates the left Outer join;
Right join indicates the right outer join;
Full join indicates a full outer join;
The on clause is used to specify the connection conditions.

Note:

If you use the from clause to specify the internal and external connections, you must use the on clause to specify the connection conditions;

If you use the (+) operator to specify the outer join, you must use the where clause to specify the join condition.

1. Inner Join/Join)

1.1 Inner Join

The Inner join logical operator returns each row that matches the first (top) input and the second (bottom) Input join. This is the same as querying multiple tables using select statements, so there are very few internal connections.
Another note is that Join is an inner join by default. Therefore, when writing an inner connection, we can omit the keyword inner.

1.2 The following is an example to describe the internal connection:

1.2.1 create two 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 1.2.3 use the internal link for query:

SQL> Select. id,. name, B. name from dave a inner join bl B on. id = B. id; -- standard expression id name ---------- 1 dave 2 bl 1 bl dave 2 dave bl SQL> Select. id,. name, B. name from dave a join bl B on. id = B. id; -- the inner keyword id name ---------- 1 dave 2 bl 1 bl dave 2 dave bl SQL> Select. id,. name, B. name from dave a, bl B where. id = B. id; -- Select multi-Table query id name ---------- 1 dave 2 bl 1 bl dave 2 dave bl from the results of these three SQL statements, we can also see that, they play the same role.

1.3 Natural join)

A natural join is to search for fields with the same data type and column name in the two tables, then automatically connect them, and return all results that meet the condition.

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

SQL> Select id, name from dave a natural join bl B; ID NAME ---------- 1 dave 2 bl here we do not specify the connection conditions. In fact, oracle is what we advocate, the id and name fields in the dave table are connected to the id and name fields in the bl table. That is, it is actually equivalent

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 think of a natural connection as an internal connection.

Notes for natural connection:

(1). If multiple fields in the two tables that are naturally connected meet the same name and type, they will be used as conditions for natural connection.

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

Ii. Outer Join)

Outer join returns each row that matches the join of the first (top) input and the second (bottom) input. It also returns any rows in the first input without matching rows in the second input. There are three types of external connections: left Outer Join, right outer join, and full outer join. Corresponding SQL: LEFT/RIGHT/FULL OUTER JOIN. Generally, the keyword outer is omitted. Written as LEFT/RIGHT/full join.

When connecting left Outer Join and right Outer Join, a table is used as the base table, and the table content is displayed in full, and the matching content of the two tables is added. If the data in the base table is not recorded in the other table. The column in the row of the associated result set is displayed as NULL ).

You can also use "(+)" for external connections. Notes for using (+:

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

2. When an external join is executed using the (+) operator, if the where clause contains multiple conditions, the (+) operator must be included in all conditions.

3. the (+) operator is only applicable to columns and cannot be used in expressions.

4. the (+) operator cannot be used with the or and in operators.

5. the (+) operator can only be used to implement left Outer Join and right outer join, but not to implement full outer join.

Before doing the experiment, 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 Huai Ning 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 join (left outer join/Left join)

Left join is based on records in the left table. In this example, Dave can be viewed as the left table, BL can be viewed as the right table, and its result set is the data in Dave's table, add data that matches the Dave table and the BL table. In other words, all records in the left table (Dave) are displayed, while the right table (BL) only displays records that meet the search criteria. All records in the BL table are NULL.

Example:

SQL> select * from dave a left join bl B on. id = B. id; 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 -- Here B table is null, because it does not match 8 Anqing -- Here Table B is null, because it does not match SQL> select * from dave a left outer join bl B on. id = B. id; id name -----------------------