在MySQL中学习LEFT JOIN [英] Learning LEFT JOIN in MySQL

问题描述

亲爱的all.i网络编程新手，直到现在我仍然了解MySQL语法. 现在我开始使用LEFT JOIN方法.我知道此方法用于在两个或多个表之间进行规范化.我在SO中发布了一个问题，然后收到了答案这让我感到困惑.我已经修改了该答案，但是我仍然感到困惑，因为它仅将LEFT JOIN用于一个表. LEFT JOIN是否可以在一个表中使用?

dear all.i newbie at web programming and until now i still have learn about MySQL syntax. for now i start to use LEFT JOIN method. i know that this method use for make normalization between two or many tables. I have posted a question in SO, then I receive an Answer which make me confuse. I have modified that answer,but i still confuse because it use LEFT JOIN just for one table. whether the LEFT JOIN can be used in one table?

推荐答案

您可以将LEFT JOIN与一个表一起使用，就像您可以JOIN将其与表一起使用一样.您的目的通常会有所不同，因为LEFT JOIN的特定特征是确保在右侧没有对应的行时确保输出中有一行.您可以通过检查该行其他部分"的NULL来选择专门选择的那些行，该部分通常来自右侧表.

You can LEFT JOIN a table with itself, just like you can JOIN a table with itself. Your purpose will usually be different, because the specific characteristic of a LEFT JOIN is ensuring a row in the output when no corresponding row exists on the right, of course; you can select those rows which have specifically been selected that way by checking for NULL for the "other part" of the row, the part that would normally come from the right-side table.

例如考虑一个具有列ID，主键，Name，Category和Cost的表Product；您需要有关其类别中最便宜的产品的信息.然后...:

Consider for example a table Product with columns ID, primary key, Name, Category, and Cost; you want info about products that are cheapest in their category. Then...:

SELECT P1.Name, P1.Category, P1.Cost
  FROM Product AS P1
  LEFT JOIN Product AS P2
  ON (P1.Category = P2.Category and P1.Cost > P2.Cost)
  WHERE P2.ID IS NULL

是表与其自身的左连接"的示例，它将回答您想要的"规范(如果在一个类别中有多个项目具有最低的同等成本，您将得到全部-查询实际上为您提供了这样的商品，使得其类别中的任何商品都不是更便宜的商品，并且不检查等于成本的商品；-).

is an example of a "left join of a table to itself" which will answer the "you want" spec (if more than one item has the equal-lowest cost in a category you'll get them all -- the query actually gives you the items such that no item in their category is cheaper and has no checks for items of equal cost;-).

这篇关于在MySQL中学习LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！