MySQL database in the left connection, right connection, the inside link detailed introduction

Generally speaking, the left connection, outer connection refers to the left outer connection, right outer connection. Do a simple test, you see.
First, left outer and right outer joins:

The code is as follows

Copy Code

[test1@orcl#16-12 Month-11] Sql>select * from T1; ID NAME ---------- -------------------- 1 AAA 2 BBB [test1@orcl#16-12 Month-11] Sql>select * from T2;         id        Age -------------- ------          1         20          3         Left OUTER join: [test1@orcl#16-12 month -11] Sql>select * from T1 A-O-join T2 on T1.id=t2.id;         ID name                          id         Age --------------------------------------------------           1 aaa                            1                   2 BBB Right outer connection: [test1@orcl#16-12 Month-11 ] Sql>select * from T1 right join T2 on T1.id=t2.id;         ID name                          id         Age --------------------------------------------------           1 aaa                            1                                                     3

As you can see from the above display: the left outer join is based on the table on the left. In layman's terms, the table on the left is all displayed, and the table ID on the right is "spliced" with the same record as the left table ID, such as the record with ID 1. If there is no matching ID, for example, T2 with ID 2 in T1. There is a null display.
The right outer join process is just the opposite.

Look at the inner join (also known as the equivalent connection):

code is as follows	copy code
[test1@orcl#16-12 month -11] sql>select * from t1 inner join T2 on t1.id=t2.id;         ID name                          id         Age --------------------------------------------------           1 aaa                            1

Do you see it? Only one record. The inner join is only to remove the record that conforms to the filter condition is t1.id=t2.id so the record that conforms to t1.id=t2.id only id=1 this one, so show only one. Unlike an outer join, it is the table that takes you as the benchmark (the left outer join is the datum on the left table, and the right outer join is the table on the right) that shows all the rows.

The previous SQL statement is equivalent to:

The code is as follows	Copy Code
SELECT * from t1,t2 where t1.id = T2.id

Supplementary Note:

1, Inner joins (typical join operations, using comparison operators like =  or <>). Includes equal joins and natural joins.     
Inner joins use comparison operators to match rows in two tables based on the values of the columns that are common to each table. For example, retrieve all lines that are the same as the student identification number for the students and courses tables.   
   
2, outer joins. An outer join can be a left outer join, a right outer join, or a full outer join.     
When you specify an outer join in the FROM clause, you can specify:    
1 in one of the following groups of keywords left  join or left The result set of the OUTER join    
Left outer join includes all the rows of the left table specified in the   OUTER clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null values.       
2) right  JOIN or right  outer  join     A
Right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table.       
3) full  join or full OUTER join
Complete outer joins return all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains the data values for the base table.   

The

3, cross joins   
Cross joins return all rows in the left table, and each row in the left table is combined with all the rows in the right table. Cross joins are also called Cartesian product.     the table or view in the FROM clause can be specified in any order by an inner join or a full outer join, but the order of the table or view is important when you specify a table or view with a left or right outer join. For more information about using a left or right outer join to arrange tables, see Using outer joins.