T-SQL query statement (III): Multi-Table query

SQL query statement "3": Multi-Table query (also called connection query, which is a connection query based on two tables)
If a query needs to operate on multiple tables, it is called a connection query, and the result set or result of the connection query is called a connection between tables.
In fact, connection query queries data through the Association of common columns between tables. It is the most important feature of relational database queries.
Select Table 1. Field name 1, Table 2. Field name 2 ,...
From table 1, table 2
Where connection Condition
The connection syntax format for the from clause defined by the SQL-92 standard is:
From table name join_type table name [ON (connection condition)]
The on clause in the Join Operation specifies the join condition, which is composed of columns, comparison operators, and logical operators in the connected table.
.
Connection query category:
1. Self-join query, connecting to the same table
2. Internal Connection query, <divided into three types: natural connection, equivalent connection, and non-equivalent connection>
3. External Connection query, <divided into three types: left outer connection, right outer connection, and full outer connection>
4. Cross-join queries are also unconditional queries.
5. Joint Query

----------------------------
I. Self-connection query:

A table establishes a connection with itself.
Just like two separate tables, you can connect one row of a table to another row in the same table.
Example:
Query the records of all students whose scores are higher than those of students whose "101" course is selected,
And sorted by score from high to low.
Select X. * From sclass X, sclass y
Where X. CNO = '000000' and X. degree> Y. degree and Y. Sno = '000000' and Y. CNO = '000000''
Order by X. degree DESC
----------------------------
II. Inner join ):
Internal join is the most common connection method. It returns only the rows matching the relationship between two data sets.
Connect the rows that are located within the overlapping parts of two cross-datasets.
Inner join compares data in one or more columns between tables using comparison operators,
And list the data rows that match the connection conditions in these tables.
The inner connection query operation lists the data rows that match the connection condition according to the comparison method used,
It uses the comparison operator to compare the column values of connected columns.
Syntax:
Select field name list from table name [inner] Join table name on join condition [Where condition expression]
Example:
Select student table. Student ID, student table. Name, student table. class code, student table. course code, student table. Course score
From student table inner join student table
On student table. Student ID = student ID. Student ID
Where student table. class code = '000000''
Select X. Sno, X. sname, Y. CNO, Y. degree from student x
Inner join sclass y on X. Sno = Y. Sno where X. sclass = ''95 1''
Intranet connections are divided into three types:
1. equijoin:
Equivalent join means that tables are connected through the "Equal" relationship to generate a temporary table,
Then the temporary table is processed and the final result is generated. All columns in the connected table are listed in the query results,
Including duplicate columns.
(1) query the sno, cname, and degree columns of all students:
Select X. Sno, Y. cname, X. Degree
From score X, course y
Where X. CNO = Y. CNO
(2) query the average score of the courses selected in the "95033" class:
Select Y. CNO, AVG (Y. Degree) as "average score"
From student X, score y
Where X. Class = ''95033'' and X. Sno = Y. Sno group by Y. CNO
(3) query student and score table information of all students:
Select X. *, Y. * from student X, score y Where X. Sno = Y. SnO
Use equijoin to list authors and publishers in the same city in the authors and publishers tables:
Select * from authors as a inner join publishers as P on A. City = P. City
2. Unequal connections:
It means that the connection between tables is not "equal", but other relationships.
These operators include >,>=, <=, <,!> ,! <And <>.
(1) query the sno, CNO, and rank columns of all students:
Select SnO, CNO, rank
From score, grade
Where degree between low and UPP order by rank
(2)
Select X. Sno, X. sname, Y. CNO, Y. Degree
From student X, score y
Where X. Sno! = Y. SnO
3. natural connection:
Removing duplicate columns in an equijoin is a natural join.
(1)
Select X. Sno, X. sname, Y. CNO, Y. Degree
From student X, score
Where X. Sno = Y. SnO
Use a natural connection to delete duplicate columns (city and state) in the authors and publishers tables in the selection list ):
Select .*? P. pub_id? P. pub_name? P. Country
From authors as a inner join publishers as P on A. City = P. City

