Join Operation Basic: Outer connection, natural connection, inner connection

Original connection: http://www.cnblogs.com/huangfr/archive/2012/06/20/2555530.html

Join operation is basically divided into 3 categories: outer join ( fine divided into: Left join, right connection, full connection ), natural connection, inner connection

The commonality of join operations: The first step is to have a Cartesian product for all participating tables before they are based on each connection

Criteria for recording a filter

Sql> SELECT * FROM Employees;

NAME	department_id	SALARY
Getz	10	3000
Davis	20	1500
King	20	2200
Davis	30	5000
Kochhar		5000

Sql> SELECT * from departments;

department_id	Department_name
10	Sales
20	Marketing
30	Accounts
40	Administration

------------------leftouter join----------------

Sql> SELECT * FROM Employees e LEFT OUTER JOIN departments D on E.DEPARTMENT_ID=D.DEPARTMENT_ID;

Additional: Oracle9i the left connection in previous versions is as follows:

Sql> SELECT * FROM Employees E, Departments D on e.department_id=d.department_id (+);

---------------------------right outer join------------------------

Sql> SELECT * FROM Employees right OUTER join departments using (DEPARTMENT_ID);

Additional: Oracle9i the left connection in previous versions is as follows:

Sql> SELECT * FROM Employees E, Departments D where E.DEPARTMENT_ID (+) =d.department_id;

--------------------------------fullJoin----------------------------

Sql> SELECT * FROM Employees full join departments using (DEPARTMENT_ID);

Description: [1] An outer join must provide the appropriate join condition using the ON or using clause

[2] cannot specify table aliases for the columns enumerated in the Using clause, even in the group BY and select clauses

[3] outer JOIN rule: Left join right complement, right and left complement, all connected and merged

For example, when you make a right connection to a table departments table, the two tables complete the Cartesian product and then filter the records with the same department_id values in the two tables based on the join condition using (department_id), but the department_id=40

There are no records in the Employees table that match, and in common sense this department_id=40 record will be thrown

Discard, but just to preserve all records in the connection table (departments table) must be virtualized in the Employees table

A matching record to preserve all the records of the connection table, and of course the virtual record will display a value of NULL

--------------------------Natural Join-----------------------------

Sql> SELECT * FROM Employees natural join departments;

Description: A natural connection is done by using all of the identically named attribute pairs in the participating table relationship (that is, equality comparisons), so you do not need to add the join condition yourself

The difference between an outer join is that a record that is not matched can be virtual with a matching record to secure all records in the connection table, but the natural connection does not

----------------------Inner Join----------------

Sql> SELECT * FROM employees INNER JOIN departments using (DEPARTMENT_ID);

Description: Inner joins are essentially the same as natural connections, except that a natural connection can only be an equivalent connection to an attribute of the same name, whereas an inner join may use a using or ON clause to specify the join condition, which indicates that a two field is equal (you can have different names).  

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

Concept of connection:

The connection is divided into three kinds of conditional connection, equivalent connection and natural connection.

1. A conditional connection is a connection that selects rows that satisfy a condition in the Cartesian product of multiple tables, such as a conditional query such as SELECT * from A,b where A.A > a.b.

2, the equivalent connection is a special condition connection, when the condition is a field = a field, that is the equivalent connection. such as select Ename,sal,dname from emp,dept where Emp.deptno=dept.deptno;

3, natural connection is a special equivalent connection, he asked that more than one table has the same property field, then the condition is the same property field values are equal, and finally the table repeated the property field removed, that is, the natural connection. The SELECT * from A natural join B is equivalent to select a.a,a.b,a.c,b.d from A.C = B.C, as in the A,b,c field in a and the C,d field in B.

the difference between an inner connection and an equivalent connection:

Inner joins: A connection in which a data item is equal in two tables (or connections) is called an inner join. The equivalent connection generally sets the condition in the WHERE clause, the inner join generally sets the condition with the ON clause, but the inner join and the equivalent connection effect are the same.

The inner connection and the equivalent connection are actually one thing (equivalent).

It is often asked to select A.id,b.name from A,b where A.id=b.pid and

What is the difference between select A.id,b.name from a inner join B on a.id=b.pid, and which is more efficient.

Actually, it's a matter of time. Only the inner joins are written in the SQL 1999 rule. The two are talking about the same.