MySQL SubQuery学习记录

 The Subquery as Scalar Operand

mysql> CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO t1 VALUES(100, 'abcde');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT (SELECT s2 FROM t1);
+---------------------+
| (SELECT s2 FROM t1) |
+---------------------+
| abcde               |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT (SELECT s2, s1 FROM t1);
ERROR 1241 (21000): Operand should contain 1 column(s)


mysql> insert into t1 values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT (SELECT s2, s1 FROM t1);

ERROR 1241 (21000): Operand should contain 1 column(s)

mysql> select * from t1 where (2,3) = (select s1,3 from t2);
ERROR 1242 (21000): Subquery returns more than 1 row。 正确的姿势见后文参考链接。

mysql> select * from t1 where (2,3) = (select s1,3 from t2 limit 1);
+------+
| s1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t1 where (2,3) IN (select s1,3 from t2);
+------+
| s1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MySQL SubQuery有这么几个重要概念：

1. 驱动顺序：外表驱动内表

2. Vector

3. 向量

4. ANY/ALL的概念

5. 关联查询

ref: https://dev.mysql.com/doc/refman/5.6/en/subquery-errors.html