Derived Tables

Description

This topic describes subqueries that occur as nested SELECT statements in the FROM clause of an outer SELECT statement. Such subqueries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.

Syntax

Every table in a FROM clause must have a name, therefore the [AS] name clause is mandatory. Any columns in the subquery select list must have unique names.

  1. > SELECT ... FROM (subquery) [AS] name ...

Examples

  1. > CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT);
  2. > INSERT INTO tb1 VALUES (1, '1', 1.0);
  3. > INSERT INTO tb1 VALUES (2, '2', 2.0);
  4. > INSERT INTO tb1 VALUES (3, '3', 3.0);
  5. > select * from tb1;
  6. +------+------+--------+
  7. | c1   | c2   | c3     |
  8. +------+------+--------+
  9. |    1 | 1    | 1.0000 |
  10. |    2 | 2    | 2.0000 |
  11. |    3 | 3    | 3.0000 |
  12. +------+------+--------+
  13. 3 rows in set (0.03 sec)
  15. > SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1;
  16. +------+------+--------+
  17. | sc1  | sc2  | sc3    |
  18. +------+------+--------+
  19. |    2 | 2    | 6.0000 |
  20. |    3 | 3    | 9.0000 |
  21. +------+------+--------+
  22. 2 rows in set (0.02 sec)
  • Subquery with Join:
  1. > create table t1 (libname1 varchar(21) not null primary key, city varchar(20));
  2. > create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60));
  3. > create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int);
  4. > insert into t2 values ('001','Daffy','Aducklife');
  5. > insert into t2 values ('002','Bugs','Arabbitlife');
  6. > insert into t2 values ('003','Cowboy','Lifeontherange');
  7. > insert into t2 values ('000','Anonymous','Wannabuythisbook?');
  8. > insert into t2 values ('004','BestSeller','OneHeckuvabook');
  9. > insert into t2 values ('005','EveryoneBuys','Thisverybook');
  10. > insert into t2 values ('006','SanFran','Itisasanfranlifestyle');
  11. > insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
  12. > insert into t3 values('000','NewYorkPublicLibra',1);
  13. > insert into t3 values('001','NewYorkPublicLibra',2);
  14. > insert into t3 values('002','NewYorkPublicLibra',3);
  15. > insert into t3 values('003','NewYorkPublicLibra',4);
  16. > insert into t3 values('004','NewYorkPublicLibra',5);
  17. > insert into t3 values('005','NewYorkPublicLibra',6);
  18. > insert into t3 values('006','SanFransiscoPublic',5);
  19. > insert into t3 values('007','BerkeleyPublic1',3);
  20. > insert into t3 values('007','BerkeleyPublic2',3);
  21. > insert into t3 values('001','NYC Lib',8);
  22. > insert into t1 values ('NewYorkPublicLibra','NewYork');
  23. > insert into t1 values ('SanFransiscoPublic','SanFran');
  24. > insert into t1 values ('BerkeleyPublic1','Berkeley');
  25. > insert into t1 values ('BerkeleyPublic2','Berkeley');
  26. > insert into t1 values ('NYCLib','NewYork');
  27. > select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ;
  28. +----------+--------------------+------+
  29. | city     | libname1           | a    |
  30. +----------+--------------------+------+
  31. | NewYork  | NewYorkPublicLibra |    6 |
  32. | SanFran  | SanFransiscoPublic |    1 |
  33. | Berkeley | BerkeleyPublic1    |    1 |
  34. | Berkeley | BerkeleyPublic2    |    1 |
  35. +----------+--------------------+------+
  36. 4 rows in set (0.00 sec)

Constraints

MatrixOne does not support selecting multiple columns for the subquery.