MySQL's inner join,left join,right join detailed

First borrow the official explanation under:

Inner JOIN (equivalent connection): Returns only rows with the same junction field in two tables;

Left join: Returns records that include all records in the left table and the equivalent of the junction fields in the right table;

Right join: Returns records that include all records in the right table and the junction fields in the left table.

For example, we have Table1, table2, two tables.

Table1 table                                 table2 table---------------                  ----------------------ID     name                     ID      score1      lee                      1       902      Zhang                    2       100

4      Wang                     3       70

　　

SQL code

CREATE TABLE table1 (ID int,name varchar); CREATE TABLE table2 (id int,score int); Insert INTO table1 (id,name) VALUES (' 1 ', ' Lee '), (' 2 ', ' Zhang '), [' 4 ', ' Wang '), insert into table2 (Id,score) VALUES (' 1 ', 90) , (' 2 ', 100), (' 3 ', 70);

　　

　　

The following are performed in Query Analyzer

One, outer connection
1. Concept: Includes a LEFT outer join, a right outer join, or a full outer join

2. Left-side connection: outer JOIN
(1) The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null (NULL).
(2) SQL statements

SELECT * FROM table1 left joins table2 on Table1.id=table2.id;

-------------Results-------------

NOTE: All clauses that contain table1, return table2 corresponding fields according to specified criteria, non-conforming null display

3. Right Join
(1) A right outer join is a reverse join of a left outer join. All rows of 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 will be returned for left table.
(2) SQL statements

SELECT * FROM table1 right join table2 on table1.id=table2.id;

-------------Results-------------

NOTE: All clauses that contain table2, return table1 corresponding fields according to specified criteria, non-conforming null display

4. Complete outer join: Full JOIN or outer join
(1) A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
(2) SQL statements

SELECT * FROM table1 full join table2 on table1.id=table2.id;

-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370
------------------------------
Note: Returns the left and right connected and (see top

Second, internal connection
1. Concept: An inner join is a join that compares the values of the columns to be joined by comparison operators

2. Internal connection: Join or INNER JOIN

3.sql statements

SELECT * FROM table1 join table2 on table1.id=table2.id;

-------------Results-------------

Note: only table1 and table2 columns that match the criteria are returned

4. Equivalent (same as the following execution effect)

A:select a.*,b.* from table1 a,table2 b where a.id=b.id; B:select * FROM table1 Cross join Table2 where table1.id=table2.id; (Note: After cross join conditions can only be used where, not on)

　　

Three, cross-connect (full)

1. Concept: A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records)

2. Cross-Joins: crosses join (without conditions where ...)

3.sql statements

SELECT * FROM table1 cross join Table2;

-------------Results-------------

Note: Returns the 3*3=9 record, which is the Cartesian product

4. Equivalent (same as the following execution effect)

A:select * from Table1,table2;

　　

MySQL's inner join,left join,right join detailed