SQL server-External Join Basics review (13)

Objective

In this section we go on to the outer joins in the join type, and we'll talk about join performance and more in-depth knowledge, short content, in-depth understanding, always to review the basics.

Outer Joins

The outer joins are also divided into left outer joins and right outer joins, using the key words to be OUTER join, OUTER join, full OUTER join, which is optional when you OUTER the keyword. The left keyword means leaving the row to the right, which means preserving the row on the left, and the full keyword to indicate that the rows to the right and both sides remain. The third logical query processing stage of an outer join identifies rows in the reserved table that are not matched to another table based on the on predicate, which adds these rows to the results generated by the first two join stages, where the property that joins the non-reserved side uses NULL as a placeholder. That said, the left outer join is based on the left table, if the right table satisfies the condition then returns the right row, if not satisfied returns null instead of the right side of the actual row of data, right outer joins the same. Let's take a look at the following simple example

Use tsql2012goselect c.custid, C.companyname, O.orderidfrom sales.customers as C     = O.custid

From the Sales.customers, one of the customers in the table has no order, it has an order ID of 22 and is informed by the left customer ID of 22 and the order ID null on the right.

Beyond the basics of outer joins

With the introduction of the outer join above, we know that the missing value can be obtained through an outer join, that is, NULL is returned if the condition is not met. Here we assume that, like the next scene, we need to query all orders in the Orders order form to ensure that there is at least one row of output per day for the range from January 1, 2006 to December 31, 2008, no action on the date of the order that the range can have, but you want the output to contain a date without an order. Use a null tag as the order attribute placeholder. The first step we need to address is to get all the dates from January 1, 2006 to December 31, 2008, the previous one we talked about Cross joins, we generated a digital table by cross-joins, and this is where it comes in handy.

You first need to get the number of days between January 1, 2006 and December 31, 2008, and then get all the dates in this interval, as follows:

-1 ,'20060101'20060101',' 20081231 ' 1 ORDER by OrderDate

Next, the resulting sequential date is matched with the order date in the Sales.orders table to obtain all information about the order, and Null is returned by using the left outer join that does not satisfy the condition.

 use tsql2012goselect DATEADD (day, n -1 ,  " 20060101   '  -1 ,  " 20060101   ") = O.orderdatewhere dbo. NUMS.N  <= DATEDIFF (day,  " 20060101  , "   20081231   ") + 1  order by OrderDate  

External join considerations (1)

(1) Where the outer join becomes a logical inner JOIN

We look at the above figure in the order date equals 2006-11-09, at this time OrderID is null, at this time we first to make the following query

' 20061108 '

We query the Sales.orders table for order date greater than 2006-11-08 order information, we look at the results returned

At this point we find that the order date for the 2006-11-09 order information is not, why so, because the WHERE clause it will filter out unknown that is null value, why to say this, because the outer join in the right table does not satisfy the original will return NULL, However, if there is a WHERE clause, it causes all external rows to be filtered out, in other words, offsetting an outer join is actually logically equivalent to an inner join, which inadvertently creates a logical bug. So based on this we can draw the following conclusions:

Conclusion: The use of a WHERE clause in an outer join will filter out NULL, causing all outer rows to be filtered out, effectively offsetting the outer joins, and the resulting outer joins logically becoming an inner join.

(2) A multi-table join causes the outer join to become a logical inner JOIN

In the case of multiple joins, such as first making an outer join of two tables, followed by an inner join of the third table, if the predicate of the INNER join sub-clause compares the property from the outer join non-reserved side to the property from the third table, all outer rows are filtered out. What do you mean, when using an outer join, it is possible to return the null value of the outer row, and then take advantage of the inner join, because NULL is compared to any value to generate unknown, so at this point the unknown is filtered out by the on filter. The above also offsets the outer joins, causing the outer joins to become logical inner joins. As follows

== O.orderid

In general, regardless of the type of outer join followed by an inner join or an outer join subquery, the outer row is filtered out, of course, it is assumed that the join condition compares the null tag from the left and any value on the right. In order to solve this problem, we can solve by the following three kinds of solutions.

"1" replaces the second inner join with a left outer join.

== O.orderid

"2" uses the inner join first, and then the right outer join.

== C.custid

"3" is changed on the original basis, and the inner join becomes an independent logical phase.

Use tsql2012goselect c.custid, O.orderid, Od.productid, Od.qtyfrom sales.customers as C left JOIN      == O.custid

External join Considerations (2)

When aggregating using count (*) in an outer join, it takes internal and external rows into account because it calculates the number of rows regardless of their contents, as follows:

Use tsql2012goselect C.custid, COUNT (*) as Numordersfrom sales.customers as C left JOIN   = C.custidgroup by C.custid

The OrderID, which was previously checked to see that the customer ID is 22, is null, i.e. there is no order quantity, so there is a bug here because COUNT (*) includes a null value, so here we need to replace with count (column name).

 COUNT (O.orderid) as Numordersfrom sales.customers as C left JOIN   = C.custidgroup by C.custid

Summarize

In this section we focus on the basics of external joins and considerations, and our next section will cover integrated knowledge of joins, short content, in-depth understanding, we'll see you next, good night.

SQL server-External Join Basics review (13)