Oracle 8i:
SQL> select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4 as a from dual;
A B
---------- ----------
1 2
3 4
SQL> select * from (select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4
as a from dual) where b = 3;
no rows selected
SQL> select * from (select 1 as a, 2 as b from dual UNION ALL select 3 as b, 4
as a from dual) where b = 4;
A B
---------- ----------
3 4
----- Original Message ----
From: Nemanja Corlija <[EMAIL PROTECTED]>
To: [email protected]
Sent: Saturday, March 25, 2006 9:03:27 AM
Subject: Re: [sqlite] Column names in a UNION
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]>
