exists : 强调的是是否返回结果集,返回的结果只有两种值,要么为真要么为假.例如以下两个查询子句的查询效果是相同的,与select的字段没有关系。
select id from test a where mark exists(select 1 from test b where a.id=b.id);
select id from test a where mark exists(select 2 from test b where a.id=b.id);
in : 强调的是返回存在的结果集,而且查询子句只能返回一个字段中符合条件的内容
select id from test a where mark in (select mark from test b where a.id=b.id ..);
以下表为例,用sql查找itemsSold相同的product:
mysql> desc productSummary;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key |Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| product | varchar(50) | NO | PRI | NULL | |
| itemsSold | int(11) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from productSummary;
+---------+-----------+
| product | itemsSold |
+---------+-----------+
| a | 1 |
| c | 1 |
| d | 7 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from productSummary a where exists(
select 1 from productSummary b wherea.itemsSold=b.itemsSold );
+---------+-----------+
| product | itemsSold |
+---------+-----------+
| a | 1 |
| c | 1 |
| d | 7 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from productSummary a where exists(
select 1 from productSummary b wherea.itemsSold=b.itemsSold and a.product!=b.product);
+---------+-----------+
| product | itemsSold |
+---------+-----------+
| a | 1 |
| c | 1 |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from productSummary a where itemsSold in (
select itemsSold from productSummaryb where a.itemsSold=b.itemsSold and a.product!=b.product);
+---------+-----------+
| product | itemsSold |
+---------+-----------+
| a | 1 |
| c | 1 |
+---------+-----------+
2 rows in set (0.00 sec)