MySQL中 in, some, all等用法的讨论

最新推荐文章于 2021-11-30 16:51:52 发布
最新推荐文章于 2021-11-30 16:51:52 发布
阅读量965 收藏
点赞数
文章标签： database mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gerogeya/article/details/8147194
版权
(本文中SQL语句均在Mysql v5.1中验证)




mysql> select * from t1;
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)


mysql> select * from t2;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |     10 |    100 |
|  2 |   NULL |    100 |
|  3 |      5 |     80 |
|  4 |     11 |     11 |
|  6 |      5 |     80 |
|  7 |      5 |      5 |
+----+--------+--------+
6 rows in set (0.00 sec)






把所有执行结果再看一遍,把问题写上






***************************************   in   ************************************************
SQL中，连接词 in 是用来测试“元组”是否是“集合”中的成员，即该元组是否属于指定集合。
其中，“元组”可以是由某个关系中的若干属性组成（如1等），或可由这些属性和一些常量组成（如2等），或者直接由若干常量组成（如4等）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列n元组组成）（如1等），或者由一些常量n元组构成此集合（如7，8等）。
需要注意的是，这个属于关系的判断要满足“元素”和“集合”的关系。如元素是一个二元组(如:(id, value))，则相对应的集合就应该是以二元组为元素的集合，（如:((id1, value1), (id2, value2),(id3, value3))），
这样才会形成一个有效的判断。由 select 语句返回的关系就是这样一个集合，该集合由若干属性构成的n元组组成。
实际上，in之前的元组的模式就是in之后的集合中元素的样子，（如12：in之前是一个三元组，则in之后的集合就是由若干三元组构成的）（又如21：in之前的元素是这个样子((1,10),(3,4))，则in之后的集合的元素就应该与它样子相同），这样才会在语法上不出问题。




(1)mysql> select * from t1 where (id, value1) in (select id, value1 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)






(2)mysql> select * from t1 where (id, value1,100) in (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1,101) in (select id, value1,value2 from t2);
Empty set (0.00 sec)






(4)mysql> select * from t1 where (3,5,80) in (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)






(5)mysql> select * from t1 where (3,5,81) in (select id, value1,value2 from t2);
Empty set (0.00 sec)






(6)mysql> select * from t1 where (id,value1) in (1,10);
ERROR 1241 (21000): Operand should contain 2 column(s)




(7)mysql> select * from t1 where (id,value1) in ((1,10));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)






(8)mysql> select * from t1 where (id,value1) in ((1,10),(4,3));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)






(9)mysql> select * from t1 where (id,value1) in ((1,10),(4,5));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(10)mysql> select * from t1 where (id,value1,100) in (1,10,100);
ERROR 1241 (21000): Operand should contain 3 column(s)




(11)mysql> select * from t1 where (id,value1,100) in ((1,10,100));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(12)mysql> select * from t1 where (id,value1,100) in ((1,10,100),(4,3,100));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(13)mysql> select * from t1 where (id,value1,100) in ((1,10,100),(4,5,100));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)






(14)mysql> select * from t1 where (1,10,100) in (1,10,100);
ERROR 1241 (21000): Operand should contain 3 column(s)




(15)mysql> select * from t1 where (1,10,100) in ((1,10,100));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)






(16)mysql> select * from t1 where (1,10,100) in ((1,10,100),(4,3,100));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(17)mysql> select * from t1 where (1,10,100) in ((1,10,100),(4,5,100));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(18)mysql> select * from t1 where ((1,10),(3,4)) in ((1,10),(3,4));
ERROR 1241 (21000): Operand should contain 2 column(s)






(19)mysql> select * from t1 where ((1,10),(3,4)) in (((1,10),(3,4)));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(20)mysql> select * from t1 where ((1,10),(3,4)) in (((1,10),(3,5)));
Empty set (0.00 sec)




(21)mysql> select * from t1 where ((1,10),(3,4)) in (((1,10),(3,5)),((1,2),(3,4)));
Empty set (0.00 sec)




**********************************************************************************************






***************************************  not in   ************************************************
not in 的执行说明与 in 完全相同，且这些SQL语句一一对应，部分互为补集。不互为补集的那部分是因为null值参与计算，从而造成了在做比较时产生了unknown值。


