SQL Server Foundation 05 multi-linked list queries and subqueries

Connection Query

It is important to note that the table name must be added before the field to confuse

1 -- Multi-table join queries and subqueries 2 Select *  from Dbo.stu_info, Dbo.sname2 3 -- query where the join rule is added 4 Select *  from where dbo.stu_info.sname= dbo.sname2. Name

Before using where

After using where

Using the logical operator and

1 SelectDbo.stu_info.sno,dbo.stu_info.sname,dbo.stu_info.sex,dbo.stu_info.depart2 , dbo.sname2.score,dbo.sname2. Name, Dbo.sname2.sno3  fromdbo.stu_info,dbo.sname24 whereDbo.stu_info.sname='Zhang San'5  andDbo.stu_info.sno=Dbo.sname2.sno6 Order  byDbo.sname2.scoredesc

Table alias Simplification statement

1 SelectA.sno,a.sname,a.sex,a.depart2 , b.score,b. Name, B.sno3  fromDbo.stu_info asA,dbo.sname2 asB4 whereA.sname='Zhang San'5  andA.sno=B.sno6 Order  byB.scoredesc

Multi-Table Connection query

Similar to the above, except that there are more N table names from the back of the n-1 and ...

Using the INNER JOIN connection query

In the WHERE clause the expression becomes bloated, in order to become clear and understandable, so ANSI SQL recommends using INNER join for multi-table joins

Syntax format:

SELECT * FROM table name 1

Inner JOIN table Name 2

On connection Rule 1

Inner JOIN table name 3

On Connection Rule 2

. . .   . . .

Inner JOIN table name n

On connection Rule n-1

Example:

1 --using INNER join2 Select *  fromDbo.stu_info asA3          Inner JoinDbo.sname2 asB4           onA.sno=B.sno5          whereA.depart='Psychology'6          Order  byB.scoredesc 

/**/is simpler than the above where + and

Advanced Connection Query

Here is a cursory summary, from the book: "21-Day Learning SQL Server" chapter 11th P226

1. Automatic connection Query

/**/

Select Table name 1.* from table name 1, table name 2 where table name 1. field = Table Name 2. field and table Name 2. field = ' field value '

2, Internal connection query

Equivalent connection: Table name 1.*= table name 2.*

Natural connections: Do not use * after select, but field names

Non-equivalent connection: >, >=, <, <=, <>, between ... Querying for values in certain ranges

3. Left Outer connection query

Syntax: SELECT * FROM table name 1 left outer join table name 2 on table name 1. field = Table Name 2. field

4, right outside the connection query

Syntax: SELECT * FROM table name 1 right outer join table name 2 on table name 1. field = Table Name 2. field

5. Full-Outer connection query

Syntax: SELECT * FROM table name 1 full outer join table name 2 on table name 1. field = Table Name 2. field

6. Cross-connect query

Syntax: SELECT * FROM table name 1 cross join table Name 2