Blog

Scalar Subqueries

Posted by Kylin Soong on Jan 18, 2017 categorized as teiid

Scalar Subqueries

What’s the Scalar Subqueries? In general terms, a scalar subquery resembles a scalar function. Remember that a scalar function returns a single value, given an argument which is some sort of expression — well, a scalar subquery returns a single value, given an argument which is a . Since that value must be a scalar value,

Examples

CREATE TABLE T1 (C1 INT, C2 CHAR(5) NOT NULL);
INSERT INTO T1 VALUES(100, 'abcde');
CREATE TABLE T2 (C1 INT, C2 CHAR(5) NOT NULL);
INSERT INTO T2 VALUES(100, 'abcde');

-- scalar subquery in a select list
SELECT (SELECT C1 FROM T1) FROM T2;
+---------------------+
| (SELECT C1 FROM T1) |
+---------------------+
|                 100 |
+---------------------+

-- scalar subquery in an UPDATE ... SET clause
UPDATE T1 SET C1 = (SELECT AVG(C1) FROM T2);

-- scalar subquery in a comparison
SELECT * FROM T1 WHERE (SELECT MAX(C1) FROM T1) = (SELECT MIN(C1) FROM T2);
+------+-------+
| C1   | C2    |
+------+-------+
|  100 | abcde |
+------+-------+

-- scalar subquery with arithmetic
INSERT INTO T1 (C1) VALUES (1 + (SELECT C1 FROM T2));

Scalar Subquery Projection

In terms of SQL Query, there always have the concepts of Projection and Selection, which they are 2 opposite noun,

  • Projection means selecting the columns of table
  • Selection means select the rows of table

So how to definite the Scalar Subquery Projection? it’s resembles the Scalar Subquery, just focus on choosing which columns (or expressions) the query shall return.

Examples

SELECT C2 FROM T1 WHERE (SELECT MAX(C1) FROM T1) = (SELECT MIN(C1) FROM T2);
+-------+
| C2    |
+-------+
| abcde |
+-------+