MySQL 使用左连接替换not in

　　众所周知，左连接和右连接的含义是以哪一张表为准。

　　左连接就是以左表为准，查出的结果中包含左表所有的记录，如果右表中没有与其对应的记录，那么那一行记录中B表部分的内容就全是NULL。

　　

　　

　　现在有两个表，一个category表和goods表：

mysql> select * from category;
+----+---------+
| id | cate    |
+----+---------+
|  1 | food    |
|  2 | clothes |
|  3 | book    |
|  4 | sport   |
|  5 | music   |
|  6 | video   |
+----+---------+
6 rows in set (0.01 sec)

mysql> select * from goods;
+----+---------+-------------------+
| id | cate_id | name              |
+----+---------+-------------------+
|  0 |       5 | You Are Not Alone |
|  1 |       2 | T-shirt           |
|  2 |       1 | water             |
|  3 |       1 | rice              |
|  4 |       3 | C++ primer        |
|  5 |       4 | basketbal         |
+----+---------+-------------------+
6 rows in set (0.00 sec)

　　

　　现在要查出每一种分类下的商品，那么可以很简单的使用左连接了：

mysql> select * from category
    -> left join goods
    -> on category.id = goods.cate_id
    -> order by category.id;
+----+---------+------+---------+-------------------+
| id | cate    | id   | cate_id | name              |
+----+---------+------+---------+-------------------+
|  1 | food    |    2 |       1 | water             |
|  1 | food    |    3 |       1 | rice              |
|  2 | clothes |    1 |       2 | T-shirt           |
|  3 | book    |    4 |       3 | C++ primer        |
|  4 | sport   |    5 |       4 | basketbal         |
|  5 | music   |    0 |       5 | You Are Not Alone |
|  6 | video   | NULL |    NULL | NULL              |
+----+---------+------+---------+-------------------+
7 rows in set (0.01 sec)

　　从上面的结果中很全就能看到video分类中没有商品。

 

　　需求：只查询哪一种分类下面没有商品

　　这个很好实现，可以用下面几个方法：

　　1、使用not in

mysql> select * from category where id not in ( select cate_id from goods);
+----+-------+
| id | cate  |
+----+-------+
|  6 | video |
+----+-------+
1 row in set (0.01 sec)

　　2、仍旧使用左连接，只不过对于结果加一个where筛选

mysql> select * from category
    -> left join goods
    -> on category.id = goods.cate_id
    -> where goods.id is NULL;
+----+-------+------+---------+------+
| id | cate  | id   | cate_id | name |
+----+-------+------+---------+------+
|  6 | video | NULL |    NULL | NULL |
+----+-------+------+---------+------+
1 row in set (0.00 sec)

　　

　　至于为什么不适用not in，这是因为他不使用索引，如果数据量大的时候，效率并不高。

　　同样，如果要显示和总表中匹配了记录，隐藏左表没有匹配到的记录，可以将后面的where goods.id is not NULL;

　　同样，对于右连接来说也是一样的。