SQL Server advanced content-subquery and table link overview and usage

A subquery is the result of another query based on the where clause in the query. The table link is to combine multiple tables into one table, but instead of merging result sets like union, table links can merge different tables and share fields. If you are interested, you can understand this article.

A subquery is the result of another query based on the where clause in the query. The table link is to combine multiple tables into one table, but instead of merging result sets like union, table links can merge different tables and share fields. If you are interested, you can understand this article.

1. subquery Concept
(1) the judgment in the where clause of the query is based on the results of another query. This constitutes an external query and an internal query. This internal query is a self-query.
(2) Self-query category
1) Independent subquery
-> Independent single value (scalar) subquery (=)
The Code is as follows:
Select
TestID, stuID, testBase, testBeyond, testPro
From Score
Where stuID = (
Select stuID from Student where stuName = 'kencery'
)

-> Independent multi-value subquery (in)
The Code is as follows:
Select
TestID, stuID, testBase, testBeyond, testPro
From Score
Where stuID in (
Select stuID from Student where stuName = 'kencery'
)

2) related subqueries
(3) Notes for writing subqueries
1) The subquery is wide in parentheses. If necessary, you need to take an alias for the table and use "as name.
2. Table join \
(1) A table link combines multiple tables into one table, but does not merge result sets like union. However, table links can merge different tables, and share fields.
(2) cross join)
1) create two tables
The Code is as follows:
Use Test
Go
Create table testNum1
(
Num1 int
);
Create table testNum2
(
Num2 int
);
Insert into testNum1 values (1), (2), (3)
Insert into testNum2 values (4), (5)

2) Execute SQL statements for cross-join
Select * from testNum1 cross join testNum2
3) Annotation
Cross join is to match all the data in the first table with all the data in the second table one by one to form a new table.
4) Implementation of Self-Crossover
Execute the insert SQL statement:
The Code is as follows:
Insert into testNum1 values (4), (5), (6), (7), (8), (9), (0)

Execute an SQL statement that is self-crossover:
The Code is as follows:
Select t1.num1, t2.num2 from testNum1 as t1 cross join testNum2 as t2

5) Another method:
Select * from testNum1 and testNum2 are not recommended. First, there is a new syntax. The defect is that the comma is not clear, and this syntax can be used with both inner and outer connections, if the join statement is used, an error can be reported when the syntax is incorrect. However, some syntax errors may be interpreted as cross-join by SQL Server and the check of this syntax is skipped.
(3) table join
1) The internal link adds a constraint on the basis of the cross connection.
2) Syntax: select * from table 1 inner join table 2 on table 1. Field = TABLE 2. Field
The Code is as follows:
Selects1.stuID,
S1.stuName,
S1.stuSex,
S2.testBase,
S2.testBeyond
From Student as s1
Inner join Score as s2
On s1.stuID = s2.stuID
Where s1.stuIsDel = 0;

(4) table join Outer Join
1) execute the following SQL statement
The Code is as follows:
Create table tblMain
(
ID int,
Name nvarchar (20 ),
Fid int
);
Create table tblOther
(
ID int,
Name nvarchar (20)
)
Insert into tblMain values (1, 'zhang san', 1), (2, 'Li si', 2)
Insert into tblOther values (1, 'c ++ '), (2,'. net'), (3, 'java ')
Select * from
TblMain as t1
Inner join
TblOther as t2
On
T1.fid = t2.id

2) based on the internal connection, when doing one thing, it is to display the Java in tblOther. At this time, it is necessary to use an external connection, with a left outer connection and a right outer connection.

3) What is the difference between left and right connections ?? The difference is that ** the connection is based on ** tables. Based on the internal connection, the information of the table without data must be displayed for users to view, the master table is the table to be displayed. The outer left join and the outer right join are the left table in the preceding table, the right table in the following table, left join and right join.
4) if you execute the following SQL statement again, Java in the tblOther table is displayed.
The Code is as follows:
Select * from
TblMain as t1
Right join tblOther as t2
On t1.fid = t2.id