sql中exists和in的比较

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)