MySQL left JOIN, Inner join instance Tutorial

MySQL left JOIN, Inner join instance Tutorial
Left JOIN, Inner join of the relevant content, very practical, for the understanding of the principles and specific applications are very helpful!
First, take a look at some of the simplest examples.

Example

Table A
Aid Adate
1 A1
2 A2
3 A3

TableB

Bid Bdate
1 B1
2 B2
4 B4
Two table a,b connected, to remove fields with the same ID
SELECT * from a INNER join B on a.aid = B.bid This is the only matching data to be fetched.
At this point, the removal is:
1 A1 B1
2 A2 B2

So the left join means:
SELECT * from a LEFT join B on a.aid = B.bid
First remove all the data from table A and then add the data that matches the A,b
At this point, the removal is:
1 A1 B1
2 A2 B2
3 A3 NULL characters

There is also the right join
It means to first remove all the data from table B and then add the data that matches the a,b.
At this point, the removal is:
1 A1 B1
2 A2 B2
4 NULL character B4

Left JOIN or left OUTER join.
The result set of a left outer join consists of 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

Two. Left Join/right Join/inner Join operation Demo

Table A records as follows:
AID Anum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

Table B is recorded as follows:
BID bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

The experiment is as follows:
1. Left Join
The SQL statement is as follows:
SELECT * from A
Left JOIN B
On a.aid = B.bid
The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(The number of rows affected is 5 rows)

Results show:
The left join is based on the records of a table, and a can be viewed as the left-hand table, B can be viewed as the right table, and left table.
In other words, the records in the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
b tables where the records are insufficient are null.

2. Right Join
The SQL statement is as follows:
SELECT * from A
Right JOIN B
On a.aid = B.bid
The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(The number of rows affected is 5 rows)

Results show:
With a closer look, you will find that the result of the left join is just the opposite, this time based on the right table (B), where a table is deficient with null padding.

3.inner Join
The SQL statement is as follows:
SELECT * from A
Innerjoin B
On a.aid = B.bid
The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404

Results show:
Obviously, only the record of A.aid = B.bid is shown here. This shows that inner join is not based on who, it only shows the records that match the criteria.

-----------------[below is a bit of information on the Web]------------------
The left JOIN operation is used to combine records from the source table in any from clause. Use the left JOIN operation to create a left-hand outer join. The left outer join will contain all records from the first (left) two tables, even if there are no records in the second (right) table that match the values.

Grammar:
From table1 left JOIN table2 on table1.field1 compopr table2.field2

Description
①table1, the table2 parameter is used to specify the name of the table to combine records with.
②field1, the Field2 parameter specifies the name of the field being joined. These fields must have the same data type and contain the same type of data, but they do not need to have the same name.
The ③COMPOPR parameter specifies the relational comparison operator: "=", "<", ">", "<=", ">=" or "<>".
④ if you want to join a field that contains Memo data type or OLE Object data type data in a inner JOIN operation, an error occurs.
Three. Related complex explanations and examples

Introduction: External and self-join inner join (equivalent connection) returns only the row left join with the join field equal in the two tables return (left-hand join) returns a record right join that includes all the records in the left table and the join fields in the right table (right-hand join) Returns a record that includes all records in the right table and the join fields in the left table on the specified table join fields and their relationships equals equal "=" expressions, returns True or false. When an expression returns True, the record is included in the query. ! External connections can only manipulate data that already exists in the database
Update (ctarticle as a left join Ctclass as C on a.classid = C.classid) left join Cttag as B on a.articleid = B.articleid
Set tag=tag+ ', B.articleid=a.articleid, B.classid=a.classid, B.nclassid=a.nclassid
where a.classid=23 and a.nclassid=0 and tagid are NOT null

Update (ctarticle as a LEFT join (Ctnclass as C left join Ctclass as D on c.classid = D.classid) on a.nclassid = C.nclassi D and a.classid = C.classid) left join Cttag as B on a.articleid = B.articleid set tag=d.class+ ' +c.nclass, b.articleid=a . ArticleID, B.classid=a.classid, B.nclassid=a.nclassid where a.classid=23 and a.nclassid=197;

Update action
The selection of data in left join connection
Insert into Cttag (ARTICLEID,CLASSID,NCLASSID) Select A.articleid,a.classid,a.nclassid from Ctarticle a LEFT join Cttag b o n A.articleid=b.articleid where B.articleid is null

The function of this statement is to display the entire contents of the primary table, inserting data into the data that is not in the secondary table
The main function is: Let the data reduce redundancy

Continuation in the example above
Select A.*, b.*, c.*, d.* from Cttag as D-left join ((Ctarticle as a left join Ctclass as B on A.classid=b.classid) left J Oin Ctnclass as C on A.nclassid=c.nclassid) on D.articleid=a.articleid;

