"Turn" MySQL 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.

Like we have XS, CJ, two tables.

XS Table CJ Table

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

ID Name ID Score

1 Sheets 31 96

2 Lee 42 80

3 86

SQL code

1 SELECT * from ' xs ' INNER JOIN ' cj ' on xs.id = Cj.id

Return

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

ID Name ID Score
1 Sheets 31 96
2 Lee 42 80

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

SQL code

2 SELECT * from ' xs ' left JOIN ' cj ' on xs.id = Cj.id

Return

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

ID Name ID Score
1 Sheets 31 96
2 Lee 42 80

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

SQL code

3 SELECT * from ' xs ' right JOIN ' cj ' on xs.id = Cj.id

Return

ID Name ID Score
1 Sheets 31 96
2 Lee 42 80
NULL NULL 3 86

There is another way of inner join, both of which are equivalent and are equivalent connections

SQL code

4 SELECT * from ' xs ', ' CJ ' where xs.id = Cj.id

MySQL multi-table connection query inner join, left JOIN, right join, full join, cross join

Keyword: mysql inner join left join right join full join cross Join

Inner join,full outer join,left join,right jion
Internal connection inner JOIN two tables satisfy the combination
Full outer the same combination of the two tables, a table has, B table does not have data (shown as null), the same B table has
Table A does not display as (null)
A table left JOIN B table left JOIN, based on a table, a table of all the data, B table has a combination. Nothing is null
A RIGHT join B table, based on B table, all the data of table B, the combination of a table. Nothing is null

Execution in Query Analyzer:
--Build Table Table1,table2:
CREATE TABLE table1 (ID int,name varchar (10))
CREATE TABLE table2 (ID int,score int)
INSERT INTO table1 Select 1, ' Lee '
INSERT INTO Table1 Select 2, ' Zhang '
INSERT INTO table1 Select 4, ' Wang '
Insert INTO table2 Select 1,90
Insert INTO table2 Select 2,100
Insert INTO table2 select 3,70
such as table
-------------------------------------------------
table1|table2|
-------------------------------------------------
idname|idscore|
1lee|190|
2zhang|2100|
4wang|370|
-------------------------------------------------

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 join table2 on Table1.id=table2.id
-------------Results-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
------------------------------
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-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
NULLNULL370
------------------------------
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-------------
Idnameidscore
------------------------------
1lee190
2zhang2100
------------------------------
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: The Add condition after cross join can only be used where, cannot be used)

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-------------
Idnameidscore
------------------------------
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
------------------------------
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

"Turn" MySQL inner join,left join,right join detailed