Talking about SQL Server cross-join inner join _mssql

Objective

At the beginning of this section we entered the join learning, about the connection of this piece of content more, we step-by-step study, short content, in-depth understanding.

Cross join (CROSS join)

Cross joins are the simplest type of join. A cross join performs only one logical query processing phase-the Cartesian product. For example, two input tables are manipulated to join and generate the Cartesian product of two tables, that is, to match each row of one table with all the rows of another table. So, if one table has m rows, and the other table has n rows, there will be m*n rows in the resulting result. Let's take the example in the SQL Server 2012 Tutorial

SELECT C.custid, e.empid from
sales.customers as C
CROSS JOIN HR. Employees as E order by
E.empid

There are 91 rows of data in the Sales.customers table, 9 rows of data in the Hr.employees table, and 819 (91*9) rows of data using the cross join data, and the abbreviated data is as follows.

Cross joins we can use the following figure to express

The greatest use of cross joins is to generate a digital table so that we can use it 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

Use TSQL2012
go
inserts into dbo. Digits
  (digit)
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)

Create a numeric table

Use TSQL2012
go
CREATE TABLE nums
(
 n INT not NULL PRIMARY KEY
);

Inserting 1 million data into a numeric table using a cross Join

Use TSQL2012
go
inserts into dbo.  Nums (n)
SELECT d6.digit * 100000 + d5.digit * 10000 + d4.digit * 1000 + d3.digit * + d2.digit * + d1.digit + 1 As N from
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 D6 order by
N

Inner joins (INNER join)

The internal join usage is as follows

SELECT * from
table1 as T1
INNER joins table2 as T2 on T1. Id = t2. Id

Inner joins return more data in table

We first give the following three test tables

Use TSQL2012
go
CREATE TABLE firsttable (Col1 int)
CREATE TABLE secondtable (Col1 int) Create
table third Table (Col1 INT)
go
insert to firsttable (Col1)
VALUES (1), (2), (3), (NULL)
go
inserts into Secondt Able (Col1)
values (1), (2), (3), (NULL)
go
inserts into thirdtable (COL1) VALUES (2), (2), (
2), (2), ( 2), (NULL) Go

(1) Equivalence condition Inquiry

SELECT f.col1 fCol1 from
firsttable f
 INNER JOIN secondtable s in s.col1 = F.col1
Go

(2) Non-equivalence condition inquiry

Use TSQL2012
go
SELECT f.col1 fCol1 from
firsttable f
INNER JOIN secondtable s on s.col1 <> f.co L1 Go

We can also use cross joins to achieve the same effect

Use TSQL2012
go
SELECT f.col1 fCol1 from
firsttable as F
 CROSS JOIN secondtable as S
where S.col1 < > F.col1 Go

(3) Query for duplicate rows (non-distinct)

When we created the Third Test table, the data we inserted was 5 2, and the data we inserted in the first table was 1, 2, 3, and the result we used for the equivalent join was a 2, or 5 2?

Use TSQL2012
go
SELECT f.col1 fCol1 from
firsttable f
INNER JOIN thirdtable s on s.col1 = F.col1
G O

We get the result is 5 2, why the use of the inner join means that the equivalent condition is not to return a 2, in fact, we can summarize as follows:

Conclusion: The reason for using an inner join to return more data than the actual table is that the result set returned by the inner join is based on a join in the query condition, and more data is returned if multiple rows meet the condition.

Internal Join security

We have two ways of writing in two tables when using the equivalence condition query.

ANSI SQL-92 notation

Use TSQL2012
go
SELECT * The sales.orders as so
 INNER joins Sales.orderdetails as SOD on sod.orderid = So.orderid

ANSI SQL-89 notation

Use TSQL2012
go
SELECT *
sales.orders as So, sales.orderdetails as SOD
WHERE Sod.orderid = So.orderid

Although both of these can be written to meet the needs, the SQL Server 2012 Basic Tutorial strongly recommends using ANSI SQL-92, because if an error occurs when the ANSI SQL-89 is written, parsing does not generate an error at all, and for ANSI SQL-92 writing will, the following we look at the issue of ANSI SQL-89 writing

Use TSQL2012
go
SELECT COUNT (*) as ' using equivalent conditions to query the total data row ' 
from sales.orders as So, sales.orderdetails as SOD
WHERE Sod.orderid = So.orderid

The above is the correct total data behavior we get using the correct notation 2,155, let's look at the problematic wording

SELECT COUNT (*) as ' uses the equivalence condition to query the total data row ' 
from sales.orders as So, sales.orderdetails as SOD

At this point we do not give a where condition, and parsing does not appear error, of course, the return of the result set is wrong. When we use ANSI SQL-92 to write, we also do not give the comparison conditions, as follows

Use TSQL2012
go
SELECT * The sales.orders as so
 INNER JOIN sales.orderdetails as SOD;

At this point there will be parsing errors, that is, can not continue to query, naturally will not get the wrong results.

Conclusion: It is strongly recommended to use ANSI SQL-92 to make the data not appear to be inconsistency, while readability and maintainability are more powerful than ANSI SQL-89.

Summarize

In this section we talk about Cross joins and inner joins, and also give a place to use attention, and this concludes this section, and we'll talk about self-join and outer joins in the next section. Short content, in-depth understanding, we see the next section, Good night.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!