Show all of the articles table, calling columns in the category table
Select A.*, b.*, c.* from (Ctarticle a LEFT join Ctclass B on A.classid=b.classid) left join Ctnclass C on A.NCLASSID=C.NC Lassid

, sometimes in the article table contains the data that is not in the individual category table, with this syntax you can read all the data of the article table
A for the article table, b for the main category, and C for subcategories

In the previous example, when appending data is selected, add the sky
Insert into Cttag (articleid,classid,nclassid,tag) Select a.articleid,a.classid,a.nclassid,d.class+ ' +c.nclass
From (Ctarticle as a-left join (Ctnclass C-left join Ctclass D in C.classid=d.classid) on A.classid=c.classid and A.nclass ID=C.NCLASSID) LEFT join Cttag as B on a.articleid = B.articleid where a.classid=4 and a.nclassid=154;

Connect n tables and append data to one of the tables, n=4
Insert into Cttag (articleid,classid,nclassid,tag) Select A.articleid,a.classid,a.nclassid,d.class+c.nclass
From (Ctarticle as a-left join (Ctnclass C-left join Ctclass D in C.classid=d.classid) on A.classid=c.classid and A.nclass ID=C.NCLASSID) LEFT join Cttag as B on a.articleid = B.articleid where a.classid=1 and a.nclassid=1;

Interpretation
Insert INTO Table 2 (column 1, column 2, column 3, column 4)
Select Alias A. Column 1, alias A. Column 2, alias A. Column 3, alias d. column 4 plus alias c. Column 5
From (table 1 alias a Left connection (table 3 alias C left JOIN table 4 alias D in alias C.) column 2 equals alias d. Column 2 in alias A. Column 2 equals alias c. Column 2 and alias A. Column 3 = Alias C. Column 3) left JOIN table 2 alias B in alias A. Column 1 equals alias B. Column 1 Is there Alias A. Column 2=1 and alias A. Column 3=1

Connect two tables and append data to one of the tables
Insert into Cttag (ARTICLEID,CLASSID,NCLASSID)
Select A.articleid,a.classid,a.nclassid
From Ctarticle as a left join Cttag as B on a.articleid = B.articleid where a.classid=1 and a.nclassid=1;

Interpretation
Insert INTO Table 2 (column 1, column 2, column 3)
Select Alias A. Column 1, alias A. Column 2, alias A. Column 3
From table 1 alias a LEFT JOIN table 2 alias B in alias A. Column 1 equals alias B. Column 1 where alias a. Column 4=1 and alias A. Column 5=1

Left connection

Synchronizing data from two tables
Update ctarticle A INNER join Cttag b on a.articleid = B.articleid set B.classid=a.classid, B.nclassid=a.nclassid;

Interpretation
Update Table 1 alias a joins table 2 alias 2 in Alias A. Column 1 equals alias B. Column 1 set alias B. Column 2 Update to alias A. Column 2, alias B. Column 3 update to alias a. Column 3

Right outer join
Select A.*, b.* from Bunclass a right join Ctclass B on A.classid=b.classid where a.nclassid=20

Query alias A,b table, only match the contents of table B.

Add data to one of the join tables
INSERT into Cttag (tag, ArticleID) Select top 1 B.tag, A.articleid from Ctarticle as a left join Cttag as B on a.article id = B.articleid where a.articleid order by A.articleid Desc;

Usage in the workaround two
INSERT INTO bureply
Select B.*, A.classid, A.nclassid
From article as a INNER join reply as B on a.articleid = B.articleid
where classid=50;

Adaptation in practical applications
INSERT into Butag (tag, ArticleID, ClassID, Nclassid)
Select B.tag, A.articleid, A.classid, A.nclassid
From article as a INNER join tag as B on a.articleid = B.articleid
where classid=24;

Add data to another table
INSERT into Butag (tag, ArticleID)
Select B.tag, A.articleid
From article as a INNER join tag as B on a.articleid = B.articleid
where a.articleid<>false;

Interpretation
Add to receive table (column 1, column 2)
Select Alias B. Column 1, alias A. Column 2
From table 1 table name a joins table 2 table name B in alias a. Column C equals alias B. Column C
where alias A. column C is not equal to No

Adaptation in practical applications
Select B.tag, A.articleid, A.classid, A.nclassid
From article as a INNER join tag as B on a.articleid = B.articleid
where a.classid=24;

Inquire
Select B.tag, A.articleid
From article as a INNER join tag as B on a.articleid = B.articleid
where a.articleid<>false;

Interpretation
Select Alias B. column, alias A. Column
From table 1 alias a joins table 2 alias B in alias a. column C = alias B. Column C
where alias A. column C is not equal to No
Note: As is not necessary