Shulou(Shulou.com)05/31 Report--

How to realize cross join and inner join in SQL Server? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Cross connection (CROSS JOIN)

Cross-join is the simplest type of connection. Cross joins perform only one logical query processing phase-Cartesian product. For example, operate on 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. So, if one table has m rows and the other table has n rows, the resulting result will have m rows. Let's take the example from 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.Customers table and 9 rows in the HR.Employees table, while using cross-join data, there are 819 (91-9) rows of data, as follows.

We can use the cross-connection as shown in the following figure

The greatest use of cross-joins is to generate digital tables so that we can use them 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 pieces of basic data

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

Create a number table

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

Inserting 1 million pieces of data into a digital table by cross join

USE TSQL2012GOINSERT INTO dbo.Nums (n) SELECT D6.digit * 100000 + D5.digit * 10000 + D4.digit * 1000 + D3.digit * 1000 + 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 (INNER JOIN)

The inner join is used as follows

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

The inner join returns more data in the table

We 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), (NULL) GO

(1) query for equivalence conditions

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

(2) query with non-equivalent conditions

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

We can also use cross-connections to achieve the same effect.

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

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

When we create the third test table, the data inserted is 5 2s, while the data inserted in the first table is 1, 2, 3 respectively. Is the result obtained by using equivalent join 1 2 or 5 2?

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

The result we get is five 2s. Why do we use the inner join, that is to say, the equivalent condition is not to return a 2s? in fact, we can sum it up as follows:

Conclusion: the reason why the inner join returns more data than the actual table is that the result set returned by the inner join is based on the JOIN in the query condition, and multiple data is returned if multiple rows meet the condition.

Internal connection security

When two tables are queried using equivalence conditions, we have two ways to write them.

ANSI SQL-92 writing method

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

ANSI SQL-89 writing method

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

Although both of these two writing methods can meet the needs, ANSI SQL-92 is strongly recommended for SQL Server 2012 basic tutorials. Why? because if there is an error in ANSI SQL-89 writing, the parsing will not generate an error at all, but for ANSI SQL-92 writing, let's take a look at the problem of ANSI SQL-89 writing.

USE TSQL2012GOSELECT COUNT (*) AS 'queries total data rows using equivalence conditions' FROM Sales.Orders AS SO, Sales.OrderDetails AS SODWHERE SOD.orderid = SO.orderid

Above are 2155 correct total data behaviors obtained by using the correct writing method. Let's take a look at the problematic writing methods below.

SELECT COUNT (*) AS 'query total data rows using equivalence conditions' FROM Sales.Orders AS SO, Sales.OrderDetails AS SOD

At this point, we do not give the WHERE condition, and there is no error in the parsing, of course, the returned result set is wrong. When we use ANSI SQL-92 to write, we also do not give a comparison condition, as follows

USE TSQL2012GOSELECT * FROM Sales.Orders AS SO INNER JOIN Sales.OrderDetails AS SOD; 's answers to the questions about how to implement cross-joins and inner joins in SQL Server are shared here. I hope the above content can be of some help to you. If you still have a lot of questions to solve, you can follow the industry information channel for more related knowledge.

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.