Shulou(Shulou.com)06/01 Report--

1.2 Summary of the content of this article

1.3 Summary of the content of this article

In the SQL statement, table joins can be divided into single table joins, two table joins and more than two table joins according to the number of tables. In this article, two table joins are mainly explained, and other multi-table joins are the same.

There are many kinds of table joins. This article mainly talks about cross-joins, inner joins, outer joins (left outer joins, right outer joins, full joins), and self-joins.

1.4 Test sample tables and Sql in this chapter

Business scenario: there are two tables, which are divided into customer table Customers and customer order table Orders,SQL statement as follows:

Create a Customes and initialize it

-- CREATE TABLE CustomersCREATE TABLE Customers (CustID VARCHAR (50) NOT NULL,-- customer ID CustName VARCHAR (50),-- customer name CustCompany VARCHAR (50)-- customer company)-- Initial CustomersINSERT INTO Customers VALUES ('SXN-DD-01',' Zhao Wu','A') INSERT INTO Customers VALUES ('SXN-DD-02',' Liu Yang','B') INSERT INTO Customers VALUES ('SXN-DD-03',' Zhang Yongwei','C') INSERT INTO Customers VALUES ('SXN-DD-04' 'Li Longfei','D') INSERT INTO Customers VALUES ('SXN-FF-01',' Deng Hua','E') INSERT INTO Customers VALUES ('SXN-HH-01',' Zhang Taoming','F')

The query results are as follows:

Create the Order table and initialize it

CREATE TABLE Orders (CustID VARCHAR (50) NOT NULL,-- customer ID OrdetID VARCHAR (50)-order ID)-- Initial OrdersINSERT INTO Orders VALUES ('SXN-DD-01','SCCCCFFFFFSSOX002') INSERT INTO Orders VALUES (' SXN-DD-02','SCCCCFFFFFSSOX0X2') INSERT INTO Orders VALUES ('SXN-DD-03','') INSERT INTO Orders VALUES (' SXN-DD-04','') INSERT INTO Orders VALUES ('SXN-DD-05','SCCCCFFFFFSSOX0H2') INSERT INTO Orders VALUES (' SXN-DD-06','')

The query results are as follows:

2 problem introduction

Q1: write a query that generates a sequence of integers from 1 to 1000.

3 Cross connection

3.1 SQL examples and sample results

SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetIDFROM Customers AS CCROSS JOIN Orders AS O

Results:

3.2 Analysis of sample results

Cross join uses the keyword CROSS JOIN to query, and the result of the query is Cartesian product. From the above result, we can see that the query result has 36 rows of data, because there are 6 records in Customers table and 6 records in Orders table. 6X6=36

3.3 Summary

a. Logically, a cross-connect is the simplest connection.

b. Cross-connect implements only one logical processing step-Cartesian product

c. Operation: operate on two input tables, join them together, and generate the Cartesian product of both, that is, each row of one input table is matched with all rows of another table. If one table has m rows and the other table has n rows, you will get the result set of m x n rows.

d. Structure:

SELECT tb1.tb1ConumName,tb2.tb2ConumName

FROM table1 AS tb1

CROSS JOIN table2 AS tb2

e. Keyword used for cross-connect: CROSS JOIN

f. The result set generated by the cross-join is a virtual table, and the columns in the virtual table are directly derived from the two tables participating in the join.

4 Internal connection

4.1 SQL examples and sample results

SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID FROM Customers AS C INNER JOIN Orders AS O ON C.CustID=O.CustID

Results:

4.2 example result analysis

The inner connection adds the filter condition ON on the basis of the cross connection. For example, using Customers.CustID=Orders.CustID as the filter condition in the above example, the result is obvious.

4.3 Summary

The inner join rule is Cartesian product + user predicate filtering: it first performs Cartesian product operation on two input tables like a cross join, and then filters the results according to the predicate specified by the user.

5 external connection

5.1 SQL example and sample results (only the left outer connection is analyzed, because the principles of right connection and full connection are the same)

SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID FROM Customers AS C LEFT OUTER JOIN Orders AS O ON C.CustID=O.CustID

Results:

5.2 example result analysis

As above, the Cutomers table is used as the left reserved table, and the join right table Orders column CustID is missing SXN-FF-01 and SXN-HH-01. In order to use the left reserved table as the benchmark, fill it with the NULL placeholder.

5.3 Summary

a. External connections: Cartesian product + ON filtering + external lines

b. In an outer join, to mark a table as reserved, you can use the keywords LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN between table names, where the OUTER keyword is optional. The LEFT keyword indicates that the rows of the left table are reserved, the RIGHT keyword indicates that the rows of the right table are reserved, and the FULL keyword indicates that the rows of the left and right tables are reserved.

c. The third logical query processing step of an outer join is to identify those rows in the reserved table that cannot be found in another table according to the ON condition, and then add these rows to the results generated by the first two steps of the join. For those columns from the joined non-reserved table, the appended columns use NULL as the placeholder

d. From the point of view of the outer join retention table, it can be considered that the data rows in the outer join result include two kinds of internal rows and external rows. Inner rows are those rows that can be found on the other side of the connection according to the conditions in the ON clause, while outer rows are those that cannot be found. The inner join returns only the inner row, and the outer join returns both the inner row and the outer row.

e. When using outer joins, do you specify the join condition in the ON clause of the query or in the WHERE clause? Considering this problem in terms of the rows in the outer join reserved table, the filter condition in the ON clause is not final, in other words, the condition in the ON clause does not ultimately determine whether some rows in the reserved table will appear in the result, but only determines whether it can match some rows in the other side of the table. So, when you need to express a non-final condition (that is, this condition only determines which rows can match the non-reserved table), specify the join condition in the ON clause, apply the filter after the external row is generated, and want the filter condition to be final, you should specify the condition in the WHERE clause

6 self-connect

6.1 SQL examples and sample results

SELECT C1.CustID AS C1CustID,C1.CustName AS C1CustName,C1.CustCompany AS C1CustCompany,C2.CustID,C2.CustName,C2.CustCompany FROM Customers AS C1 JOIN Customers AS C2 ON C1.CustID=C2.CustID

Results:

6.2 example result analysis

For example, the above example is the application of self-connection in internal connection, but the application in other connections is not an example, which is relatively simple.

6.3 Summary

a. Self-join gives a different alias to a single table and connects through an alias

b. Self-connection can be used for other connections

b. Self-connection can be regarded as a special case of cross-connection, inner connection, outer connection and so on.

7 answers to questions

Q1:KEY

CREATE TABLE Digits (digit int not null primary key)-Initial testing data for DigitsINSERT INTO Digits VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)-- QuerySELECT D3.digit*100+D2.digit*10+D1.digit+1 AS nFROM Digits AS D1CROSS JOIN Digits AS D2CROSS JOIN Digits AS D3ORDER BY n

8 references

[01] Microsoft SqlServer 2008 Technical Insider: T-SQL language Foundation

[02] Microsoft SqlServer 2008 technical insider: T-SQL query

Summary

The above is the SqlServer table connection tutorial introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me. The editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.