Oracle multi-Table connection

1. internal Connections (natural connections) 2. outer Join (1) left Outer Join (no restrictions on the table on the left) (2) Right Outer Join (no restrictions on the table on the right) (3) full outer join (no restrictions are imposed on both the left and right tables) 3. standard SQL Syntax: selecttable1.column, table2.columnfromtable1 [inner | left

1. internal Connections (natural connections) 2. outer Join (1) left Outer Join (no restrictions on the table on the left) (2) Right Outer Join (no restrictions on the table on the right) (3) full outer join (no restrictions are imposed on both the left and right tables) 3. standard SQL syntax for self-join (join in the same table): select table1. column, table2. column from table1 [inner | left

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 the 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 internal links for query:

 
 

  
  
SQL> Select a. id, a. name, B. name from dave a inner join bl B on a. id = B. id; -- standard syntax
  
  
ID NAME
  
  
------------------------------
  
  
1 dave
  
  
2 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
  
  
------------------------------
  
  
1 dave
  
  
2 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 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 their functions are the same.

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 makes our own claim to connect the id and name fields in the dave table with 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 regard natural connections 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 a. 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, table B is null because it does not match
  
  
8 Anqing -- table B is null because it does not match
  
  
 
  
  
SQL> select * from dave a left outer join bl B on a. 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
  
  
8 Anqing
 
 

This number can be understood as follows: + indicates the supplement, that is, which table has a plus sign, and this table is a matching table. Therefore, the plus sign is written in the right table, and the left table is all displayed, so it is a left join.

 
 

  
  
SQL> Select * from dave a, bl B where a. id = B. id (+); -- Note: The where keyword is used when (+) is used.
  
  
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 join (right outer join/right join)

The result of left join is the opposite. It is based on the right table (BL) and displays the records of the BL table. The results of Dave and BL matching are added. Fill the Dave table with NULL when the table is insufficient.

Example:

 
 

  
  
SQL> select * from dave a right join bl B on a. id = B. id;
  
  
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 Huai Ning -- Null is not enough in the left table.
 
 

You have selected 7 rows.

 
 

  
  
SQL> select * from dave a right outer join bl B on a. id = B. id;
  
  
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 Huai Ning -- Null is not enough in the left table.
 
 

You have selected 7 rows.

This number can be understood as follows: + indicates the supplement, that is, which table has a plus sign, and this table is a matching table. Therefore, the plus sign is written in the left table, and the right table is all displayed, so it is a right join.

 
 

  
  
SQL> Select * from dave a, bl B where a. id (+) = B. id;
  
  
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 Huai Ning
 
 

2.3 full outer join (full outer join/full join)

No restrictions are imposed on the left and right tables. All records are displayed. If the two tables are insufficient, use null to fill them. All external connections do not support (+.

Example:

 
 

  
  
SQL> select * from dave a full join bl B on a. id = B. id;
  
  
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 Huai Ning
 
 

Nine rows have been selected.

 
 

  
  
SQL> select * from dave a full outer join bl B on a. id = B. id;
  
  
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 Huai Ning
 
 

Nine rows have been selected.

Iii. Self-connection

Self join is a commonly used connection method in SQL statements. Using self join, you can treat one image of your table as another table, so that you can get some special data.

Example:

One table in oracle scott's schema is emp. Every employee in emp has its own mgr (manager), and each manager is also an employee of the company, and also has its own manager.

Next we need to find out the names of employees and managers. What should we do at this time?

If we have two such tables to teach worker and mgr respectively, we can write SQL statements very well.

Select worker. name,

Mgr. name

From worker, mgr

Where worker. id = mgr. id;

But now we only have one emp table. So we can use self-connection. The intention of Self-join is to regard a table as multiple tables for join. We can write SQL statements like this:

 
 

  
  
SQL> select work. ename worker, mgr. ename manager from scott. emp work, scott. emp mgr
  
  
2 where work. mgr = mgr. empno (+)
  
  
3 order by work. ename;
  
  
WORKER MANAGER
  
  
--------------------
  
  
ADAMS SCOTT
  
  
ALLEN BLAKE
  
  
BLAKE KING
  
  
CLARK KING
  
  
FORD JONES
  
  
JAMES BLAKE
  
  
JONES KING
  
  
KING -- the right table is not filled with Null.
  
  
MARTIN BLAKE
  
  
MILLER CLARK
  
  
SCOTT JONES
  
  
SMITH FORD
  
  
TURNER BLAKE
  
  
WARD BLAKE