SQL Server advanced content subquery and table Link

1. Subquery Concept

(1)It is under QueryWhereThe judgment in the clause is based on the results of another query, which 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(=)

  1   select   2   3   testid, stuid, testbase, testbeyond, testpro   4   5   from   score   6   7   where  stuid =  (  8   9   select  stuid  from  Student  where  stuname =  'kencery '  10   11 ) 

-> independent multi-value subquery (in)

  1   select   2   3   testid, stuid, testbase, testbeyond, testpro   4   5   from   score   6   7   where  stuid  in   (  8   9   select  stuid  from  Student  where  stuname =  'kencery '  10   11 ) 

2) related subqueries

(3) Notes for writing subqueries

1) The subquery is wide with a parentheses. If necessary, you need to take an alias for the table and use"AsName.

2.Table connection \

(1)The table link is to combine multiple tables into one table, but notUnionMerge result sets, but table links can merge different tables and share fields.

(2)Table join (CrossJoin)

1) create two tables

 1   Use test  2  3   Go  4   5   Create Table testnum1  6   7   (  8   9 Num1 Int  10   11   );  12   13   Create Table testnum2 14   15   (  16   17 Num2 Int  18   19   );  20   21 Insert into testnum1 values ( 1 ),( 2 ),( 3  )  22  23 Insert into testnum2 values ( 4 ),( 5 )

2) execute the cross-joinSQLStatement

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

InsertSQLStatement:

Insert into testnum1 values (4), (5), (6), (7), (8), (9), (0)

Execute self-CrossoverSQLStatement:

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 table1Inner join table2On Table1.Field=Table2.Field

 1   Select s1.stuid,  2   3   S1.stuname,  4   5   S1.stusex, 6   7   S2.testbase,  8   9   S2.testbeyond  10   11           From Student As  S1  12   13 Inner join score As  S2  14   15 On s1.stuid = S2.stuid  16   17       Where S1.stuisdel = 0 ;

(4) table join Outer Join

1) execute the followingSQLStatement

 1   Create Table tblmain  2   3   (  4   5 IDInt  ,  6   7 Name nvarchar ( 20  ),  8   9 FID Int  10   11   );  12   13   Create Table tblother  14   15  (  16   17 ID Int  ,  18   19 Name nvarchar ( 20  )  20   21   )  22   23 Insert into tblmain values ( 1 , ' Zhang San  ' , 1 ),( 2 , '  Li Si  ' , 2  )  24   25 Insert into tblother values ( 1 , '  C ++  ' ),( 2 ,'  . Net  ' ),( 3 , '  Java  '  )  26   27   Select * From   28   29 Tblmain As  T1  30  31   Inner join  32   33 Tblother As  T2  34   35   On  36   37 T1.fid = t2.id

 

2) based on the internal connection, one thing to do isTblotherInJavaIt is also shown that the external connection is used at this time, and the external connection has the left outer connection and the right outer connection.

3) What is the difference between left and right connections ?? The difference is**The connection is**The table is the master table. Based on the internal connection, the information of the table without data is displayed for users to view. Then, the master table is the table to be displayed. The left Outer Join and the right outer join are respectively in the front table, the left table, the right table in the back table, and the left join is usedLeftJoin, used for connectionRightJoin.

4)Execute the followingSQLStatement.TBLIn the other tableJava.

1 Select*From 2 3TblmainAsT14 5Right join tblotherAsT26 7On t1.fid = t2.id
 

 

 

 

Believe in yourself, you are the next miracle (kencery)