Sqlserver table connection tutorial (problem analysis)

Time:2020-9-16

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

result:

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;

3.3 summary

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;
d. Structure:
  SELECT  tb1.tb1ConumName,tb2.tb2ConumName
  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

result:

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.

4.3 summary

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

result:

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.

5.3 summary

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

result:

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.

6.3 summary

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

Q1:KEY


										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

8 references

[01] inside story of Microsoft sqlserver 2008: T-SQL language foundation

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

summary

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!

Recommended Today

How to share queues with hypertools 2.5

Share queue with swote To realize asynchronous IO between processes, the general idea is to use redis queue. Based on the development of swote, the queue can also be realized through high-performance shared memory table. Copy the code from the HTTP tutorial on swoole’s official website, and configure four worker processes to simulate multiple producers […]