Left join on and placement of where condition

SELECT * FROM

Td
Left JOIN (
Select case_id as SUP_CASE_ID, COUNT (*) Supervise_number from
Td_kcdc_case_sup_info
GROUP BY case_id
) SUP
On
sup.sup_case_id = td.case_id
where 1=1/* cannot be removed, otherwise the condition after and is considered to be the condition of the federated query, which cannot act as a filter, because the LEFT join will therefore be fully identified in the TD table.

and Td.con = ' xxxx '

Summarize:

1. For a LEFT join, regardless of the condition behind on, the data in the left-hand table is all checked out, so you need to filter the conditions to the where

2. For inner join, the data that satisfies on the condition table on the back can be detected and can act as a filter. You can also put the conditions behind the where.

Reference: The difference between on,where and conditions behind the join table

Http://wenku.baidu.com/view/fa341ad4c1c708a1284a4450.html

The difference between on condition and where condition in SQL (having) (turn)

http://apps.hi.baidu.com/share/detail/20768615

the difference between an on condition and a Where condition in SQL

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and then the temporary table is returned to the user.

When you use the left jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the table on the left regardless of whether the condition in on is true.

2. Where condition is the condition of filtering the temporary table after the temporary table is generated. There is no meaning for the left join (the record must be returned to the left-hand table), and the condition is not really filtered out.

Suppose there are two tables: Table 1:TAB2

Id	Size
1	10
2	20
3	30

Table 2:TAB2

Size	Name
10	Aaa
20	Bbb
20	Ccc

Two sql:
1, select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size) where tab2.name= ' AAA '
2, select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size and Tab2.name= ' AAA ')

The first SQL process: 1. Middle table On condition: Tab1.size = Tab2.size Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa 2 20 20 Bbb 2 20 20 Ccc 3 30 (NULL) (NULL) | | 2. Filter the middle table again Where Condition: Tab2.name= ' AAA ' Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa

Procedure for second sql: 1, intermediate table on condition: Tab1.size = tab2.size and Tab2.name= ' AAA ' (the condition is not true to return records from the left table) (NULL) tab1.id tab1.size tab2.size tab2. Name 1 ten ten AAA 2 (null) (null) 3 , (null)

In fact, the key reason for the above result is the particularity of the left Join,right Join,full join, which returns records in the left or right table regardless of whether the condition on the on is true , and full has the set of left and right attributes. Inner Jion does not have this specificity, the condition is placed in on and where the result set returned is the same.

On, where, having differences

On, where, and having these three clauses can be conditionally, on is the first execution, where the second, having the last. Sometimes the end result is the same if the order does not affect the middle result. But because on is the first time does not meet the criteria of the record filtered before the statistics, it can reduce the intermediate operation to deal with the data, it should be said that the speed is the fastest.

According to the above analysis, you can know where also should be faster than having, because it filtered data before the sum, so having is the slowest. But it is also not to say that having no use, because sometimes in step 3 has not come out, do not know that record to meet the requirements, it will be used having.

On is used on two table joins, so when a table is left, the where is compared to having. In this case of single table query statistics, if the condition to be filtered does not involve the calculation of fields, then their results are the same, but where the Rushmore technology can be used, and the having is not, the slower the latter.

If you want to involve a calculated field, it means that the value of this field is indeterminate before it is computed, and according to the workflow in the previous article, where the action time is done before the calculation, and the having is performed after the calculation, so in this case the results will be different.

On a multiple table join query, on has an earlier effect than where. First, the system is based on the join conditions between the tables, a number of tables into a temporary table, and then filtered by the where, and then calculated, after the calculation by having to filter. Therefore, to filter the conditions to play a correct role, first of all to understand how this condition should work, and then decide to put it therethe difference between the on,where and the conditions behind the join table The join table operation is not detailed here, when we're doing a join-association operation on a table, it's not clear if you've noticed what's the difference between the on and where conditions, and some friends might think that the conditions behind them are the same, you can follow on, If you want, you can also follow the where. What is the difference between on and where?

In a JOIN operation, there are several situations. Left Join,right Join,inner JOIN and so on.

In order to clearly express the problem described in the topic, I briefly explain the left,right,inner of these kinds of connection methods.

Here is a common blog system of the Log table (POST) and classification table (category) to describe it.

Here we stipulate that some of the logs may not be categorized, some classifications may not currently belong to its article.

1. Left JOIN:

(Guaranteed to find all rows in the leftist table)

Find out all the articles and display their classifications:

Copy Code

SELECT p.title,c.category_name from Post p left JOIN category C on p.cid = C.cid

2. Right JOIN:

(Guaranteed to find all rows in the right table)

Query all categories and display the number of articles that the category contains.

Copy Code

SELECT COUNT (p.id), c.category_name from post p rightjoin  category C on p.pid = C.cid

3. INNER JOIN

(Find the rows with the same association in both tables)

Query for a journal that has a category of which it belongs. (that is, those who do not have the classification of the journal articles will not be within our query scope).

Copy Code

SELECT p.title,c.category_name from post p INNER JOIN category C on p.cid = C.cid.

This situation is equivalent to the direct two-table hard association.

Now let's look back at the questions above.

In the first case, what happens if the condition we are on is written behind the where.

That

Copy Code

SELECT p.title,c.category_name from Post P-left JOIN category C WHERE  p.cid = c.cid

For the second case, we also follow the above method of writing.

Copy Code

SELECT COUNT (p.id), c.category_name from post p rightjoin  category C WHERE p.pid = C.cid

If you run the above SQL statement, you will find that they have filtered out some records that do not meet the criteria, and perhaps here you will have questions, not left and right. They make sure that all the rows on the left or right side are all queried and why they are not used anymore. For this problem, hehe. Is that a little weird? The problem with

occurs because of the two keywords where and on are followed by the criteria.

OK, now I also do not make people taste mouth, give everyone hints answer it.

is associated with the table that the join participates in. If the row that does not meet the join condition is also within our query, we must put the join condition on the back, not the where, and if we put the join condition behind the where, then all left, Right, and so these operations will not have any effect, in this case, its effect is exactly the same as the inner connection. For those conditions that do not affect the selection of rows, it is possible to put them on or behind them.

Remember that all join conditions need to be placed on the back, otherwise all left, and right associations in front of them will be used as furnishings without any effect.

This question was raised in our Phpoo discussion area a few days ago, has not written a more detailed distinction, in fact, this question can be described clearly in one sentence, then, why I want to write here so many long-winded words, mainly because in their own knowledge to consolidate the same time, I also hope to give more convenience to friends, and now our Phpoo team members of the level of uneven, so in order to take care of more people, just more long-winded so a few words, Hope Regiment inside the masters are not jokes.