Mysql Multi-table connection query inner JOIN and outer join use

The meaning of join is like the English word "join", connecting two tables, roughly divided into inner connection, outer connection, right connection, left connection, natural connection. The description here is to throw out a rotten picture and insert the test data.

First of all, the classification (inner connection, outer connection, cross Connection) and connection method (as follows) are listed in this article:

A) Inner connection : Join,inner join

B) Outer connection : Left join,left outer join,right join,right outer join,union

C) Cross connect: intersect Join

Case table:

T_users:

T_department:

First type: Inner connection inner JOIN

INNER JOIN INNER JOIN is the most common connection operation. From a mathematical point of view is to find the intersection of two tables, from the angle of Cartesian product is to pick out the Cartesian product on the condition of the set of records.

There are inner join,where (equivalent connection), Straight_join,join (omit inner) four kinds of notation.

Select  from  = t.department_id

The results show that only the partial data of the on condition are common to the table A and B tables.

Second type: outer connection left join,left outer JOIN, right JOIN, right outer join, union

1) The meaning of the left join is to find the intersection of the two table A and B tables plus the remaining data from the left table . Still from the Cartesian product point of view, is to first pick out the Cartesian product on the condition of the set of records, and then add the left table a table of the remaining records (see the last three).

Select    from t_users T  Left Join  on = t.department_id

Mysql Multi-table connection query inner JOIN and outer join use