SQL Server Cross-join Internal Connection

SQL Server Cross-join Internal Connection

Preface

At the beginning of this section, we will go to Join learning. We will learn more about connection in a step-by-step manner, including brief content and in-depth understanding.

CROSS JOIN)

Cross-join is the simplest connection type. Only one logical query processing phase-Cartesian product is executed for the cross join. For example, to operate two input tables, join and generate the Cartesian product of the two tables, that is, match each row of one table with all rows of the other table. Therefore, if one table has m rows and the other table has n rows, m * n rows will be returned. Let's talk about the example in the SQL Server 2012 tutorial.

SELECT C.custid, E.empidFROM Sales.Customers AS CCROSS JOIN HR.Employees AS EORDER BY E.empid

There are 91 rows of data in the Sales. MERs table, and 9 rows of data in the HR. Employees table, 819 (91*9) rows of data in the cross join data. The simple data is as follows.

Cross join can be represented by an example

The biggest purpose of cross-join is to generate a number table for other purposes. Let's take a look.

IF OBJECT_ID('dbo.Digits','U')IS NOT NULL DROP TABLE dbo.Digits;CREATE TABLE dbo.Digits( digit INT NOT NULL);

Insert 10 basic data records

USE TSQL2012GOINSERT INTO dbo.Digits  ( digit )VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )

Create a digital table

USE TSQL2012GOCREATE TABLE Nums( n INT NOT NULL PRIMARY KEY);

Insert 1 million pieces of data into a digital table using a cross join

USE TSQL2012GOINSERT INTO dbo.Nums(n)SELECT D6.digit * 100000 + D5.digit * 10000 + D4.digit * 1000 + D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS nFROM dbo.Digits AS D1 CROSS JOIN dbo.Digits AS D2 CROSS JOIN dbo.Digits AS D3 CROSS JOIN dbo.Digits AS D4 CROSS JOIN dbo.Digits AS D5 CROSS JOIN dbo.Digits AS D6ORDER BY n

INNER JOIN)

The internal join usage is as follows:

SELECT *FROM table1 AS t1INNER JOIN table2 AS t2 ON t1.Id = t2.Id

More data in the internal join return table

We will first give the following three test tables:

USE TSQL2012GOCREATE TABLE FirstTable (Col1 INT)CREATE TABLE SecondTable (Col1 INT)CREATE TABLE ThirdTable (Col1 INT)GOINSERT INTO FirstTable (Col1)VALUES (1), (2), (3), (NULL)GOINSERT INTO SecondTable (Col1)VALUES (1), (2), (3), (NULL)GOINSERT INTO ThirdTable (Col1)VALUES (2), (2), (2), (2), (2), (NULL)GO

(1) Equivalent Condition Query

SELECT f.Col1 fCol1FROM FirstTable f INNER JOIN SecondTable s ON s.Col1 = f.col1GO

(2) Non-equivalent condition Query

USE TSQL2012GOSELECT f.Col1 fCol1FROM FirstTable fINNER JOIN SecondTable s ON s.Col1 <> f.col1GO

We can also achieve the same effect using cross-join.

USE TSQL2012GOSELECT f.Col1 fCol1FROM FirstTable AS f CROSS JOIN SecondTable AS swhere s.Col1 <> f.col1GO

(3) Querying non-duplicate rows (NON-DISTINCT)

When we create the third test table, the inserted data is 5 2, and the data inserted in the first table is 1, 2, 3, respectively, in this case, is the result obtained by using the equijoin one 2 or five 2?

USE TSQL2012GOSELECT f.Col1 fCol1FROM FirstTable fINNER JOIN ThirdTable s ON s.Col1 = f.col1GO

The result we get is 5 to 2. Why is the equivalent condition used by internal join not 1 to 2? In fact, we can summarize it as follows:

Conclusion: The reason why the internal JOIN Operation returns more data than the actual table is that the result set returned by the internal JOIN operation is based on the JOIN operation in the query condition. If multiple rows meet the condition, multiple data entries are returned.

Internal Connection Security

When two tables use equivalent conditions for query, we have two methods.

ANSI SQL-92 writing

USE TSQL2012GOSELECT *FROM Sales.Orders AS SO INNER JOIN Sales.OrderDetails AS SOD ON SOD.orderid = SO.orderid

ANSI SQL-89 writing

USE TSQL2012GOSELECT *FROM Sales.Orders AS SO, Sales.OrderDetails AS SODWHERE SOD.orderid = SO.orderid

Although both writing methods can meet the needs, but SQL Server 2012 basic tutorial strongly recommended to use ANSI SQL-92 writing, why, because in the case of errors when writing ANSI SQL-89, at this point the parsing will not generate errors, and for the ANSI SQL-92 write law will, let's take a look at the ANSI SQL-89 writing problem.

USE TSQL2012GOSELECT COUNT (*) AS 'use the equivalent condition to query the total data rows 'FROM Sales. Orders as so, Sales. OrderDetails as sodwhere sod. orderid = SO. orderid

The above shows the correct total number of data behaviors obtained by using the correct writing method. Let's take a look at the problematic writing method.

Select count (*) AS 'query the total data rows using equivalent conditions 'FROM Sales. Orders as so, Sales. OrderDetails AS SOD

At this time, we did not provide the WHERE condition, but there was no error in parsing. Of course, the returned result set is also incorrect. When we use ANSI SQL-92, we also do not provide a comparison condition, as shown below:

USE TSQL2012GOSELECT *FROM Sales.Orders AS SO INNER JOIN Sales.OrderDetails AS SOD;

At this time, a parsing error occurs, that is, the query cannot be continued, and the error result is naturally not obtained.

Conclusion: It is strongly recommended to use ANSI SQL-92 writing, so that data will not be inconsistent, at the same time the readability and maintainability is better than ANSI SQL-89 writing method.

Summary

In this section, we talk about the cross-join and internal join operations, as well as the usage notes. This section ends now. Next we will talk about the self-join and external join operations. For a brief introduction and a deep understanding, we will try again later in the next section, good night.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!