在我们的工作中可能会遇到这样的情形：
我们需要查询a表里面的数据，但是要以b表作为约束。
举个例子，比如我们需要查询订单表中的数据，但是要以用户表为约束，也就是查询出来的订单的user_id要在用户表里面存在才返回。
表结构和表数据如下：
table1 usertb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type
| Null | Key | Default | Extra
|
+-------+-------------+------+-----+---------+----------------+
| id | int(11)
| NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES |
| NULL |
|
+-------+-------------+------+-----+---------+----------------+
+----+-----------+
| id | name
|
+----+-----------+
| 1 | panchao |
| 2 | tangping |
| 3 | yinkaiyue |
+----+-----------+
table2 ordertb;
+------------+-------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+-------------+------+-----+---------+----------------+
| id
| int(11)
| NO | PRI | NULL | auto_increment |
| user_id | int(11)
| YES |
| NULL |
|
| order_name | varchar(50) | YES |
| NULL |
|
+------------+-------------+------+-----+---------+----------------+
+----+---------+-------------------+
| id | user_id | order_name
|
+----+---------+-------------------+
| 1 |
1 | tangping's order |
| 2 |
2 | yinkaiyue's order |
| 3 |
0 | zhangtian's order |
+----+---------+-------------------+
看过表过后，大家在脑海中可能已经想出了很多方法了，对吧。
主要三种方法：left join、in、exists。
我们分别来看看。他们的查询结果和explain的结果。
1、left join：
MariaDB [test]> select * from ordertb a left join usertb b on a.user_id = b.id;
+----+---------+-------------------+------+----------+
| id | user_id | order_name
| id | name
|
+----+---------+-------------------+------+----------+
| 1 |
1 | tangping's order | 1 | panchao |
| 2 |
2 | yinkaiyue's order | 2 | tangping |
| 3 |
0 | zhangtian's order | NULL | NULL
|
+----+---------+-------------------+------+----------+
MariaDB [test]> explain select * from ordertb a left join usertb b on a.user_id= b.id;
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL
| 3 | |
| 1 | SIMPLE
| b
| eq_ref | PRIMARY
| PRIMARY | 4
| test.a.user_id | 1 | Using where |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
2、in：
MariaDB [test]> select * from ordertb where ordertb.user_id in (select id from usertb);
+----+---------+-------------------+
| id | user_id | order_name
|
+----+---------+-------------------+
| 1 |
1 | tangping's order |
| 2 |
2 | yinkaiyue's order |
+----+---------+-------------------+
MariaDB [test]> explain select * from ordertb where ordertb.user_id in (select id from usertb);
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
|
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| 1 | PRIMARY
| ordertb | ALL | NULL
| NULL | NULL | NULL
| 3 | Using where |
| 1 | PRIMARY
| usertb | eq_ref | PRIMARY
| PRIMARY | 4
| test.ordertb.user_id | 1 | Using index |
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
3、exists：
MariaDB [test]> select * from ordertb where exists(select 1 from usertb where usertb.id = ordertb.user_id);
+----+---------+-------------------+
| id | user_id | order_name
|
+----+---------+-------------------+
| 1 |
1 | tangping's order |
| 2 |
2 | yinkaiyue's order |
+----+---------+-------------------+
MariaDB [test]> explain select * from ordertb where exists(select 1 from usertbwhere usertb.id = ordertb.user_id);
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
|
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| 1 | PRIMARY
| ordertb | ALL | NULL
| NULL | NULL | NULL
| 3 | Using where |
| 1 | PRIMARY
| usertb | eq_ref | PRIMARY
| PRIMARY | 4
| test.ordertb.user_id | 1 | Using index |
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+