Join Operations: external connections, natural connections, and internal connections

Join Operations are classified into three categories:External Connection(Details: left join, right join, full join), Natural connection, internal connection

The commonality of Join Operations: the first step is to perform a flute product for all tables involved in the operation, and then perform the join operation based on the join operation.

Filter records by condition

SQL> select * from employees;

name	department_id
	10	3000
Davis	20	1500
King	20	2200
Davis	30	5000
		5000

SQL> select * from orders;

Department_id	Department_name
10	Sales
20	Marketing
30	Accounts
40	Administration

------------------Left Outer Join----------------

SQL> select * from employees e left Outer Join orders ments D on E. department_id = D. department_id;

 

Additional:In earlier versions of Oracle9i, the left join statement is as follows:

SQL> select * from employees e, orders ments D on E. department_id = D. department_id (+ );

---------------------------Right Outer Join------------------------

SQL> select * from employees right Outer Join orders ments using (department_id );

 

Additional:In earlier versions of Oracle9i, the left join statement is as follows:

SQL> select * from employees e, orders ments d Where E. department_id (+) = D. department_id;

--------------------------------Full join----------------------------

SQL> select * from employees full join orders ments using (department_id );

 

Note:[1]External ConnectionThe on or using clause must be used to provide the corresponding connection conditions.

[2] The table alias cannot be specified for the columns listed in the using clause, even in the group by and select clauses.

[3] Outer Join rules: left join, right join, and left join

For example, when right join is performed on the table orders tables, the department_id records in the two tables are filtered Based on the connection condition using (department_id), but department_id = 40

There is no matching record in the Employees table. It is common sense that the record corresponding to department_id = 40 will be thrown.

However, to save all records in the connector tables

A matched record is used to save all records of the connected table. Of course, the value of this virtual record is null.

 

 

--------------------------Natural join-----------------------------

SQL> select * from employeesNatural joinAdministrative departments;

 

Note:Natural connectionThis is done by performing the equality (equal comparison) operation on all attribute pairs with the same name in the table relationship. Therefore, you do not need to add the connection conditions yourself.

The difference between external connections and record external connections is that an external connection that cannot be matched virtualizes a matching record to save all records in the connection table, but the natural connection does not

 

 

----------------------Inner join----------------

SQL> select * from employeesInner joinAdministrative mentsUsing(Department_id );

 

Note:Internal ConnectionAndNatural connectionBasicSameThe difference is that a natural connection can only be an equivalent connection with the same name, while an internal connectionYou can use the using or on clause.Specify the connection conditions. The connection conditions indicate that two fields are equal (different names are allowed ).

 

 

Bytes -------------------------------------------------------------------------------------------------------------------

Concept of connection:

There are three types of connections: conditional connection, equivalent connection, and natural connection.

1,Conditional connectionIt is to select the join of rows that meet the conditions in the Descartes of multiple tables, such as select * from a, B where a. A> A. B and other conditional queries.

2,EquijoinIt is a special conditional connection. When the condition is a field = a field, it is equivalent connection. For example, select ename, Sal, dname from EMP, DEPT where EMP. deptno = Dept. deptno;

3,Natural connectionIt is a special equijoin. It requires that multiple tables have the same attribute fields, and the condition is that the values of the same attribute fields are equal, and the duplicate attribute fields in the table are removed, it is a natural connection. For example, if field a, field B, and field B contain fields C and field D, select * from a natural join B is equivalent to select. a,. b,. c, B. d from. C = B. c.

 

Internal Connection and equivalent connectionDifferences:

Inner join: an internal join is the same connection of a data item in two tables (or connections. Generally, the where clause is used to set conditions for equijoin. Generally, the on clause is used to set conditions for injoin, but the effect of inner join is the same as that of equijoin.

Internal Connections and equivalent connections are actually one thing (equivalent ).

Some people often ask about select a. ID, B. name from a, B.WhereA. ID = B. PID and

Select a. ID, B. name fromInner joinBOnA. ID = B. What is the difference between PID and which is more efficient.

In fact, it's a matter of fact. Only internal connections are written according to SQL 1999 rules. The two are just one thing.