Shulou(Shulou.com)06/01 Report--

1. Oracle connection type

☆ notes: types classified according to their own understanding are not official definitions.

1. Internal connection

① equivalent connection

② non-equivalent connection

③ Natural connection

2. External connection

① left outer connection

② right external connection

③ full external connection

Table data preparation

T table

Field a field b1182283384NULLt1 table field x field y192837

III. Internal connection

1. Internal joins are associated with multiple tables through inner join to display the query results. Examples are as follows:

SQL > select * from t inner join T1 on t.a=t1.x

Effect display:

Description: through the t table inner join T1 table, we get the display effect of the above figure.

2. To achieve the same query results in the above figure, we can also use the equivalent join method to achieve

SQL > select * from t _ 1 where t.a=t1.x

3. Inner join can be simplified to join, but it is not recommended to write in this way in practice, because it is not readable.

4. The popular understanding is that equivalent connection, non-equivalent connection and natural connection are all the concrete forms of the big category of inner connection.

5. Non-equivalent connections can be implemented in where and inner join.

SQL > select * from t inner join T1 on t.at1.x

SQL > select * from t _ 1 where t.at1.x

The results of the above two sentences of sql are the same.

6. Self join is a frequently used join method in SQL statements. Using self-join, you can treat a mirror of your own table as another table, so that you can get some special data.

For example: select * from employee worker,employee manger where worker.id=manger.id

IV. External connection

1. The left outer join: it is realized by the keyword left join, which is all written as left out join. In the actual operation, it is generally written as left join, and it can also be replaced by adding (+) after the right join value. The left join means that the two tables are associated, and the number of queries is based on the left table, but the constraint is affected by the right table. For cases that do not meet the equivalent, use NULL instead, such as the following example:

Note: first of all, through the equivalent connection, the first three rows of data association are displayed normally, and the fourth line is displayed because the sql statement is a left join, as we said, the number of data display entries is based on the left table, but all those that do not meet the requirements are filled with NULL.

2. Right connection: contrary to the left connection, the core idea is the same. As long as you are proficient in the left connection, the right connection will naturally be self-taught.

3. Notes on the use of (+):

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

When using the (+) operator to perform an outer join, if multiple conditions are included in the where clause, the (+) operator must be included in all conditions

The (+) operator applies only to columns, not to expressions.

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

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

4, full external join: through the full join keyword join to achieve, the left and right tables are not restricted, both tables do not meet the conditions are filled with NULL, full external join does not support (+) this way of writing, that is, can not be written on both sides of the left table and right table (+).

SQL > select * from t full join T1 on t.a=t1.x

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.