The difference between the on and where of SQL-----------------------

The difference between on and where in SQL

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.
When using 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 left table regardless of whether the condition on is true.
2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Suppose there are two tables:
Table 1:TAB2
ID size
1 10
2 20
3 30
Table 2:TAB2
Size Name
Ten AAA
BBB
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 ')

Procedure for the first SQL: 1, Intermediate table on condition: tab1.size = tab2.size tab1.id tab1.size tab2.size tab2.name
1 AAA
2 BBB
2 CCC
3 (NULL) (NULL)

2, again on the intermediate table filter where Condition: Tab2.name= ' AAA ' tab1.id tab1.size tab2.size tab2.name
1 AAA

Procedure for the second SQL: 1, Intermediate table on condition: Tab1.size = tab2.size and Tab2.name= ' AAA ' (the condition is not true also returns records from the left table) tab1.id tab1.size tab2.size tab2.name
1 AAA
2 (NULL) (NULL)
3 (NULL) (NULL)

In fact, the key reason for the above results is the particularity of the left Join,right Join,full join, regardless of whether the on condition is true will return the records in the left or right table, and full has a set of attributes of left and right. The inner join does not have this particularity, the condition is placed in and where, and the result set returned is the same. On in order to reflect the full connection of a party in an outer connection, where there is no such function, the internal connection pairing is possible.

==================================================================================

On, where, having the difference

On, where, have these three clauses that can be conditionally added, on is the first execution, where second, having the last. Sometimes if this order does not affect the intermediate results, then the final result is the same. But because on is the non-qualifying records 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 should also be faster than having, because it filters the data before the sum, so having is the slowest. But it is not that having a useless, because sometimes in step 3 did not come out of the record is not known to meet the requirements, it is necessary to use having.

It is used on when two tables are joined, so there is a comparison between where and having a table. In the case of this single-table query statistic, if the conditions to be filtered are not related to the fields to be computed, then the results are the same, except where the Rushmore technique can be used, and the having is not, the latter is slower in speed.

If a calculated field is to be involved, it means that the value of the field is indeterminate until it is calculated, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated before it works, so in this case the results will be different.

On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand that this condition should be at the time of action, and then decided to put it there



The difference between a on,where and a condition in a join table

For join table operations, here is not a detailed, when we are in the join operation on the table, for on and where the conditions behind, it is not clear whether you have noticed, there is any difference, maybe some friends will think that the conditions behind them are the same, you can follow on the back, if you like, can also be followed in the Where. What difference do they have between on and where?
In the join operation, there are several situations. Left Join,right Join,inner JOIN, etc.

In order to express clearly the topic of the problem described, I briefly on the Left,right,inner these several connection methods to make a description.

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

Here we stipulate that some logs may not be classified, and some classifications may not currently belong to its articles.

1.LEFT JOIN: (Guaranteed to find all rows in the leftist table)
Find out all the articles and show them the categories:
SELECT P.title,c.category_name
From Post P
Left joins category C on p.cid = C.cid2.

Right JOIN: (Guaranteed to find all rows in the left-hand table)
Query all categories and show the number of articles that are included in the category.
SELECT COUNT (p.id), C.category_name
From Post P
Right joins category C on p.pid = C.cid3.

INNER JOIN: (Find the rows in the two tables that are associated equal)
The query has a log of the owning category. (that is, those journal articles that do not have a sex classification will not be within our scope of query).
SELECT P.title,c.category_name
From Post P
INNER JOIN category C on p.cid = C.cid. This case is equivalent to a direct two-table hard association.

Now let's go back and look at the above question. In the first case, what would happen if the conditions on which we were on were written behind the Where? That

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 notation.

SELECT COUNT (p.id), C.category_name
From Post P
Right JOIN category C
WHERE p.pid = C.cid

If you run the above SQL statement, you will find that they have filtered out some of the records that do not meet the conditions, perhaps here, people will have doubts, not using left and right? They can guarantee that all rows on the left or right are all queried, so why not use them now? For the emergence of this problem, hehe! Does it feel a little weird? This problem arises because of the conditions behind the two keywords, where and on.
Well, now I do not adjust the taste of the mouth, give everyone the answer.
For associative operations on tables joined by joins, if a row that does not satisfy the join condition is also within our query, we must put the join condition on the back, not in the where, if we put the join condition behind the where, then all the Left,right, Such operations will have no effect, and in this case, the effect is exactly the same as the inner connection. For those conditions that do not affect the selection of rows, either on or behind.
Remember: All connection conditions must be placed behind, otherwise all left, and right will be used as a device, without any effect.

==================================================================================

The magical of SQL where 1=1 0=1

What's the use of where 1=1? In the SQL language, writing this sentence is not the same as writing it.

SELECT * FROM table1 where 1=1 is completely indistinguishable from the select * FROM table1, there are even many other formulations, 1<>2, ' a ' = ' a ', ' a ' <> ' B ', with only one purpose, The condition of where is eternal, and the result is no constraint condition.

This is used in SQL injection, such as SELECT * FROM table1 where name= ' lala ' to force plus select * FROM table1 where name= ' lala ' or 1=1 this becomes an unconstrained query again.

The magic of the recent discovery is that 1=1 can be a very handy specification for a variable number of query conditions. For example, a query may have name,age,height,weight constraints, or not, what should be handled?

String Sql=select * FROM table1 where 1=1

Why write extra 1=1? I'll find out soon.

if (!name.equals ("")) {
sql=sql+ "Name=" "+name+" ";
}
if (!age.equals ("")) {
Sql=sql+ "Age", "+age+";
}
if (!height.equals ("")) {
sql=sql+ "height=" "+height+" ";
}
if (!weight.equals ("")) {
sql=sql+ "weight=" "+weight+" ";
}

If you do not write 1=1, then in every non-empty query conditions before, you must determine whether there is a WHERE clause, or in the first occurrence of the place to add where

See today: "SELECT * from strName WHERE 1 = 0";
Don't understand why there are 1=0?

The query answers:

This SELECT statement is primarily used to read the structure of a table without regard to the data in the table, saving memory because you can save the result set.

In addition, where does this use? is used primarily to create a new table, and the structure of the new table is the same as the structure of the queried table. The following SQL statement:

CREATE TABLE newtableas SELECT * from oldtablewhere 1=0;

===================================================================================

The difference between the on and where of SQL-----------------------