The difference between a left join, right join, inner join, and full join

Internal Connection
INNER Join (equivalent connection):Only rows that are equal to the junction fields in two tables are displayed. This is the same effect as using Select to query multiple tables, so it is seldom used;

External connection:
Left JOIN: Displays all records in the left table on a left table basis, regardless of whether they match the association criteria, and the data in the right table shows only those records that match the association criteria, and the unmatched records are filled with null characters. The LEFT join is a shorthand for the left outer join, and the left join defaults to the outer property.


Right JOIN: Displays all records in the right table, regardless of whether they match the association condition, and the data in the left table shows only the records that match the association criteria, and the unmatched records are filled with null characters.

Full JOIN: Displays all records in multiple tables, and columns that do not match the associated condition are populated with null characters.

first, let's look at some of the simplest examples.
Example
Table A
Aid Adate
1 A1
2 A2
3 A3

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

Then the left join means:
SELECT * from a LEFT join B on a.aid = B.bid
First remove all the data from the a table, 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 character

There's also right join.
Refers to the first to remove all the data in the B table, and then add the data that matches the
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 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 values

Two. Left Join/right Join/inner Join operation Demo
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 statements are 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)

Result Description:
The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table.
In other words, the records of 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).
The low-record of table B is null.

2. Right Join
The SQL statements are 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)

Result Description:
Looking closely, you will find that the result of the left join is exactly the opposite, this time it is based on the right table (B), where a table is not enough to fill with null.

3.inner Join
The SQL statements are 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

Result Description:
Obviously, only a.aid = B.bid records are shown here. This shows that inner join is not based on who, it only shows records that match the criteria.

-----------------[below is a little 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 an outer join of the side. The left outer join will contain all of the records from the first (left) two tables, even if there are no records of the corresponding values in the second (right) table.

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 the records with.
②field1, the Field2 parameter specifies the name of the field being joined. And 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 "<>".
④ An error occurs when you join a field in a inner JOIN operation that contains data of Memo data type or OLE Object data type.

Three. Related complex explanations and examples

Summary: External joins and self-joins inner join (equivalent join) returns only rows that are equal to the junction fields in two tables the left join returns the records that include all the records in the left table and the equivalent of the junction fields in the right table right join Returns the equals "=" expression that includes all records in the right table and the junction fields in the left table on the specified table, and returns TRUE or false. When the 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 in 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 in 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
Filtering 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

This statement functions to display the entire contents of the main table, inserting data into the data not in the secondary table
The main role is: to reduce redundancy of data

Continuation in the above example
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 in the article table, call the 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

function, sometimes in the article table contains data that is not in the individual category table, using this syntax can read all the data of the article table
A is the article table, B is the main category, and C is a subcategory

Above, select Append data to add an overhead grid
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 A.classid=c.classid and A.nclass ID=C.NCLASSID) left joins Cttag as B on a.articleid = B.articleid where a.classid=4 and a.nclassid=154;

Joins n tables and appends 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 on C.classid=d.classid) on A.classid=c.classid and A.nclass ID=C.NCLASSID) left joins 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 is there 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 join table 2 alias 2 in Alias A. Column 1 equals alias B. Column 1 set alias B. Column 2 is updated to alias a. Column 2, alias B. Column 3 update to alias a. Column 3

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

The query alias, A, a, matches only 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;

Use two in a workaround
INSERT INTO bureply
Select B.*, A.classid, A.nclassid
From article as a inner joins reply as B on a.articleid = B.articleid
where classid=50;

Workarounds in real-world 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;


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

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

Workarounds in real-world 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 join 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

The difference between a left join, right join, inner join, and full join