[Tutor] Table Joins

[Terry Carroll]

On Wed, 22 Aug 2007, z machinez wrote:

> Hi All:
> 
> I have the following tables selected from a database:
> 
> a1
> 
> a2
> 
> each table are of the same column length, same col names. How do I combine
> or concatenate these tables ? So, I would like to have
> 
> a3 = a1, a2 # combining all the rows into one formal table

If a1 and a2 are both lists, my first approach would have been to convert
them to sets and take their union (converting to tuples along the way to
make them hashable):

>>> a1 = [[1, 'a'], [2, 'b'], [3, 'c'], [4, 'd'], [5, 'e']]
>>> a2 = [[1, 'a'], [5, 'e'], [9, 'i'], [15, 'o'], [21, 'u']]
>>> t1 = [tuple(x) for x in a1]
>>> t2 = [tuple(x) for x in a2]
>>> s1 = set(t1)
>>> s2 = set(t2)
>>> s3 = s1.union(s2)

You can see the combination is all done now:

>>> s3
set([(5, 'e'), (4, 'd'), (9, 'i'), (3, 'c'), (2, 'b'), (21, 'u'), (1, 'a'), (15, 'o')])

All that's left is to get them back into a list of lists:

>>> a3 = [list(x) for x in list(s3)]
>>> a3
[[5, 'e'], [4, 'd'], [9, 'i'], [3, 'c'], [2, 'b'], [21, 'u'], [1, 'a'], [15, 'o']]

And you can sort them if you want a more rational order:

>>> a3.sort()
>>> a3
[[1, 'a'], [2, 'b'], [3, 'c'], [4, 'd'], [5, 'e'], [9, 'i'], [15, 'o'], [21, 'u']]

Now, if you want to maintain the origianl two lists' order, interleaving 
as you go, this approach won't work, and you're instead going to have to 
do it with a couple nested loops (I think).

If you want to pull them out of the database as a single table....
I was wondering that myself the other day.  I was planning on looking 
into whether you could just do a FULL OUTER JOIN (which is essentially a 
union operation) on both tables.  I haven't checked that out, yet; you 
might want to look into it.