sql - What's a zip join? Have you ever heard of that, or a pairwise join?

Question

This section of Slick's documentation page is quite odd.

What is this zip join? It says that it means:

a pairwise join of two queries

but what that means @.@ I don't know

I've tried Googling for both "zip join" and "pairwise join"... but no results to do with databases.

I do get this from Wikipedia when I search on "pairwise" though...

Could somebody give me some examples illustrating the differences between a zip join and a normal outer or inner join? Thanks!

Answer 1

Zip joins are only meaningful when talking about ordered sets. Instead of joining based on the value of a column, you are joining based on the row number.

Table1

[λ]  [color] 
400  violet 
415  indigo 
475  blue   
510  green  
570  yellow 
590  orange 
650  red    

Table2

[flame]  [element]
green    boron
yellow   sodium
white    magnesium
red      calcium
blue     indium

Table1 INNER JOIN Table2 ON [color] = [flame] : only matching rows

[λ]  [color]  [flame]  [element]
475  blue     blue     indium 
510  green    green    boron
570  yellow   yellow   sodium
650  red      red      calcium

Table1 OUTER JOIN Table2 ON [color] = [flame] : all rows, matched where possible

[λ]  [color]  [flame]  [element]
400  violet   NULL     NULL
415  indigo   NULL     NULL
475  blue     blue     indium
510  green    green    boron
570  yellow   yellow   sodium
590  orange   NULL     NULL
650  red      red      calcium
NULL NULL     white    magnesium

Table1 "zip joined" to Table2 : all rows, regardless of match

[λ]  [color]  [flame]  [element]
400  violet   green    boron
415  indigo   yellow   sodium
475  blue     white    magnesium
510  green    red      calcium
570  yellow   blue     indium
590  orange   NULL     NULL
650  red      NULL     NULL

Zip joins are combining the data like a zipper, pairing the first row from one table with the first row from the other, second paired with second, etc. It's not actually looking at that data. They can be generated very quickly, but they won't mean anything unless there is some meaningful order already present in your data or if you just want to generate random pairings