Saturday, June 27, 2009

New Features in DB2

Besides support for FULL OUTER JOIN in the new version of DB2/400, there is another new feature, select from values:
Select * From (Values(1),(2),(3),(4),(5)) As Counter_Table (RowNo)
+-----+
|RowNo|
+-----+
|  1  |
|  2  |
|  3  |
|  4  |
|  5  |
+-----+
Trying to mimic this in MySQL, I used union:
mysql> select * from (select 1 AS 'One', 2 AS 'Two', 3 AS 'Three' UNION select 4, 5, 6) as T1;
+-----+-----+-------+
| One | Two | Three |
+-----+-----+-------+
|   1 |  2  |   3   | 
|   4 |  5  |   6   | 
+-----+-----+-------+
2 rows in set (0.00 sec)
These statements make apparent the similarity of databases and tuples. Documentation of MySQL joins is here.