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