SQL server-Cross join, INNER join Basics review (12)

Objective

This section begins with our join learning, about connecting this piece involves more content, we step by step learning, short content, in-depth understanding.

Crossover 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, to manipulate two input tables, join and generate a Cartesian product of two tables, that is, each row of one table is matched to all rows of another table. So, if a table has m rows and the other table has n rows, the resulting results will have m*n rows. Let's take the example in the SQL Server 2012 Tutorial

SELECT C.custid, E.empidfrom sales.customers as C cross JOIN HR. Employees as Eorder by E.empid

There are 91 rows of data in the Sales.customers table and 9 rows of data in the Hr.employees table, then 819 (91*9) rows of data are available using the cross join data, with the following brief data.

Cross join we can use as a representation

The biggest use of cross joins is to generate digital tables so that we can use them for other purposes, and we'll look at them together.

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 the tsql2012goinsert into dbo. Digits        (digit) VALUES  012345 6 7 8 9 )

Create a digital table

Use Tsql2012gocreate TABLE Nums (    n INT not NULL PRIMARY KEY);

Inserting 1 million of data into a digital table with a cross join

Use the tsql2012goinsert into dbo. Nums (n) SELECT  100000100001 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 usage is as follows

SELECT * fromtable1 as T1inner JOIN table2  as = t2. Id

The inner join returns more data than 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) G Oinsert 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     = F.col1go

(2) non-equivalent condition query

<> F.col1go

We can also use cross-joins to achieve the same effect

Use tsql2012goselect f.col1 fcol1from firsttable as  F cross    JOIN secondtable as Swhere s.col1 & lt;> F.col1go

(3) Querying non-repeating 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, and 3, when we were using the equivalent join to get the result of a 2 or 5 2?

= F.col1go

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

Conclusion: The reason that the inner joins return more data than the actual table is that the result set returned by the inner join is based on join in the query condition, and multiple rows of data are returned if more than one row satisfies the condition.

Internal Join security

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

ANSI SQL-92 notation

* FROMsales.orders    as so = So.orderid

ANSI SQL-89 notation

*= So.orderid

Although both of these can be written to meet the requirements, the basic SQL Server 2012 Tutorial strongly recommends using ANSI SQL-92 notation, because if an error occurs when using ANSI SQL-89, parsing does not generate an error at all, and for ANSI SQL-92, we will look at the question of ANSI SQL-89

Use tsql2012goselect COUNT (' query total data rows using equivalent conditions '= So.orderid

Above is the correct way we get the total data behavior 2,155, below we look at the problem of the wording

' querying total data rows with equivalent conditions ' From sales.orders as So, sales.orderdetails as SOD

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

* FROMsales.orders as so    INNER JOIN sales.orderdetails as SOD;

There will be a parsing error, which means that no further queries can be continued, and the result of the error is naturally not available.

Conclusion: It is strongly recommended to use ANSI SQL-92 notation, which makes the data not inconsistent, while readability and maintainability are stronger than ANSI SQL-89.

Summarize

In this section we talk about cross joins and internal joins, as well as the areas where we need to be aware, and this section concludes with the following section on self-joins and outer joins. Short content, deep understanding, we'll see you next day, good night.

SQL server-Cross join, INNER join Basics review (12)