Paste(1) In Sql

How in SQL could you 'zip' together records in separate tables (à la the UNIX paste(1) utility)? For example, assuming two tables, A and B, like so: A B ======== ==

Solution 1:

In SQL Server 2005, Oracle 9i and PostgreSQL 8.4 and higher:

SELECT  name, num
FROM    (
        SELECT  name, ROW_NUMBER() OVER (ORDERBY id) AS rn
        FROM    a
        ) qa
LEFT JOIN
        (
        SELECT  num, ROW_NUMBER() OVER (ORDERBY id) AS rn
        FROM    b
        ) qb
ON      qb.rn = qa.rn
ORDERBY
        qa.rn

Note that ROW_NUMBER() requires that the records are explicitly sorted.

If you don't have a column similar to id, you cannot sort the records in order other than alphabetical, since relational databases have no concept of implicit record order.

Solution 2:

Without some identifying information to associate rows in one to another you shouldn't. Order or records in SQL should not matter, now if you altered your table to be like.

 ID | NAME
===============
1 | Harkness
2 | Costello
3 | Sato 
4 | Harper
5 | Jones

ID | Num
=========
 1| uno
2 | du
3 | tri

Then a simple

SELECT*FROM A LEFTJOIN B ON A.ID=B.ID;