The difference between a SQL statement inner join and a LEFT join

Project requirements change, before writing a query SQL, need to modify, nausea for a long while did not change well, finally want to go, spell to spell (splicing SQL), the original directly to the inner join changed to the left join on OK.

After writing that feeling Ah! Really for the fountainhead come ah, what the best programming habit is to think good after the start, the Boulevard to Jane, Big Love boundless ...

After the bitter force, still want to come out to share the experience.

In short, the demand is probably:

Products are sold in installments, of course, the product will be effective, failure, a product corresponding to multiple inventory, one inventory corresponding to a specific day, each inventory will correspond to a price. Requires that all products with the lowest price in stock be displayed.

Start, the demand is sold out of the product will not show.

At first glance, you must think it's easy. INNER JOIN inventory table, GROUP by a product number is OK. But have you ever thought that the price and inventory of such inquiries are inaccurate. The relationship between product and inventory is one-to-many, can only group by the product number, and the lowest price and inventory can not be matched at the same time ( the use of Niang Group by ).

Write to the following SQL:

Selectp.*,min (S.Price) as Pricefromhc_product PINNER JOIN hc_stock s on P.productid=s.productid and s.stocknum>0 and S. Effectday>now () GROUP by P.productid

Now that the demand has changed, the products that need to be empty are also shown and labeled "The product is sold out". Ideas are as follows:

I use the Product table as the main table, query the inventory of more than 0 in the validity period of the product, and the inventory table is the left connection, then you can query up the quantity is empty products, inventory field is not 0, but null, and then the product number to group, according to the above conditions, you can query up all the products, A product with an inventory of 0, the stock amount is null.

Selectp.*,min (S.Price) as Pricefromhc_product pleft JOIN hc_stock s on P.productid=s.productid and s.stockNum>0 and S.E Ffectday>now () GROUP by P.productid

PS1: The query term validity period and the number of products to be written behind the connection query, that is, on the back. In this way, you can check out outdated, but not expired, products. That is to be able to query up all the products (product is not divided into inventory, as long as there is the product, can query up). If the condition is written in the where, then the expired product will be filtered and no longer queried.

PS2: The inner join is much simpler, and it will query for records that meet the conditions of two tables.

PS3: Expired products have uniform control over whether the attributes in the product table are "valid".

Query results without drawings, we may not see very clear, the end of the project has been in a tense state. Demo didn't care to do, in a few days, I will attach.

The difference between a SQL statement inner join and a LEFT join