Mysql Left Join, Inner Join instance tutorial

Mysql Left Join, Inner Join instance tutorial
Left Join and Inner Join are useful for understanding the principles and specific applications!
I. Let's take a look at some of the simplest examples.

Example

Table
Aid adate
1 a1
2 a2
3 a3

TableB

Bid bdate
1 b1
2 b2
4 b4
Two tables a and B are connected. fields with the same id must be retrieved.
Select * from a inner join B on a. aid = B. bid this is only used to retrieve matching data.
In this case, the following information is taken:
1 a1 b1
2 a2 b2

Then left join refers:
Select * from a left join B on a. aid = B. bid
First, retrieve all the data in Table a, and then add the data that matches table a and table B.
In this case, the following information is taken:
1 a1 b1
2 a2 b2
3 a3 null characters

Right join is also available.
This means that all data in Table B is retrieved first, and then the data matching a and B is added.
In this case, the following information is taken:
1 a1 b1
2 a2 b2
4 blank characters b4

Left join or left outer join.
The result set of the left outer Join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selected list columns in the right table in the row of the associated result set are null.

2. left join/right join/inner join Operation demonstration

Table A records the following:
AID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

Table B records the following:
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
LEFT JOIN B
On a. aID = B. bID
The result is 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
(The number of affected rows is 5)

Result description:
Left join is based on the records of table A. A can be seen as the left table, B can be seen as the right table, and left join is based on the left table.
In other words, the records in the left table (A) are all expressed, while the right table (B) only displays records that meet the search criteria (in this example:. aID = B. bID ).
All records in Table B are NULL.

2. right join
The SQL statement is as follows:
SELECT * FROM
RIGHT JOIN B
On a. aID = B. bID
The result is as follows:
AID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL 8 2006032408
(The number of affected rows is 5)

Result description:
After careful observation, we will find that the result of left join is exactly the opposite. This time, it is based on the right table (B) and is filled with NULL when table A is insufficient.

3. inner join
The SQL statement is as follows:
SELECT * FROM
INNERJOIN B
On a. aID = B. bID
The result is as follows:
AID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404

Result description:
Obviously, only the records of A. aID = B. bID are displayed here. This indicates that inner join is not based on WHO, and only records meeting the conditions are displayed.

----------------- [The following are some online materials] ----------------
The left join operation is used to combine records of the source table in any FROM clause. Use the left join operation to create a LEFT Outer JOIN. The outer join on the left contains all records from the first (left) two tables, even if there is no record with consistent values in the second (right) table.

Syntax:
FROM table1 left join table2 ON table1.field1 compopr table2.field2

Note:
① The table1 and table2 parameters are used to specify the names of the tables whose records are to be combined.
② The field1 and field2 Parameters specify the names of joined fields. These fields must have the same data type and contain the same data type, but they do not need the same name.
③ The compopr parameter specifies the relational comparison operator: "=", "<", ">", "<=", ">=" or "<> ".
④ If you want to JOIN a field that contains the Memo data type or OLE Object data type in the inner join operation, an error will occur.
Iii. Complex explanations and Examples

Introduction: External join and self-join inner join (equivalent join) only return rows with equal join fields in two tables left join (left join) returns the record right join (right join) that includes all records in the left table and joined fields in the right table) returns the equal sign (=) expression of the joined Field and Its Relation between the specified table on which all records in the right table are equal to the joined field in the left table. returns true or false. if the expression returns true, the query contains the record .! An external connection can only operate on 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 is not null

Update (ctarticle as a left join (ctnclass as c left join ctclass as d on c. classid = d. classid) on. nclassid = c. nclassid and. classid = c. classid) left join cttag as B on. articleid = B. articleid set tag = d. class + ''+ c. nclass, B. articleid =. articleid, B. classid =. classid, B. nclassid =. nclassid where. classid = 23 and. nclassid= 197;

Update operation
Filter data in left join
Insert into cttag (articleid, classid, nclassid) select. articleid,. classid,. nclassid from ctarticle a left join cttag B on. articleid = B. articleid where B. articleid is null

// This statement function is used to display all the content of the master table and insert the data that is not in the secondary table.
// The main function is to reduce data redundancy.

Continuation in the previous example
Select. *, B. *, c. *, d. * from cttag as d left join (ctarticle as a left join ctclass as B on. classid = B. classid) left join ctnclass as c on. nclassid = c. nclassid) on d. articleid =. articleid;

Show all the columns in the Document Table and call the columns in the category table
Select. *, B. *, c. * from (ctarticle a left join ctclass B on. classid = B. classid) left join ctnclass c on. nclassid = c. nclassid

// Function. Sometimes, an article table contains data that is not present in an individual category table. You can use this syntax to read all the data in the article table.
// A is the document table, B is the main category, and c is the subcategory.

In the same example, add a space when appending data.
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 on c. classid = d. classid) on. classid = c. classid and. nclassid = c. nclassid) left join cttag as B on. articleid = B. articleid where. classid = 4 and. nclassid= 154;

Connect n tables and append data to one of them, 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 on c. classid = d. classid) on. classid = c. classid and. nclassid = c. nclassid) left join cttag as B on. articleid = B. articleid where. classid = 1 and. nclassid = 1;

// Explanation
Insert to 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, alias c. Column 5
From (Table 1 alias a left join (Table 3 alias c left join Table 4 alias d in alias c. column 2 equals to alias d. column 2) in alias. column 2 equals to alias c. column 2 and alias. column 3 = alias c. column 3) join table 2 on the left. Alias B is in alias. column 1 equals to alias B. column 1 is alias. column 2 = 1 and alias. column 3 = 1

Connect two tables and append data to one of them.
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;

// Explanation
Insert to 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 join

Synchronize 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;

// Explanation
Update Table 1 alias a join Table 2 alias 2 in alias. column 1 equals to alias B. column 1 sets the alias B. column 2 is updated to alias. column 2, alias B. column 3 is updated to alias. column 3

Outer right connection
Select a. *, B. * from bunclass a right join ctclass B on a. classid = B. classid where a. nclassid = 20

Query aliases a and B, and only match the content in table B.

Add data to a connection table
Insert into cttag (tag, articleid) select top 1 B. tag,. articleid from ctarticle as a left join cttag as B on. articleid = B. articleid where. articleid order by. articleid desc;

Work und 2
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;

Flexibility in practical application
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 other tables
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;

// Explanation
Add to receiving table (column 1, column 2)
Select alias B. Column 1, alias a. Column 2
From table 1 Table Name a join Table 2 table name B in alias a. Column c equals alias B. Column c
Where is the alias? a. Column c is not equal to no

Flexibility in practical application
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;

Query
Select B. tag, a. articleid
From article as a inner join tag as B on a. articleid = B. articleid
Where a. articleid <> false;

// Explanation
Select alias B. Column, alias a. Column
From table 1 alias a join Table 2 alias B in alias a. Column c = alias B. Column c
Where is the alias? a. Column c is not equal to no
Note: as is not necessary