----------------------------
3. Outer Join ):
Unlike internal connections, external connections not only list rows that match the connection conditions,
Instead, the left table (when left Outer Join), right table (when right Outer Join), or two tables (when full outer join) are listed)
All data rows that meet the search criteria.
External connections are the expansion of internal connections. Apart from connecting the data rows that overlap the two data sets,
You can also return unmatched or all data in the Left or Right dataset as required.
That is, left Outer Join );
Right Outer Join (right Outer Join );
All connections (full outer join ).
Some data in the result set returned by the outer connection looks exactly the same as the data returned by the inner connection,
However, some data is different from the data returned by the internal connection. Some of these data rows are from the data in the table,
The other part is null values. The reason for these null values is that the other table does not contain data items that match the specified table.
When the inner connection is established, only the where search condition or having condition and
The row of the connection condition. When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions,
It also includes all data rows in the left table (when left Outer Join), right table (when right Outer Join), or two edge join tables (full outer join.
Note: The external connection is strong so that records that do not meet the conditions also appear in the result set.
Outer Join Syntax:
Select field name list from table name left | right | full [outer] Join table name on join Condition
External connections are divided:
1. Left Outer Join (left Outer Join or left join ):
The results table contains all records that meet the conditions in the first table.
If the record is matched on the connection condition, the second table returns the corresponding value; otherwise, the second table returns a null value.
Select student table. Student ID, student table. Name, student table. course code, student table. Course score
From student table left Outer Join student table
On student table. Student ID = student ID. Student ID
2. Right Outer Join (right Outer Join or right join ):
The result table contains all records that meet the conditions in the second table.
If the record is matched on the connection condition, the first table returns the corresponding value; otherwise, the first table returns a null value.
Select student table. Student ID, student table. Name, student table. course code, student table. Course score
From student table right Outer Join orders table
On student table. Student ID = student ID. Student ID
3. Full outer join (full outer join or full join ):
The results table contains all records that meet the conditions in the two tables.
If it is a tuple matched on the connection condition, the other table returns the corresponding value; otherwise, the other table returns a null value.
Select student table. Student ID, student table. Name, student table. course code, student table. Course score
From student table full outer join orders table
On student table. Student ID = student ID. Student ID
Example:
Select a. *, B. * from student as a left join sclass as B
On a. Sno = B. SnO and A. Sno = '000000''
Go
Select a. *, B. * from student as a right join sclass as B
On a. Sno = B. SnO and A. Sno = '000000''
Go
Select a. *, B. * from student as a full join sclass as B
On a. Sno = B. SnO
Go
----------------------------
4. Cross join:
Cross join does not use any join conditions to limit the result set. It combines the records of each table in the form of a Cartesian product,
Is to use the rows of the two data sources to combine in all possible ways,
That is, each row in the dataset must form a new row with each row in the other table.

For example, one table has three records, and the other table has four records. After a cross join operation, the result set is composed of 12 records.
Cross join does not have a where clause. It returns the Cartesian product of all data rows in the two joined tables,
The number of rows returned to the result set is equal to the number of rows that meet the query conditions in the first table multiplied by the number of rows in the second table.
The number of rows that meet the query conditions.
For example,
There are 6 types of books in the titles table, and 8 publishers in the publishers table,
The number of records retrieved by the following cross-join is 6*8 = 48.
Select * from student, sclass
Select * from student a cross join sclass order by A. SnO
----------------------------
5. Joint Query
The Union operator can combine the query result sets of two or more select statements into a result set for display,
That is, the Union query is executed.

The syntax format of union is:
Select_statement
Union [all] selectstatement
[Union [all] selectstatement] [… N]
Selectstatement is the SELECT query statement to be combined.
The all option combines all rows into the result set. If this item is not specified,
Duplicate rows in the Union query result set retain only one row.
During a joint query, the column title of the query result is the column title of the first query statement.
Therefore, to define a column title, it must be defined in the first query statement. To sort the Union query results,
You must also use the column name, column title, or column number in the first query statement.
When using the Union operator, make sure that each union query statement has the same number of expressions in the selection list,
And each query selection expression should have the same data type, or they can be automatically converted to the same data type
.
During automatic conversion, the system converts low-precision data types to high-precision data types.
In Union statements that contain multiple queries, the execution sequence is from left to right. Brackets can be used to change the execution sequence.

 

For example:
Query 1 Union (query 2 Union query 3)
Select topicbody, posttime from bbs_topic
Union all
Select replybody, posttime from bbs_reply
----------------------------
Sat. Complex Query

--- Composite connection
Select a. Student ID, A. Name, B. course code, B. Course score, C. Course name, D. Instructor code
From student Table A, student table B, Course table C, Teaching Table D
Where (A. Student ID = B. Student ID)
And (B. course code = C. course code)
And (C. course code = D. course code)
Connections with more than three tables
Select DBO. KB. XQ, DBO. kbk. kcmc, DBO. kbk. lbdh, DBO. kbk. jsmc, DBO. KB. JSE, DBO. BJ. BJ,
DBO. KB. JC, 2 as num, DBO. KB. ZC,
Case DBO. KB. DS when ''single ''then'' 1' 1' when'' dual ''then'' '2' when''' ''then'' '0'' end as DS,
DBO. KB. ZC1, DBO. KB. zc2
From DBO. KB inner join
DBO. BJ on DBO. KB. bH = DBO. BJ. bh inner join
DBO. kbk on DBO. KB. XQ = DBO. kbk. XQ and DBO. KB. bH = DBO. kbk. bh and
DBO. KB. kcdm = DBO. kbk. kcdm
Where (DBO. KB. JC = 1) or
(DBO. KB. JC = 3) or
(DBO. KB. JC = 5) or
(DBO. KB. JC = 7) or
(DBO. KB. JC = 9) or
(DBO. KB. JC = 11)

----------------------------
Summary:
No connection can be used to directly connect text, ntext, and image data columns,
However, these three columns can be indirectly connected.
For example:
Select p1.pub _ id? P2.pub _ id? P1.pr _ INFO
From pub_info as P1 inner join pub_info as p2

On datalength (p1.pr _ INFO) = datalength (p2.pr _ INFO)