[Tutor] Table Joins

[Ricardo Aráoz]

Terry Carroll wrote:
> 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.
> 

In SQL if you want an union operation you must use UNION (if you want no
repetitions) or UNION ALL (which will output repetitions), both tables
must have the same structure and the names of the output fields will be
those of the first table.
A join is a pairing of the fields of both tables.

a = field1, field2, field3
b = field4, field5, field6

let's say they have 3 records each. Then a join without any condition
(WHERE or ON) would be :

1field1, 1field2, 1field3, 1field4, 1field5, 1field6
1field1, 1field2, 1field3, 2field4, 2field5, 2field6
1field1, 1field2, 1field3, 3field4, 3field5, 3field6
2field1, 2field2, 2field3, 1field4, 1field5, 1field6
2field1, 2field2, 2field3, 2field4, 2field5, 2field6
2field1, 2field2, 2field3, 3field4, 3field5, 3field6
3field1, 3field2, 3field3, 1field4, 1field5, 1field6
3field1, 3field2, 3field3, 2field4, 2field5, 2field6
3field1, 3field2, 3field3, 3field4, 3field5, 3field6

It can get very big in no time.
Now you usually qualify the join. Let's say u say :

select * from a join b on a.field1 = b.field4

Now only the records that fulfill the ON condition will be left, thats
an INNER JOIN. If you want to keep all the records from table 'a' even
if they don't have a corresponding record in table 'b' (b fields will be
NULL) then that's a LEFT JOIN ('b' records that don't have a
corresponding 'a' record will be left out). If you want all records of
table 'b' and only corresponding records from table 'a' that's a RIGHT
JOIN. Finally if you want all records from 'a' and all records from 'b'
to be on your output that's a FULL OUTER JOIN.

HTH