Scalar sub queries

Question

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);

when i am executing in MySql DB its giving syntax error like 1065 the select statement is not executing, this is call scalar sub queries according to MySql follow link for more info http://dev.mysql.com/doc/refman/5.1/en/scalar-subqueries.html

Answer 1

To return a scalar value your subquery must return ONE row and ONE field. So you should limit rows in your subquery using WHERE or using MySQL LIMIT statement. For example:

SELECT (select s2 FROM t1 limit 1);

SQLFiddle example