1.2 summary of this article
1.3 summary of this article
In SQL statements, table connection can be divided into single table connection, two table connection and more than two table connection if it is divided according to the number of tables. In this article, we mainly explain the connection of two tables. The principle of other multiple table connection is the same.
There are many kinds of table join. This article mainly introduces cross join, inner join, outer join (left outer join, right outer join, full join), self join.
1.4 test sample table and SQL in this chapter
Business scenario: there are two tables: customers and orders. SQL statements are as follows:
Create customers and initialize
--CREATE TABLE Customers CREATE TABLE Customers ( Custid varchar (50) not null, -- customer ID Custname varchar (50), -- customer name Custcompany varchar (50) -- customer company ) --Initial Customers INSERT 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 Orders INSERT 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 to generate a sequence of integers from 1 to 1000.
3 cross connect
3.1 SQL examples and results
SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID FROM Customers AS C CROSS JOIN Orders AS O
3.2 analysis of example results
Cross join uses the keyword cross join to query, and the query result is Cartesian product. From the above results, we can see that there are 36 rows of data in the query result, because the customers table and orders table have 6 records respectively, 6×6 = 36;
a. Logically, cross connection is the simplest connection;
b. Cross connection only realizes one logical processing step, Cartesian product;
c. Operation: operate two input tables and connect them to generate Cartesian product of them. 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 has n rows, the result set of m x n rows will be obtained;
FROM table1 AS tb1
CROSS JOIN table2 AS tb2
e. Keywords used in cross connection: cross join;
f. The result set generated by 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 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
4.2 example result analysis
The inner connection is based on the cross connection with the filter condition on, which is used in the above example Customers.CustID=Orders . custid as the filter condition, the result is obvious.
The inner join rule is Cartesian product + user predicate filtering: it first performs Cartesian product operation on two input tables like cross join, and then filters the results according to the predicates specified by users;
5 external connection
5.1 SQL examples and results (only the left external connection is analyzed, because the principle of right connection and full connection is 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
5.2 example result analysis
As mentioned above, the cutomers table is used as the left reserved table, and the custid of the orders column in the right table is missing sxn-ff-01 and sxn-hh-01. In order to take the left reserved table as the benchmark, it is filled with null placeholders.
a. External connection: Cartesian product + on filter + external line;
b. In an external join, to mark a table as a reserved table, you can use the keywords left outer join, right outer join, and full outer join between the table names. The outer keyword is optional. The left keyword indicates that the rows in the left table are reserved, the right keyword indicates that the rows in the right table are reserved, and the full keyword indicates that the rows in the left and right tables are reserved;
c. The third logical query processing step of the outer join is to identify the 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 the columns from the joined non reserved table, null is used as a place holder for the columns in the appended outer row;
d. From the perspective of external join reservation table, we can think that the data rows in external join results include two kinds of internal rows and external rows. Internal rows are those that can be found on the other side of the join according to the conditions in the on clause, while external rows are those that cannot be found. Inner join only returns inner row, outer join returns inner row and outer row at the same time;
e. When using outer join, do you specify the join condition in the on clause of the query or in the where clause? In other words, the conditions in the on clause can not ultimately determine whether some rows in the reserved table will appear in the results, but only determine whether some rows in the other table can be matched. Therefore, when a non final condition needs to be expressed (that is, the condition only determines which rows can match the non reserved table), the join condition is specified in the on clause. When the filter is applied after the external row is generated and the filter condition is final, the condition should be specified in the where clause;
6 self connection
6.1 SQL examples and 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
6.2 analysis of example results
The above example is the application of self connection in internal connection. The application in other connections is not an example, which is relatively simple.
a. Self join takes different aliases for a single table and joins them through aliases;
b. Self connection can be used for other connections;
b. Self connection can be regarded as a special case of cross connection, inner connection and outer connection;
7 question answer
CREATE TABLE Digits ( digit int not null primary key ) --Initial testing data for Digits INSERT INTO Digits VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9) --Query SELECT D3.digit*100+D2.digit*10+D1.digit+1 AS n FROM Digits AS D1 CROSS JOIN Digits AS D2 CROSS JOIN Digits AS D3 ORDER BY n
 inside story of Microsoft sqlserver 2008: T-SQL language foundation
 technical insider of Microsoft sqlserver 2008: T-SQL query
The above is the introduction of SQL Server table connection tutorial, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the developeppaer website!
If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!