Difference between inner join and left join in SQL statements: innerjoin

Difference between inner join and left join in SQL statements: innerjoin

The project requirement is changed. I wrote a query SQL statement, which needs to be modified. I haven't changed it for a long time. Finally, I want to join the SQL statement ), it turns out that it is OK to directly change inner join to left join.

That emotion after writing it! The best programming habit is to try again after thinking about it. The greatest truths are simple and the greatest love is boundless ......

After the hard work, we still need to share our experiences.

In short, the requirement is probably like this:

Products are sold in installments. Of course, products will take effect and expire. One product corresponds to multiple inventories, one inventory corresponds to a specific day, and each inventory corresponds to a price. Show all products with the lowest prices in stock.

At the beginning, the requirement is that the products sold are not displayed.

At first glance, you must think this is very simple. After inner joins the database table, the product number of group by is OK. However, have you ever thought that the price and inventory obtained by such queries are not accurate. The relationship between products and inventory is one-to-many, and only the group by product number is allowed, while the lowest price and inventory cannot correspond at the same time (Can du Niang Group By usage).

The following SQL statement is also written:

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 the demand has changed. You need to display the products with empty inventory and add "This product has been sold out ". The idea is as follows:

I use the product table as the main table to query products whose inventory is greater than zero within the validity period, and the left connection with the inventory table, then I can query products whose quantity is null, the inventory field is not 0, but null. Then, it is grouped by product number. According to the above conditions, all products can be queried. If the inventory is 0, the inventory 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.effectDay>NOW()group by p.productId

PS1: The validity period and product quantity of the query conditions should be written after the connection query, that is, after the ON query. In this way, expired products can be queried. That is to say, all products can be queried (products are not in stock and can be queried as long as they exist ). If the condition is written after the where clause, expired products are filtered and no longer queried.

PS2: inner Join is much simpler. It queries the records that meet the conditions of both tables.

PS3: expired products have the "valid" attribute in the product table for unified control.

The query results are not shown in the diagram, which may not be very clear. The project remains tight at the end of the year. I will attach the DEMO in a few days.