SQL External link Operations summary INNER join LEFT join right Join_mssql

Database Action Statement
7. External connection--cross-check
7.1 Query
7.2 Equivalent connections
7.3 Right Outer connection
7.4 Left outer connection
7.5 Update operation

Brief introduction:

External joins and self-joins
INNER JOIN (equivalent join) returns only rows with the same join field in two tables
Left JOIN, which returns records that include all records in the left table and the join fields in the right table
Right join returns records that include all the records in the right table and the join fields in the left table
On specifies the equal sign "=" Expression for the Join field and its relationship between the tables, returning 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

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


7.5 Update operation

74.5 filtering of data in left connection

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

The continuation of 7.4.4.1 in the above example

Program code
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 join Ctnclass as C on A.ncl ASSID=C.NCLASSID) on D.articleid=a.articleid;


7.4.4 shows all of the articles table, calling columns in the category table

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

7.4.3 in the previous example, select Append data to add the above grid

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


7.4.2 joins N tables and appends data to one of the tables, 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 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

7.4.1 connects two tables and appends data to one of the tables

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;

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

7.4. Left connection

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;

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

7.3 Right Outer connection

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

7.2.3 add data to one of the join tables

Program code
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 7.2.2 workaround two

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

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

7.1.1 in 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;

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

7. External connection--cross-check
Tip: Note the same columns in the table