Difference between "on" and "where" when using left/right join query

Source: Internet
Author: User

I used to put the where-condiction in the "on" substatement in a join query, wishing that can help sort the join count and improving the same mence. but totally I was wrong. it seems the on-condiction is not like the where-condiction in a left/right join query.

For example, there are two tables:
Order ([ID], [order_code]), order_detail ([ID], [order_id], [product_name]), and they have some rows of data:

Order:

ID Order_code
1 Order001
2 Order002
3 Order003
Order_detail:

ID Order_id Product_name
1 1 P001
2 1 P002
3 2 P003
4 3 P001
5 3 P003

Now I want to know the order_codes of orders which buying the product "P001", what is the query statement probably like?

In the pass, I may write this SQL like this:

select * from [order_detail] left join [order] on [order].id=[order_detail].orderid and order_detail.product_name='p001'

Rather than this:

select * from [order_detail] left join [order] on [order].id=[order_detail].orderid where order_detail.product_name='p001'

How come I prefer the first one ?, But not the second one? I thought the first one is faster because it wocould only join rows which product_name is 'p001' in table [order_detail]. I guess
The SQL Server wocould check all of "On-condiction" and if the condiction is false, SQL Server wocould not execute the join operation, that means join operation will only occur twice (because there are only two rows which product_name is "P001" in table [order_detail]). but the second one sucks since it will join all rows of table [order_detail] and then find out which row's product_name is 'p001' only w Except all join operations get done!

But the first one is a bad query, It's not giving what I want. In fact it returns result like this:

ID Order_id Product_name ID Order_code
1 1 P001 1 Order001
2 1 P002 Null Null
3 2 P003 Null Null
4 3 P001 3 Order003
5 3 P003 Null Null

But what result I want is like this:

ID Order_id Product_name ID Order_code
1 1 P001 1 Order001
4 3 P001 3 Order003

And only the second SQL is correct.
So what is wrong? What's the matter of the first one?
In this case, the "on-condiction" is not like what I think about. in a left/right join query, SQL Server will select all rows of the basic table no matter the on-condiction is true or false, in other words, the "on-condiction" is not a condiction to selecting rows of a basic table, in fact, it's just a condiction to joinning rows. if the on-condiction is true, the current row of the basic table will join the row of the secondary table, if not, it won't, remainning the null in the field. but no matter it's true or not, all rows of the basic table are there, no more less.

But the second, surely, will get the right result: only two row with the product_name "P001 ". it will join all rows, and when the join get done, it then find out rows I want.

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.