41.笔记 MySQL学习——IN和NOT IN子查询

当子查询要返回多个行来与外层查询进行比较运算时，可以使用运算符IN和NOT IN。会测试给定的比值是否存在于某一组值里。

           例如：

mysql> select * from student where student_id in(select student_id from absence);

+--------+-----+------------+

| name  | sex | student_id |

+--------+-----+------------+

| George | M   |         3 |

| George | F   |         5 |

| George | M   |        10 |

| George | M   |        17 |

| George | F   |        20 |

+--------+-----+------------+

5 rows in set (0.01 sec)

mysql> select * from student where student_id notin (select student_id from absence);

+--------+-----+------------+

| name  | sex | student_id |

+--------+-----+------------+

| George | F   |         1 |

| George | M   |         2 |

| George | F   |         4 |

| George | M   |         6 |

| George | F   |         7 |

| George | M   |         8 |

| George | M   |         9 |

| George | M   |        11 |

| George | M   |        12 |

| George | F   |        13 |

| George | M   |        14 |

| George | F   |        15 |

| George | F   |        16 |

| George | M   |        18 |

| George | F   |        19 |

| George | F   |        21 |

| George | F   |        22 |

| George | M   |        23 |

| George | F   |        24 |

| George | M   |        25 |

| George | M   |        26 |

| George | M   |        27 |

| George | M   |        28 |

| George | F   |        29 |

| George | F   |        30 |

| George | F   |        31 |

+--------+-----+------------+

26 rows in set (0.00 sec)

实际上IN和NOT IN就是= ANY 和 ALL的同义词。