SQL中，连接词 not in 是用来测试“元组”是否不是“集合”中的成员，即该元组是否不属于指定集合。
其中，“元组”可以是由某个关系中的若干属性组成（如1等），或可由这些属性和一些常量组成（如2等），或者直接由若干常量组成（如4等）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列n元组组成）（如1等），或者由一些常量n元组构成此集合（如7，8等）。
需要注意的是，这个属于关系的判断要满足“元素”和“集合”的关系。如元素是一个二元组(如:(id, value))，则相对应的集合就应该是以二元组为元素的集合，（如:((id1, value1), (id2, value2),(id3, value3))），
这样才会形成一个有效的判断。由 select 语句返回的关系就是这样一个集合，该集合由若干属性构成的n元组组成。
实际上，in之前的元组的模式就是in之后的集合中元素的样子，（如12：in之前是一个三元组，则in之后的集合就是由若干三元组构成的）（又如21：in之前的元素是这个样子((1,10),(3,4))，则in之后的集合的元素就应该与它样子相同），这样才会在语法上不出问题。




(1)mysql> select * from t1 where (id, value1) not in (select id, value1 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(2)mysql> select * from t1 where (id, value1,100) not in (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1,101) not in (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(4)mysql> select * from t1 where (3,5,80) not in (select id, value1,value2 from t2);
Empty set (0.00 sec)




(5)mysql> select * from t1 where (3,5,81) not in (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(6)mysql> select * from t1 where (id,value1) not in (1,10);
ERROR 1241 (21000): Operand should contain 2 column(s)




(7)mysql> select * from t1 where (id,value1) not in ((1,10));
+----+--------+
| id | value1 |
+----+--------+
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
3 rows in set (0.00 sec)




(8)mysql> select * from t1 where (id,value1) not in ((1,10),(4,3));
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
+----+--------+
1 row in set (0.00 sec)




(9)mysql> select * from t1 where (id,value1) not in ((1,10),(4,5));
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(10)mysql> select * from t1 where (id,value1,100) not in (1,10,100);
ERROR 1241 (21000): Operand should contain 3 column(s)




(11)mysql> select * from t1 where (id,value1,100) not in ((1,10,100));
+----+--------+
| id | value1 |
+----+--------+
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
3 rows in set (0.00 sec)


(12)mysql> select * from t1 where (id,value1,100) not in ((1,10,100),(4,3,100));
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
+----+--------+
1 row in set (0.00 sec)




(13)mysql> select * from t1 where (id,value1,100) not in ((1,10,100),(4,5,100));
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(14)mysql> select * from t1 where (1,10,100) not in (1,10,100);
ERROR 1241 (21000): Operand should contain 3 column(s)




(15)mysql> select * from t1 where (1,10,100) not in ((1,10,100));
Empty set (0.00 sec)




(16)mysql> select * from t1 where (1,10,100) not in ((1,10,100),(4,3,100));
Empty set (0.00 sec)




(17)mysql> select * from t1 where (1,10,100) not in ((1,10,100),(4,5,100));
Empty set (0.00 sec)




(18)mysql> select * from t1 where ((1,10),(3,4)) not in ((1,10),(3,4));
ERROR 1241 (21000): Operand should contain 2 column(s)




(19)mysql> select * from t1 where ((1,10),(3,4)) not in (((1,10),(3,4)));
Empty set (0.00 sec)




(20)mysql> select * from t1 where ((1,10),(3,4)) not in (((1,10),(3,5)));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(21)mysql> select * from t1 where ((1,10),(3,4)) not in (((1,10),(3,5)),((1,2),(3,4)));
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)


**********************************************************************************************




******************************************  =some  **********************************************
SQL中，连接词 =some 是用来测试“元组”是否至少与“集合”中的某一个成员相等，它与 in 等价，即判断该“元组”是否属于指定“集合”。
其中，“元组”可以是由某个关系中的若干属性组成（如1），或可由这些属性和一些常量组成（如2），或者直接由若干常量组成（如4）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列n元组组成）。
      
注意：与 in 和 not in 不同的是，=some 后面那个指定的集合，只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错（如6）。




(1)mysql> select * from t1 where (id, value1) =some (select id, value1 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(2)mysql> select * from t1 where (id, value1,100) =some (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1,101) =some (select id, value1,value2 from t2);
Empty set (0.00 sec)




(4)mysql> select * from t1 where (3,5,80) =some (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(5)mysql> select * from t1 where (3,5,81) =some (select id, value1,value2 from t2);
Empty set (0.00 sec)




(6)mysql> select * from t1 where (id,value1) =some ((1,10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,10))' at line 1


**********************************************************************************************




*****************************************   =any    ************************************************
SQL中，连接词 =any 是用来测试“元组”是否至少与“集合”中的某一个成员相等，它与 =some 和 in 等价，即判断该“元组”是否属于指定“集合”。
其中，“元组”可以是由某个关系中的若干属性组成（如1），或可由这些属性和一些常量组成（如2），或者直接由若干常量组成（如4）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列n元组组成）。
      
注意：与 in 和 not in 不同的是，=any 后面那个指定的集合，只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错（如6）。


(1)mysql> select * from t1 where (id, value1) =any (select id, value1 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(2)mysql> select * from t1 where (id, value1,100) =any (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
+----+--------+
1 row in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1,101) =any (select id, value1,value2 from t2);
Empty set (0.00 sec)




(4)mysql> select * from t1 where (3,5,80) =any (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(5)mysql> select * from t1 where (3,5,81) =any (select id, value1,value2 from t2);
Empty set (0.00 sec)




(6)mysql> select * from t1 where (id,value1) =any ((1,10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,10))' at line 1


**********************************************************************************************




*****************************************   <>all    ************************************************
SQL中，连接词 <>all 是用来测试“元组”是否与“集合”中的所有成员都不相等，它与 not in 等价，即判断该“元组”是否不属于指定“集合”。
其中，“元组”可以是由某个关系中的若干属性组成（如1），或可由这些属性和一些常量组成（如2），或者直接由若干常量组成（如4）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列n元组组成）。
      
注意：与 in 和 not in 不同的是，<>all 后面那个指定的集合，只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错（如6）。


(1)mysql> select * from t1 where (id, value1) <>all (select id, value1 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(2)mysql> select * from t1 where (id, value1,100) <>all (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  3 |     10 |
|  4 |      3 |
+----+--------+
2 rows in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1,101) <>all (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(4)mysql> select * from t1 where (3,5,80) <>all (select id, value1,value2 from t2);
Empty set (0.00 sec)




(5)mysql> select * from t1 where (3,5,81) <>all (select id, value1,value2 from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(6)mysql> select * from t1 where (id,value1) <>all ((1,10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,10))' at line 1


**********************************************************************************************






*****************************************   <>some    ************************************************
SQL中，连接词 <>some 是用来测试一个“属性”的值是否至少不等于一个指定“集合”中的对应成员。
其中，“属性”只能是单个属性，可以是由某个关系中的某个属性组成（如1），或者直接指定一个常量（如2）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列单属性元组组成）。
      
注意：与 =some 不同的是，<>some 前面只能指定一个属性，而且 <>some 后面那个指定的集合，只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错（如3,4）。




(1)mysql> select * from t1 where id <>some (select id from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(2)mysql> select * from t1 where 1 <>some (select id from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1) <>some (select id, value1 from t2);
ERROR 1241 (21000): Operand should contain 1 column(s)




(4)mysql> select * from t1 where id <>some (1,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1




>some, >=some, <some, <=some 亦均只能对一个属性进行操作，否则报错：ERROR 1241 (21000): Operand should contain 1 column(s)
>some, >=some, <some, <=some 后面那个指定的集合，亦均只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错：ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
**********************************************************************************************






*****************************************   <>any    ************************************************
SQL中，连接词 <>any 是用来测试一个“属性”的值是否至少不等于一个指定“集合”中的对应成员。
其中，“属性”只能是单个属性，可以是由某个关系中的某个属性组成（如1），或者直接指定一个常量（如2）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列单属性元组组成）。
      
注意：与 =any 不同的是，<>any 前面只能指定一个属性，而且 <>any 后面那个指定的集合，只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错（如3,4）。




(1)mysql> select * from t1 where id <>any (select id from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(2)mysql> select * from t1 where 1 <>any (select id from t2);
+----+--------+
| id | value1 |
+----+--------+
|  1 |     10 |
|  2 |   NULL |
|  3 |     10 |
|  4 |      3 |
+----+--------+
4 rows in set (0.00 sec)




(3)mysql> select * from t1 where (id, value1) <>any (select id, value1 from t2);
ERROR 1241 (21000): Operand should contain 1 column(s)




(4)mysql> select * from t1 where id <>any (1,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1




>any, >=any, <any, <=any 亦均只能对一个属性进行操作，否则报错：ERROR 1241 (21000): Operand should contain 1 column(s)
>any, >=any, <any, <=any 后面那个指定的集合，亦均只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错：ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
**********************************************************************************************






*****************************************   =all    ************************************************
SQL中，连接词 =all 是用来测试一个“属性”的值是否与指定“集合”中的所有对应成员相等。
其中，“属性”只能是单个属性，可以是由某个关系中的某个属性组成（如1），或者直接指定一个常量（如2）。
      “集合”可以是由 select 语句返回的关系（这个关系是由一系列单属性元组组成）。
      
注意：与 <>any 不同的是，=all 前面只能指定一个属性，而且 =all 后面那个指定的集合，只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错（如3,4）。




(1)mysql> select * from t1 where id =all (select id from t2);
Empty set (0.00 sec)




(2)mysql> select * from t1 where 1 =all (select id from t2);
Empty set (0.00 sec)




(3)mysql> select * from t1 where (id, value1) =all (select id, value1 from t2);
ERROR 1241 (21000): Operand should contain 1 column(s)




(4)mysql> select * from t1 where id =all (1,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1




>all, >=all, <all, <=all 亦均只能对一个属性进行操作，否则报错：ERROR 1241 (21000): Operand should contain 1 column(s)
>all, >=all, <all, <=all 后面那个指定的集合，亦均只能是由 select 语句返回的关系，而不能由一些常量n元组构成，否则会报错：ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
**********************************************************************************************


以上三个 <>some, <>any, =all 中相应的 op+{some|any|all} 只能对一个属性进行操作的说明参见：








************************************  =,<>,>,>=,<,<=   **********************************************************
=,<>,>,>=,<,<= 仅能对元组进行比较，而不能对元组和集合比较，所以在 where 从句中，如果要使用这些操作符，则 where 前后应均为元组，而不能为元组和集合。
而且 where 前如果是一个元组，而且 where 后使用了 select 语句，则该 select 语句也只有在返回单行数据时才可与该元组比较（如5），否则会报错（如6）。




(1)mysql> select * from t2 where (id, value1) = (1,10);
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |     10 |    100 |
+----+--------+--------+
1 row in set (0.00 sec)




(2)mysql> select * from t2 where (id,10) = (1,10);
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |     10 |    100 |
+----+--------+--------+
1 row in set (0.00 sec)




(3)mysql> select * from t2 where (id,11) = (1,10);
Empty set (0.00 sec)




(4)mysql> select * from t2 where (1,10) = (1,10);
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |     10 |    100 |
|  2 |   NULL |    100 |
|  3 |      5 |     80 |
|  4 |     11 |     11 |
|  6 |      5 |     80 |
|  7 |      5 |      5 |
+----+--------+--------+
6 rows in set (0.00 sec)




(5)mysql> select * from t2 where (id, value1) = (select * from t1 where id=1 and value1=10);
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
|  1 |     10 |    100 |
+----+--------+--------+
1 row in set (0.00 sec)




(6)mysql> select * from t2 where (id, value1) = (select * from t1);
ERROR 1242 (21000): Subquery returns more than 1 row


**********************************************************************************************


<b><i>几个说明：</i></b>
1.个人感觉 MySQL 中对 in(=some) 以及 not in(<>all) 的实现是这样的：
  判断某元组对是否属于某关系：(a,b) in(=some) ((a1,b1),(a2,b2)......)   (1)
  实际上是将(1)转换为如下的方式
  (1)  =>  ((a,b)=(a1,b1)) ∨ ((a,b)=(a2,b2)) ∨ ......
       =>  (a=a1 ∧ b=b1)  ∨ (a=a2 ∧ b=b2)  ∨ ......




  判断某元组对是否不属于某关系：(a,b) not in(<>all) ((a1,b1),(a2,b2)......)   (2)
  实际上是将(2)转换为如下的方式
  (2)  =>  ((a,b)≠(a1,b1)) ∧ ((a,b)≠(a2,b2)) ∧ ......
       =>  (a≠a1 ∨ b≠b1)  ∧ (a≠a2 ∨ b≠b2)  ∧ ......
       
  之所以得出这个结论，是根据 not in 部分的sql语句1，3推导出来的。
  not in.(1)中，结果集中没出现(2, NULL)是因为t1中的(2, NULL)在与t2中的(2, NULL)进行not in判断时得到的结果是unknown，故where语句未把t1中的(2, NULL)加入到结果集中。
                (2≠2 ∨ NULL≠NULL) ∧ T ∧ ......  =>  (F ∨ unknown) ∧ T ∧ ......  =>  unknown
   
  not in.(2)中，结果集中出现了(2, NULL)是因为(2, NULL, 101)在与t2中的(2, NULL, 100)进行not in判断时得到的结果是true，故where语句把t1中的(2, NULL)加入到了结果集中。
                (2≠2 ∨ NULL≠NULL ∨ 100≠101) ∧ T ∧ ......  =>  (F ∨ unknown ∨ T) ∧ T ∧ ......  => (T) ∧ T ∧ ......  => T
                可见，在判断not in是否成立时，元组中只要有任意一项不同即可认为它成立，即使该元组中含有NULL值。
  所以如果将sql改为  select * from t1 where (id, value1,100) not in (select id, value1,value2 from t2); 所得结果仍然与not in.(1)相同。








<b><i>几个没搞清的问题：</i></b>
1.关于为何除了 =some, =any, <>all 之外的型如  op{ALL|ANY|SOME} 均不支持多属性的问题，在网上找到了个说法，摘录如下：
  （网址为：http://www.5dlinux.com/article/6/2010/linux_39642.html）
  
  ·对于expr IN (subquery)，expr可以是n-tuple（通过行构造程序语法指定），而且子查询能返回n-tuples个行。
  ·对于expr op {ALL|ANY|SOME} (subquery)，expr必须是标度值，子查询必须是列子查询，不能返回多列行。
  换句话讲，对于返回n-tuples行的子查询，
  支持：(val_1, ..., val_n) IN (subquery)
  但不支持：(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)
  支持针对IN的行比较，但不支持针对其他的行比较，原因在于，IN实施是通过将其重新编写为“=”比较和AND操作的序列完成的。该方法不能用于ALL、ANY或SOME。
  
  但在实际运行运行中，我们发现其实 =some, =any, <>all 也是可以针对多个属性进行的，不知是 MySQL 进行了改进还是怎么的。
  <font color="red">但除了上面三个特例之外，其他的型如 expr op {ALL|ANY|SOME} (subquery) 的查询为何不支持多列还是不太清楚。</font>




2. not in 中的 7 vs 8,9  以及 11 vs 12,13 的问题。
   我将这几个sql分了两组，分别为 7 vs 8,9 以及 11 vs 12,13. 这两组的问题其实都一样，那就是为何7，11的结果集中有(2, NULL)，
   而8，9，12，13的结果集却全都没有，难道仅仅是因为后者(8，9，12，13)比前者(7，11)多出来了一个常量元组？
   
   经过试验，即使(1)改变后者中两个元组的前后位置；(2)增加后者中的元组个数；(2, NULL)都仍然不显示在结果集当中。
   如执行：
   mysql>  select * from t1 where (id,value1) not in ((4,5),(1,10));
   mysql>  select * from t1 where (id,value1) not in ((4,5),(1,10),(100,200));
   mysql> select * from t1 where (id,value1) not in ((1,10),(4,5),(100,200));
   结果仍均为：
   +----+--------+
   | id | value1 |
   +----+--------+
   |  3 |     10 |
   |  4 |      3 |
   +----+--------+




   但是，如果将 not in 后面的常量元组改为由 select 返回的，则(2, NULL)可正常显示在结果集当中。
   mysql>  select * from t1 where (id,value1) not in (select * from t3);
   +----+--------+
   | id | value1 |
   +----+--------+
   |  2 |   NULL |
   |  3 |     10 |
   |  4 |      3 |
   +----+--------+


   其中表 t3 为：
   mysql> select * from t3;
   +------+--------+
   | id   | value1 |
   +------+--------+
   |    1 |     10 |
   |    4 |      5 |
   +------+--------+
   
   若将 t3 变成仅含一行，再执行该语句，(2, NULL)仍可正常显示在结果集当中。
   mysql> select * from t3;
   +------+--------+
   | id   | value1 |
   +------+--------+
   |    1 |     10 |
   +------+--------+


   mysql>  select * from t1 where (id,value1) not in (select * from t3);
   +----+--------+
   | id | value1 |
   +----+--------+
   |  2 |   NULL |
   |  3 |     10 |
   |  4 |      3 |
   +----+--------+
   


   
   
   原因为何，百思不得其解，如果有人知道，麻烦请告知，多谢！















gerogeya
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL入门（ALL）
qq_40703471的博客
03-11 481
数据库 DB database（数据库）：存储数据的仓库，保存了一系列有组织的数据。 DBMS 数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器，管理DB中的数据。常见的数据库管理系统：MySQL，Oracle，SqlServer，DB2 SQL 结构化查询语言：专门用来与数据库通信的语言。 SQL优点： 不是某个特定数据库供应商专有的语言，几乎所有的DBMS都支持SQL 可以进行非常复杂和高级的数据库操作。 数据库的特点： 将数据放到表中，
MySQL中Union子句不支持order by的解决方法
09-10
在MySQL中，`UNION` 子句用于合并两个或多个`SELECT`语句的结果集，但是它并不直接支持在每个`SELECT`语句后直接跟`ORDER BY`子句来对合并后的结果进行排序。这是因为`UNION`操作会先进行数据合并，然后再去除重复的...
MySQL 列子查询及 IN、ANY、SOME 和 ALL 操作符的使用
weixin_34060299的博客
08-20 328
MySQL 列子查询 列子查询是指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。 一个列子查询的例子如下： SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1) 该例子在前文《MySQL 子查询subquery语法与用法实例》已有解释及数据实例，在此不再赘述。 列子查询...
MySQL中的ALL字段
hopyGreat的博客
05-12 1503
ALL字段不是单独出现的，而是和一些其他字段配合使用，例如UNION、聚合函数的参数。 假设存在如下【Products】表： 一、在聚合函数如SUM，MAX，MIN，AVG等中，ALL是默认行为： select sum(prod_price) from products where vend_id=1003; # 92.49 select sum(DISTINCT prod_price) from products where vend_id=1003; # 79.99 selec..
mysql 中的all_mysql中all的用法是什么
weixin_33737110的博客
01-20 5253
mysql中all的用法：1、all是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”；2、all可以与【=、>、>=、】结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于。mysql中all的用法：最近一直在练习MYSQL的多表查询，基本上每个查询语句我都会写至少两次：一次join连接，一次子查询。来对比一下MYSQL在不同的查询方式下的工作方...
sql中any，some，in，not in
qq_41081716的博客
10-02 578
准备两个表：T1(2,3)，T2(1,2,3,4) all：在T2表中查询比T1表中所有id都大的id； select * from T2 where id>all (select id from T1) any：在T2表中查询比T1表中任意一个id大的id；只需大于一个就可以 select * from T2 where id>any(select id from T1) any，some是一样的结果，即父查询中的结果集大于子查询中任意一个结果集中的值,则为...
mysql使用GROUP BY分组实现取前N条记录的方法
09-10
以下将详细讨论三种在MySQL中使用GROUP BY分组获取前N条记录的方法。 ### 方法一：LEFT JOIN + HAVING子句 这种方法利用LEFT JOIN和HAVING子句来找出每个分组内的前两名学生。首先，将原始表与自身进行LEFT JOIN，...
MySQL创建新用户、增加账户的2种方法及使用实例
01-19
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’ IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’%’ IDENTIFIED BY ‘some_pass’ WITH ...
服务器：在树莓派上安装MySQL数据库和简单使用方法 过程详细
06-20
以下将详细讲解这个过程，包括安装、配置、使用MySQL的基本步骤。 首先，确保你的树莓派运行的是最新版本的Raspberry Pi OS。更新系统可以使用以下命令： ```bash sudo apt update sudo apt upgrade ``` 接着，...
Mysql 5.6添加修改用户名和密码的方法
09-09
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION; ``` 这里，`*.*`表示所有数据库和所有表，`WITH GRANT OPTION`允许用户将权限转授给其他用户。 如果只需要特定权限，如只允许...
mysql all_【MySQL】mysql中any，in，some，all的区别
weixin_29024675的博客
01-18 432
子查询就是指在一个select语句中嵌套另一个select语句。any，in，some，all分别是子查询关键词之一，any 可以与=、>、>=、结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。all可以与=、>、>=、结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。他们进行子查询的语法如下：op...
mysql中all运算_SQL All运算符
weixin_39710041的博客
02-18 466
本篇文章帮大家学习SQL All运算符，包含了SQL All运算符使用方法、操作技巧、实例演示和注意事项，有一定的学习价值，大家可以用来参考。在本教程中，您将了解SQL ALL运算符以及如何使用它来将值与一组值进行比较。1. SQL ALL运算符简介SQL ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。以下是SQL ALL运算符的语法：WHERE column_name ...
mysql里all的意思和用法
黄佳俊的博客
11-30 2169
mysql中all的用法：1、all是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”；2、all可以与【=、>、>=、】结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于。 db545fb86b62b97c8cfb69315b9d1e9c.png mysql中all的用法： 最近一直在练习MYSQL的多表查询，基本上每个查询语句我都会写至少两次：一次join连接，一次子查询。来对比一下MYSQL在不同的查询方式下的工作方式和效率。在子查询有any、
MySQL使用IN、EXISTS、ANY、ALL关键字的子查询
热门推荐
pan_junbiao的博客
01-07 1万+
子查询是SELECT查询的另一个查询的附属，MySQL 4.1可以嵌套多个查询，在外面一层的查询中使用里面一层查询产生的结果集。这样就不是执行两个（或者多个）独立的查询，而是执行包含一个（或者多个）子查询的单独查询。 创建图书信息表和编号信息表，用于后续的实例使用。 （1）创建图书信息表，并添加数据。 -- 创建图书信息表 CREATE TABLE tb_book ( id INT AU...
mysql里all什么意思_mysql中all的用法是什么
weixin_29822105的博客
02-08 5408
mysql中all的用法：1、all是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”；2、all可以与【=、>、>=、】结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于。mysql中all的用法：最近一直在练习MYSQL的多表查询，基本上每个查询语句我都会写至少两次：一次join连接，一次子查询。来对比一下MYSQL在不同的查询方式下的工作方...
mysql in查询_mysql 子查询(in,all,exists,any) (**)
weixin_39759890的博客
12-05 652
表信息1 -- 用in操作符与子查询语句来查询所有f_id对应的f_price在10元到20元之间的水果记录(*)select * from fruits where f_id in (select f_id from fruits where f_price between 10 and 20);2 -- 用all操作符与子查询语句来查询所有f_price大于20元的水果记录 (**)selec...
MySQL子查询：ALL、ANY、SOME、EXISTS、IN、比较运算符查询
董哥的黑板报
04-20 1458
本文要用到的样例表 vendors表：存储销售产品的供应商。供应商ID（vend_id）列用来匹配产品和供应商 vend_id为主键，且为自动增量字段 products表：包含产品目录，每行一个产品。每个产品有唯一的ID（prod_id），通过vend_id关联到它的供应商 prod_id为主键 vend_id为外键，关联到vendors的vend_id custo...
mysql all的用法_mysql中all的用法是什么
weixin_36378951的博客
01-19 1272
mysql中all的用法：1、all是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”；2、all可以与【=、>、>=、】结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于。mysql中all的用法：最近一直在练习MYSQL的多表查询，基本上每个查询语句我都会写至少两次：一次join连接，一次子查询。来对比一下MYSQL在不同的查询方式下的工作方...
MySQL|ALL 关键字
5年+算法工程师，分享人工智能前沿技术，与自己的工程经验，欢迎订阅关注。
04-19 451
-- Select invoices larger than all invoices of client 3 USE sql_invoicing; SELECT * FROM invoices WHERE invoice_total >( SELECT MAX(invoice_total) FROM invoices WHERE invoice_id = 3 ) Other solution(with ALL 关键字)： USE sql_invoicing; SELEC
mysql中any、some、all
最新发布
03-16
在 MySQL 中，ANY、SOME 和 ALL 都是用于子查询中的关键字。 ANY 表示子查询中的任何值都可以与外部查询中的值匹配。 SOME 与 ANY 相同，只是在语法上的差别。 ALL 表示子查询中的所有值都需要与外部查询中的值匹配。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值