About left join on and where conditional placement

The placement of the left join on and where conditions is important in PHP, and this article will explain the relevant knowledge.

Query with and within the left join

SELECT p.pname,p.pcode,s.saletime from product as P left joins Sales_detail as S on (S.pcode=p.pcode) and S.saletime in (' 2 012-07-23 ', ' 2012-07-05 ');

Find out the results:

+-------+-------+------------+

| PName | Pcode | Saletime |

+-------+-------+------------+

| A | AC | 2012-07-23 |

| A | AC | 2012-07-05 |

| A | AC | 2012-07-05 |

| B | DE | NULL |

| C | XXX | NULL |

+-------+-------+------------+

Direct Where Condition query

SELECT p.pname,p.pcode,s.saletime from product as P left joins Sales_detail as S on (S.pcode=p.pcode) where S.saletime in ( ' 2012-07-23 ', ' 2012-07-05 ');

The results of the query

+-------+-------+------------+

| PName | Pcode | Saletime |

+-------+-------+------------+

| A | AC | 2012-07-23 |

| A | AC | 2012-07-05 |

| A | AC | 2012-07-05 |

+-------+-------+------------+

Conclusion: On the Conditional association, a table data does not meet the criteria when the null value is displayed. Where outputs two tables that fully satisfy the condition data

The condition inside the left join: the datum that will be left table, where the data that appears on the right table is to appear, and then the join is on the table, as long as the association on the need to find out, if the corresponding field does not have a value or does not meet the criteria is set to null.

SELECT p.pname,p.pcode,s.saletime from product as P left joins Sales_detail as S on (S.pcode=p.pcode);

+-------+-------+------------+

| PName | Pcode | Saletime |

+-------+-------+------------+

| A | AC | 2012-07-23 |

| A | AC | 2012-07-05 |

| A | AC | 2012-07-05 |

| B | DE | 2012-07-16 | There's a value in it.

| C | XXX | NULL | There's no value in there.

+-------+-------+------------+

Null if there is a value but does not meet the criteria. If no value is definitely null

If it is a where condition, it must be satisfied.

Application scenario: For example, there is a main table, that is, the main table as the benchmark to show the data can consider the way left join processing

Summarize:

1. For the left join, regardless of the conditions on the back of the table, the data are all checked out, so to filter the need to put the conditions behind where

2. For inner join, the data of the condition table after on is able to be detected, can play the role of filtering. You can also put the condition behind the where.

Differences between on and where conditions 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.

This article explains the knowledge of left join and where condition placement, and more learning materials are available for viewing in the PHP Chinese web.