On 3/25/06, Joe Wilson <[EMAIL PROTECTED]> wrote: > Since we're on this topic, what do other databases return for these queries? > > sqlite> select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; > a|b > 1|2 > 3|4 mysql> select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; +---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | +---+---+
postgres=# select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a; a | b ---+--- 1 | 2 3 | 4 (2 rows) > sqlite> select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as > a) where b = 3; > b|a > 3|4 mysql> select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 3; Empty set (0.00 sec) postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 3; a | b ---+--- (0 rows) > sqlite> select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as > a) where b = 2; mysql> select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 2; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as b, 4 as a) as foo where b = 2; a | b ---+--- 1 | 2 (1 row) > sqlite> select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as > b) where b = 2; > e|b > 1|2 mysql> select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b = 2; +---+---+ | a | b | +---+---+ | 1 | 2 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b = 2; a | b ---+--- 1 | 2 (1 row) > sqlite> select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as > b) where b > 0; > e|b > 1|2 > 3|4 mysql> select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b > 0; +---+---+ | a | b | +---+---+ | 1 | 2 | | 3 | 4 | +---+---+ postgres=# select * from (select 1 as a, 2 as b UNION ALL select 3 as e, 4 as b) as foo where b > 0; a | b ---+--- 1 | 2 3 | 4 (2 rows) Firebird doesn't seem to support SELECT w/o FROM. -- Nemanja Corlija <[EMAIL PROTECTED]>
