SQL outer link Operation summary inner join left join right join

Database Operation statement
7. Outer Join-cross Query
7.1 Query
7.2 equijoin
7.3 right Outer Join
7.4 left Outer Join
7.5 update operations

Introduction:

External Connection and self-connection
Inner join (equivalent join) returns only rows with equal join fields in two tables.
Left join returns records that include all records in the left table and join fields in the right table.
Right join returns records that include all records in the right table and the joined fields in the left table.
On specifies the equal sign "=" expression of the joined fields and their relationships between tables. true or false is returned. When the expression returns true, the query contains the record.
! An external connection can only operate on data that already exists in the database.

7.5.2

Program code
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


7.5.1

Program code
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;


7.5 update operations

74.5 filter data in left join

Program code
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.

7.4.4.1 continuation in the previous example

Program code
Select a. *, 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;


7.4.4 show all in the Document Table and call the columns in the category table

Program code
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.

7.4.3 in the preceding example, add a space when appending data.

Program code
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;


7.4.2 connect N tables and append data to one of them, N = 4

Program code
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

7.4.1 connect two tables and append data to one of them

Program code
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

7.4. Left join

7.3.1 synchronize data from two tables

Program code
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

7.3 right Outer Join

Program code
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.

7.2.3 add data to a connection table

Program code
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;


7.2.2 usage 2 in the work und

Program code
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;


7.2.1 flexibility in practical application

Program code
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;


7.2 add data to other tables

Program code
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

7.1.1 practical application

Program code
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;


7.1 Query

Program code
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

7. Outer Join-cross Query
Tip: Pay attention to the same columns in the table.