Execution order of SQL with LEFT join

As the name implies, this concept is named after Descartes. In mathematics, the Cartesian product of two sets X and Y (Cartesian product), also known as the direct product, is expressed as XXY, which is the first object that is a member of X and the second object is all possible ordered pairs of a member of Y.

Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases. Similarly, if a represents a collection of students in a school and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses.

2.Join type

A cross join is a Descartes product that is the number of rows in a table multiplied by another table.
Inner join returns only matches for two table join columns.
The join column for the first table in the left join does not match in the second table, and the value in the second table returns NULL.
The join column of Right join second table does not match in the first table, the value in the first table returns NULL.
The full join returns rows from two tables with the left Join+right join.

3. There is a need to construct a Cartesian product for each type of join (cross, left, right, full, inner) on both tables.

Sometimes it's incredible to think that if you join two extra-large tables, does SQL go straight to the Cartesian product? Don't you have to filter on the condition beforehand? How big is the data?

4. Check MSDN to see how the entire SQL execution sequence is performed.

http://msdn.microsoft.com/en-us/library/ms189499 (v=sql.100). aspx

Processing Order of the SELECT statement
The following steps show the processing order for a SELECT statement.

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP by

6.WITH CUBE or with ROLLUP

7.HAVING

8.SELECT

9.DISTINCT

10.ORDER by

11.TOP

That is, the filter on the first, and then the join, which avoids the two large tables produce all the data of the Cartesian product large data.

When these steps are executed, each step produces a virtual table that is used as input to the next step. These virtual tables are not available to callers (client applications or external queries). Only the table generated in the last step is returned to the caller.

If you do not specify a clause in the query, the corresponding step is skipped.

The following is an illustration of the SQL execution order given in the <<inside Microsoft SQL Server querying>> T-SQL book.

Are the remaining filters in the 5.On more efficient or lower in where?

SELECT * FROM table1 as a

INNER JOIN table2 as B on A.id=b.id and A.status=1

SELECT * FROM table1 as a

INNER JOIN table2 as B on a.id=b.id

where A.status=1

It's clear that you check MSDN. http://msdn.microsoft.com/en-us/library/ms189499 (v=sql.100). aspx

There can be predicates this involve only one of the joined tables in the ON clause. Such predicates also can is in the WHERE clause in the query. Although the placement of such predicates does do a difference for INNER joins, they might cause a different result When OUTER joins is involved. This was because the predicates in the "on clause was applied to the table before the join, whereas the WHERE clause was SEMA Ntically applied to the result of the join.

After translation, if it is inner join, put on and put where the result is the same, but did not say which efficiency is higher? If there is a outer join (left or right), there is a difference, because on takes effect earlier, a portion of the data has been filtered in advance, where the where is in effect.

In general, it feels more efficient to put on, because it is executed before the where.

I heard that through the SQL query plan to determine the actual results, tomorrow to study, welcome to the master to criticize.

********************************************************************************************************

2011/11/21 Latest Experience

Just saw <<microsoft SQL Server 2008 Insider: T-SQL query >> a description of the connection is not the same as I had previously understood;

Itzib in the book that the Cartesian product, and then on the filter, if the join is inner, continue to go down, if the join is a left join, the on filter off the main table of the data is added back; Then the filter in the where is executed;

On is not the final filter, because the left join may be added back, and where is the final filter.

The difference between on and where is only when an outer join (left, right) is used, if the inner join is the same as where it was made, since on is the where, there is no other step in the middle.

Execution order of SQL with LEFT join