MySQL LEFT JOIN 默认值，数据过滤，排序的处理

MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据，RIGHT JOIN 和 LEFT JOIN 方向相反，其他完全一样，主要理解 LEFT JOIN，RIGHT JOIN 也是一样的。

举个栗子，我们有个商品分销系统，首先系统全局设置了一个默认的分销比例，为了方便，这里就为10%，然后每个商品还可以单独设置分销比率。

我们直接将商品信息存储在 WordPress 默认的 posts 表里面，当该商品的分销比率不同于系统默认值的时候，我们会在 postmeta 表插入一条记录， meta_key 为 commission，meta_value 为该产品独立设置的分销比率。

因为只有部分商品设置了分销比率，我们可以通过 LEFT JOIN 获取商品信息和它的分销比率。

SELECT wp_posts.*, wp_postmeta.meta_value as commission
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish' AND wp_postmeta.meta_key = 'commission'
LIMIT 0, 10

什么时候过滤数据

上面的 SQL 语句返回空，这里就出现了第一个问题，首先要明确一下是数据过滤是在 JOIN 之前过滤还是 JOIN 之后过滤的。

• JOIN 之前过滤的，放到 on 子句中。
• JOIN 之后过滤的，放到 where 子句中。

我们这里是在 JOIN 之前要先过滤单独设置 commisson 的 meta 数据，所以 wp_postmeta.meta_key = 'commission' 要放到 ON 子句中：

SELECT wp_posts.*, wp_postmeta.meta_value as commission
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'commission'
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
LIMIT 0, 10

用默认值取代 null

上面 SQL 可以返回商品信息和他分销比率，但是没有单独设置的分销比率的商品返回结果是 null，能否使用默认的分销比率代替 null 值呢？

SELECT wp_posts.*, COALESCE(wp_postmeta.meta_value, 10) as commission
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'commission'
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
LIMIT 0, 10

COALESCE(value,…) 是一个可变参的 SQL 函数，它可以接受多个参数，返回第一个不为 NULL 的参数，如果所有参数都为 NULL，此函数返回 NULL，当它使用 2 个参数时，和 IFNULL 函数作用相同。

排序

使用了默认值之后，我们就可以使用 commission 进行排序了：

SELECT wp_posts.*, COALESCE(wp_postmeta.meta_value, 10) as commission
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'commission'
WHERE wp_posts.post_type = 'product' AND wp_posts.post_status = 'publish'
ORDER BY commission+0 DESC, wp_posts.post_date DESC
LIMIT 0, 10

首先使用 commission 进行排序，相同的商品，则使用发布时间进行排序。