Related explanations about left join on and where condition placement

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

With and in left joinQuery

SELECT p.pname,p.pcode,s.saletime from product as p left join sales_detail as s on (s.pcode=p.pcode) and s.saletime in ('2012-07-23','2012-07-05');

The result found:

------- ------- ------------

| pname | pcode | saletime |

-- ----- ------- ------------

| A | AC | 2012-07-23 |

| A | AC | 2012-07-05 |

| A | AC | 2012-07-05 |

##| B | DE |

NULL |

| C |

##SELECT p.pname,p.pcode,s.saletime from product as p left join sales_detail as s on (s.pcode=p.pcode) where s.saletime in ('2012-07-23', '2012-07-05');

Query results

------- ------- ----------- -

| pname | pcode | saletime |

------- ------- ------------

| A | AC | 2012-07-23 |

| A | AC | 2012-07-05 |

##| A | AC | 2012-07-05 |

------- ------- ------------

Conclusion: Conditions in on Association, when the data in a table does not meet the conditions, a null value will be displayed. where will output the two tables that completely meet the conditional data

The conditions in the left join: the benchmark data of the left table will be used, and all the data that appears in the left table will appear, and then To join the right table, you need to find out as long as there is a correlation. If the corresponding field has no value or does not meet the conditions, it is set to NULL.

SELECT p.pname,p.pcode,s.saletime from product as p left join sales_detail as s on (s.pcode=p.pcode) ;If you just left join, the displayed content is as follows

------- ------- ------------

| pname | pcode | saletime |

------- ------- ------------

| A | AC | 2012-07-23 |

| A | AC | 2012-07-05 |

| A | AC | 2012-07-05 |

| B | DE | 2012-07-16 | There is a value in it

| C | XXX | NULL | There is no value in it

------- ------- --------- ---

If it has a value but does not meet the conditions, it will be set to NULL. If there is no value, it must be NULL

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

Application scenario: For example, if there is a main table, then you can consider left join to display data based on the main table.

Summary:

1. For left join, no matter what condition is followed by on, all the data in the left table will be found. Therefore, if you want to filter, you need to put the condition after where

2. For inner join, the data in the table that satisfies the condition after on Only then can it be detected, which can play a filtering role. You can also put the condition after where.

The difference between on condition and where condition in SQL

When the database returns records by connecting two or more tables, it will generate an intermediate temporary table, and then return this temporary table to the user.

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

1. The on condition is a condition used when generating a temporary table. It does not matter whether the condition in on is true. will return the records in the table on the left.

2. The where condition is the condition for filtering the temporary table after the temporary table is generated. At this time, there is no meaning of left join (the records of the left table must be returned). If the condition is not true, all will be filtered out.

This article explains the knowledge related to the placement of left join and where conditions. For more learning materials, please pay attention to the php Chinese website.

Related recommendations:

Related knowledge about php mysql fuzzy query function

How to copy and move files through php

About jQuery effects-hiding and showing related knowledge

The above is the detailed content of Related explanations about left join on and where condition placement. For more information, please follow other related articles on the PHP Chinese website!