Hash join: Difference between revisions
Content added Content deleted
m (→{{header|Phix}}: added syntax colouring, marked p2js compatible) |
Thundergnat (talk | contribs) m (syntax highlighting fixup automation) |
||
Line 123: | Line 123: | ||
{{trans|Python}} |
{{trans|Python}} |
||
< |
<syntaxhighlight lang="11l">F hash_join(table1, table2) |
||
DefaultDict[String, [(Int, String)]] h |
DefaultDict[String, [(Int, String)]] h |
||
L(s) table1 |
L(s) table1 |
||
Line 146: | Line 146: | ||
L(row) hash_join(table1, table2) |
L(row) hash_join(table1, table2) |
||
print(row)</ |
print(row)</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 165: | Line 165: | ||
The vertical bars distinguish AppleScript reserved words ('''name''' and '''character''' here) from field name literal strings. |
The vertical bars distinguish AppleScript reserved words ('''name''' and '''character''' here) from field name literal strings. |
||
< |
<syntaxhighlight lang="applescript">use framework "Foundation" -- Yosemite onwards, for record-handling functions |
||
-- HASH JOIN ----------------------------------------------------------------- |
-- HASH JOIN ----------------------------------------------------------------- |
||
Line 296: | Line 296: | ||
set my text item delimiters to dlm |
set my text item delimiters to dlm |
||
return lstParts |
return lstParts |
||
end splitOn</ |
end splitOn</syntaxhighlight> |
||
{{Out}} |
{{Out}} |
||
<pre>{{age:27, |name|:"Jonah", |character|:"Jonah", nemesis:"Whales"}, |
<pre>{{age:27, |name|:"Jonah", |character|:"Jonah", nemesis:"Whales"}, |
||
Line 307: | Line 307: | ||
=={{header|AWK}}== |
=={{header|AWK}}== |
||
<syntaxhighlight lang="awk"> |
|||
<lang AWK> |
|||
# syntax: GAWK -f HASH_JOIN.AWK [-v debug={0|1}] TABLE_A TABLE_B |
# syntax: GAWK -f HASH_JOIN.AWK [-v debug={0|1}] TABLE_A TABLE_B |
||
# |
# |
||
Line 369: | Line 369: | ||
return(tmp) |
return(tmp) |
||
} |
} |
||
</syntaxhighlight> |
|||
</lang> |
|||
<p>TABLE_A input:</p> |
<p>TABLE_A input:</p> |
||
<pre> |
<pre> |
||
Line 402: | Line 402: | ||
=={{header|Bracmat}}== |
=={{header|Bracmat}}== |
||
This solution creates a hash table for the smaller relation in the function <code>join</code>. This function takes as arguments the smallest table, the biggest table and then three pieces of code: two patterns that describe each table's field order and code that generates one row of output. These pieces of code are inserted in a fixed skeleton of code using macro substitution. |
This solution creates a hash table for the smaller relation in the function <code>join</code>. This function takes as arguments the smallest table, the biggest table and then three pieces of code: two patterns that describe each table's field order and code that generates one row of output. These pieces of code are inserted in a fixed skeleton of code using macro substitution. |
||
< |
<syntaxhighlight lang="bracmat">( (27.Jonah) |
||
(18.Alan) |
(18.Alan) |
||
(28.Glory) |
(28.Glory) |
||
Line 458: | Line 458: | ||
) |
) |
||
& |
& |
||
);</ |
);</syntaxhighlight> |
||
Output: |
Output: |
||
<pre> (28.Alan.Ghosts) |
<pre> (28.Alan.Ghosts) |
||
Line 470: | Line 470: | ||
=={{header|C sharp}}== |
=={{header|C sharp}}== |
||
;using LINQ to Objects |
;using LINQ to Objects |
||
< |
<syntaxhighlight lang="csharp">using System; |
||
using System.Collections.Generic; |
using System.Collections.Generic; |
||
using System.Linq; |
using System.Linq; |
||
Line 569: | Line 569: | ||
} |
} |
||
} |
} |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 583: | Line 583: | ||
=={{header|C++}}== |
=={{header|C++}}== |
||
< |
<syntaxhighlight lang="cpp">#include <iostream> |
||
#include <string> |
#include <string> |
||
#include <vector> |
#include <vector> |
||
Line 648: | Line 648: | ||
} |
} |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
<pre>Table A: |
<pre>Table A: |
||
Line 675: | Line 675: | ||
=={{header|Clojure}}== |
=={{header|Clojure}}== |
||
< |
<syntaxhighlight lang="clojure"> |
||
(defn hash-join [table1 col1 table2 col2] |
(defn hash-join [table1 col1 table2 col2] |
||
(let [hashed (group-by col1 table1)] |
(let [hashed (group-by col1 table1)] |
||
Line 696: | Line 696: | ||
(pprint (sort-by :name (hash-join s :name r :name))) |
(pprint (sort-by :name (hash-join s :name r :name))) |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
Line 711: | Line 711: | ||
=={{header|Common Lisp}}== |
=={{header|Common Lisp}}== |
||
< |
<syntaxhighlight lang="lisp">(defparameter *table-A* '((27 "Jonah") (18 "Alan") (28 "Glory") (18 "Popeye") (28 "Alan"))) |
||
(defparameter *table-B* '(("Jonah" "Whales") ("Jonah" "Spiders") ("Alan" "Ghosts") ("Alan" "Zombies") ("Glory" "Buffy"))) |
(defparameter *table-B* '(("Jonah" "Whales") ("Jonah" "Spiders") ("Alan" "Ghosts") ("Alan" "Zombies") ("Glory" "Buffy"))) |
||
Line 727: | Line 727: | ||
(let ((val (car (gethash i *hash-table*)))) |
(let ((val (car (gethash i *hash-table*)))) |
||
(loop for (a b) in val do |
(loop for (a b) in val do |
||
(format t "{~a ~a} {~a ~a}~%" a b i r))))</ |
(format t "{~a ~a} {~a ~a}~%" a b i r))))</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 742: | Line 742: | ||
=={{header|D}}== |
=={{header|D}}== |
||
{{trans|Python}} |
{{trans|Python}} |
||
< |
<syntaxhighlight lang="d">import std.stdio, std.typecons; |
||
auto hashJoin(size_t index1, size_t index2, T1, T2) |
auto hashJoin(size_t index1, size_t index2, T1, T2) |
||
Line 776: | Line 776: | ||
foreach (const row; hashJoin!(1, 0)(table1, table2)) |
foreach (const row; hashJoin!(1, 0)(table1, table2)) |
||
writefln("(%s, %5s) (%5s, %7s)", row[0][], row[1][]); |
writefln("(%s, %5s) (%5s, %7s)", row[0][], row[1][]); |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>(27, Jonah) (Jonah, Whales) |
<pre>(27, Jonah) (Jonah, Whales) |
||
Line 788: | Line 788: | ||
=={{header|Déjà Vu}}== |
=={{header|Déjà Vu}}== |
||
{{trans|Python}} |
{{trans|Python}} |
||
< |
<syntaxhighlight lang="dejavu">hashJoin table1 index1 table2 index2: |
||
local :h {} |
local :h {} |
||
# hash phase |
# hash phase |
||
Line 806: | Line 806: | ||
for row in hashJoin table1 1 table2 0: |
for row in hashJoin table1 1 table2 0: |
||
!. row</ |
!. row</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>[ [ 27 "Jonah" ] [ "Jonah" "Whales" ] ] |
<pre>[ [ 27 "Jonah" ] [ "Jonah" "Whales" ] ] |
||
Line 819: | Line 819: | ||
=={{header|EchoLisp}}== |
=={{header|EchoLisp}}== |
||
Since this is a real, professional application, we build the hash tables in permanent (local) storage. |
Since this is a real, professional application, we build the hash tables in permanent (local) storage. |
||
< |
<syntaxhighlight lang="lisp"> |
||
(define ages '((27 "Jonah") (18 "Alan") (28 "Glory") (18 "Popeye") (28 "Alan"))) |
(define ages '((27 "Jonah") (18 "Alan") (28 "Glory") (18 "Popeye") (28 "Alan"))) |
||
(define nemesis '(("Jonah" "Whales") ("Jonah" "Spiders") ("Alan" "Ghosts") ("Alan" "Zombies") ("Glory" "Buffy"))) |
(define nemesis '(("Jonah" "Whales") ("Jonah" "Spiders") ("Alan" "Ghosts") ("Alan" "Zombies") ("Glory" "Buffy"))) |
||
Line 847: | Line 847: | ||
(n (local-get-value k 'NEMESIS))) |
(n (local-get-value k 'NEMESIS))) |
||
(writeln a n)) |
(writeln a n)) |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
< |
<syntaxhighlight lang="lisp"> |
||
(28 "Alan") ("Alan" "Zombies") |
(28 "Alan") ("Alan" "Zombies") |
||
(28 "Alan") ("Alan" "Ghosts") |
(28 "Alan") ("Alan" "Ghosts") |
||
Line 857: | Line 857: | ||
(27 "Jonah") ("Jonah" "Spiders") |
(27 "Jonah") ("Jonah" "Spiders") |
||
(27 "Jonah") ("Jonah" "Whales") |
(27 "Jonah") ("Jonah" "Whales") |
||
</syntaxhighlight> |
|||
</lang> |
|||
=={{header|ECL}}== |
=={{header|ECL}}== |
||
<syntaxhighlight lang="ecl"> |
|||
<lang ECL> |
|||
LeftRec := RECORD |
LeftRec := RECORD |
||
UNSIGNED1 Age; |
UNSIGNED1 Age; |
||
Line 894: | Line 894: | ||
27 Jonah Spiders |
27 Jonah Spiders |
||
*/ |
*/ |
||
</syntaxhighlight> |
|||
</lang> |
|||
=={{header|Elixir}}== |
=={{header|Elixir}}== |
||
{{trans|Ruby}} |
{{trans|Ruby}} |
||
< |
<syntaxhighlight lang="elixir">defmodule Hash do |
||
def join(table1, index1, table2, index2) do |
def join(table1, index1, table2, index2) do |
||
h = Enum.group_by(table1, fn s -> elem(s, index1) end) |
h = Enum.group_by(table1, fn s -> elem(s, index1) end) |
||
Line 917: | Line 917: | ||
{"Alan", "Zombies"}, |
{"Alan", "Zombies"}, |
||
{"Glory", "Buffy"}] |
{"Glory", "Buffy"}] |
||
Hash.join(table1, 1, table2, 0) |> Enum.each(&IO.inspect &1)</ |
Hash.join(table1, 1, table2, 0) |> Enum.each(&IO.inspect &1)</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 931: | Line 931: | ||
=={{header|Erlang}}== |
=={{header|Erlang}}== |
||
<syntaxhighlight lang="erlang"> |
|||
<lang Erlang> |
|||
-module( hash_join ). |
-module( hash_join ). |
||
Line 949: | Line 949: | ||
dict_find( error, _Key, _Value ) -> []; |
dict_find( error, _Key, _Value ) -> []; |
||
dict_find( {ok, Values}, Key, Value ) -> [{X, {Key, Value}} || X <- Values]. |
dict_find( {ok, Values}, Key, Value ) -> [{X, {Key, Value}} || X <- Values]. |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 963: | Line 963: | ||
=={{header|F_Sharp|F#}}== |
=={{header|F_Sharp|F#}}== |
||
< |
<syntaxhighlight lang="fsharp">[<EntryPoint>] |
||
let main argv = |
let main argv = |
||
let table1 = [27, "Jonah"; |
let table1 = [27, "Jonah"; |
||
Line 985: | Line 985: | ||
|> Seq.toList |
|> Seq.toList |
||
|> printfn "%A" |
|> printfn "%A" |
||
0</ |
0</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>[((27, "Jonah"), ("Jonah", "Whales")); ((27, "Jonah"), ("Jonah", "Spiders")); |
<pre>[((27, "Jonah"), ("Jonah", "Whales")); ((27, "Jonah"), ("Jonah", "Spiders")); |
||
Line 998: | Line 998: | ||
Needs the FMS-SI (single inheritance) library code located here: |
Needs the FMS-SI (single inheritance) library code located here: |
||
http://soton.mpeforth.com/flag/fms/index.html |
http://soton.mpeforth.com/flag/fms/index.html |
||
< |
<syntaxhighlight lang="forth"> |
||
include FMS-SI.f |
include FMS-SI.f |
||
include FMS-SILib.f |
include FMS-SILib.f |
||
Line 1,084: | Line 1,084: | ||
q free: |
q free: |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 1,095: | Line 1,095: | ||
=={{header|Go}}== |
=={{header|Go}}== |
||
< |
<syntaxhighlight lang="go">package main |
||
import "fmt" |
import "fmt" |
||
Line 1,125: | Line 1,125: | ||
} |
} |
||
} |
} |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 1,140: | Line 1,140: | ||
Semi-imperative style: |
Semi-imperative style: |
||
<syntaxhighlight lang="groovy"> |
|||
<lang Groovy> |
|||
def hashJoin(table1, col1, table2, col2) { |
def hashJoin(table1, col1, table2, col2) { |
||
Line 1,156: | Line 1,156: | ||
q |
q |
||
} |
} |
||
</syntaxhighlight> |
|||
</lang> |
|||
More functional style: |
More functional style: |
||
<syntaxhighlight lang="groovy"> |
|||
<lang Groovy> |
|||
def hashJoin(table1, col1, table2, col2) { |
def hashJoin(table1, col1, table2, col2) { |
||
Line 1,168: | Line 1,168: | ||
}.flatten() |
}.flatten() |
||
} |
} |
||
</syntaxhighlight> |
|||
</lang> |
|||
Sample run (either version as the result is the same): |
Sample run (either version as the result is the same): |
||
<syntaxhighlight lang="groovy"> |
|||
<lang Groovy> |
|||
def s = [[age: 27, name: 'Jonah'], |
def s = [[age: 27, name: 'Jonah'], |
||
[age: 18, name: 'Alan'], |
[age: 18, name: 'Alan'], |
||
Line 1,185: | Line 1,185: | ||
hashJoin(s, "name", r, "name").sort {it.name}.each { println it } |
hashJoin(s, "name", r, "name").sort {it.name}.each { println it } |
||
</syntaxhighlight> |
|||
</lang> |
|||
produces: |
produces: |
||
Line 1,206: | Line 1,206: | ||
Placing all relations with the same selector value in a list in the hashtable allows us to join many to one/many relations. |
Placing all relations with the same selector value in a list in the hashtable allows us to join many to one/many relations. |
||
< |
<syntaxhighlight lang="haskell">{-# LANGUAGE LambdaCase, TupleSections #-} |
||
import qualified Data.HashTable.ST.Basic as H |
import qualified Data.HashTable.ST.Basic as H |
||
import Data.Hashable |
import Data.Hashable |
||
Line 1,234: | Line 1,234: | ||
("Alan", "Ghosts"), ("Alan", "Zombies"), ("Glory", "Buffy")] |
("Alan", "Ghosts"), ("Alan", "Zombies"), ("Glory", "Buffy")] |
||
fst |
fst |
||
</syntaxhighlight> |
|||
</lang> |
|||
<pre> |
<pre> |
||
((3,"Glory"),("Glory","Buffy")) |
((3,"Glory"),("Glory","Buffy")) |
||
Line 1,244: | Line 1,244: | ||
The task require hashtables; however, a cleaner and more functional solution would be to use Data.Map (based on binary trees): |
The task require hashtables; however, a cleaner and more functional solution would be to use Data.Map (based on binary trees): |
||
< |
<syntaxhighlight lang="haskell">{-# LANGUAGE TupleSections #-} |
||
import qualified Data.Map as M |
import qualified Data.Map as M |
||
import Data.List |
import Data.List |
||
Line 1,262: | Line 1,262: | ||
("Alan", "Ghosts"), ("Alan", "Zombies"), ("Glory", "Buffy")] |
("Alan", "Ghosts"), ("Alan", "Zombies"), ("Glory", "Buffy")] |
||
fst |
fst |
||
</syntaxhighlight> |
|||
</lang> |
|||
<pre> |
<pre> |
||
((1,"Jonah"),("Jonah","Spiders")) |
((1,"Jonah"),("Jonah","Spiders")) |
||
Line 1,275: | Line 1,275: | ||
Data: |
Data: |
||
< |
<syntaxhighlight lang="j">table1=: ;:;._2(0 :0) |
||
27 Jonah |
27 Jonah |
||
18 Alan |
18 Alan |
||
Line 1,289: | Line 1,289: | ||
Alan Zombies |
Alan Zombies |
||
Glory Buffy |
Glory Buffy |
||
)</ |
)</syntaxhighlight> |
||
The task does not specify the hash function to use, so we'll use an identity function. But [[SHA-1]] could be used instead, with a little more work (you'd need to convert the name into the bit vector needed by the SHA-1 interface). Practically speaking, though, the only benefit of SHA-1 in this context would be to slow down the join. |
The task does not specify the hash function to use, so we'll use an identity function. But [[SHA-1]] could be used instead, with a little more work (you'd need to convert the name into the bit vector needed by the SHA-1 interface). Practically speaking, though, the only benefit of SHA-1 in this context would be to slow down the join. |
||
Line 1,295: | Line 1,295: | ||
Implementation: |
Implementation: |
||
< |
<syntaxhighlight lang="j">hash=: ] |
||
dojoin=:3 :0 |
dojoin=:3 :0 |
||
c1=. {.{.y |
c1=. {.{.y |
||
Line 1,303: | Line 1,303: | ||
) |
) |
||
JOIN=: ; -.&a: ,/each(hash@{."1 <@dojoin/. ]) (1 1 0&#inv@|."1 table1), 1 0 1#inv"1 table2</ |
JOIN=: ; -.&a: ,/each(hash@{."1 <@dojoin/. ]) (1 1 0&#inv@|."1 table1), 1 0 1#inv"1 table2</syntaxhighlight> |
||
Result: |
Result: |
||
< |
<syntaxhighlight lang="j"> JOIN |
||
┌─────┬──┬───────┐ |
┌─────┬──┬───────┐ |
||
│Jonah│27│Whales │ |
│Jonah│27│Whales │ |
||
Line 1,322: | Line 1,322: | ||
├─────┼──┼───────┤ |
├─────┼──┼───────┤ |
||
│Glory│28│Buffy │ |
│Glory│28│Buffy │ |
||
└─────┴──┴───────┘</ |
└─────┴──┴───────┘</syntaxhighlight> |
||
=={{header|Java}}== |
=={{header|Java}}== |
||
{{trans|PHP}} |
{{trans|PHP}} |
||
{{works with|Java|8}} |
{{works with|Java|8}} |
||
< |
<syntaxhighlight lang="java">import java.util.*; |
||
public class HashJoin { |
public class HashJoin { |
||
Line 1,366: | Line 1,366: | ||
return result; |
return result; |
||
} |
} |
||
}</ |
}</syntaxhighlight> |
||
<pre>[[27, Jonah], [Jonah, Whales]] |
<pre>[[27, Jonah], [Jonah, Whales]] |
||
Line 1,379: | Line 1,379: | ||
===ES6=== |
===ES6=== |
||
< |
<syntaxhighlight lang="javascript">(() => { |
||
'use strict'; |
'use strict'; |
||
Line 1,434: | Line 1,434: | ||
})(); |
})(); |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{Out}} |
{{Out}} |
||
Line 1,460: | Line 1,460: | ||
===hashJoin=== |
===hashJoin=== |
||
< |
<syntaxhighlight lang="jq"># hashJoin(table1; key1; table2; key2) expects the two tables to be |
||
# arrays, either of JSON objects, or of arrays. |
# arrays, either of JSON objects, or of arrays. |
||
Line 1,494: | Line 1,494: | ||
reduce $hash[$key][] as $r (.; . + [ $row + $r ] ) |
reduce $hash[$key][] as $r (.; . + [ $row + $r ] ) |
||
else . end) |
else . end) |
||
;</ |
;</syntaxhighlight> |
||
'''Example''' |
'''Example''' |
||
< |
<syntaxhighlight lang="jq">def table1: |
||
[ {"age": 27, "name": "Jonah"}, |
[ {"age": 27, "name": "Jonah"}, |
||
{"age": 18, "name": "Alan"}, |
{"age": 18, "name": "Alan"}, |
||
Line 1,535: | Line 1,535: | ||
( hashJoin(table1; "name"; table2; "name"), |
( hashJoin(table1; "name"; table2; "name"), |
||
hashJoin(table1a; 1; table2a; 0) |
hashJoin(table1a; 1; table2a; 0) |
||
) | pp</ |
) | pp</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
< |
<syntaxhighlight lang="sh">$ jq -c -r -n -f HashJoin.jq |
||
{"age":27,"name":"Jonah","nemesis":"Whales"} |
{"age":27,"name":"Jonah","nemesis":"Whales"} |
||
Line 1,549: | Line 1,549: | ||
[28,"Alan","Alan","Ghosts"] |
[28,"Alan","Alan","Ghosts"] |
||
[28,"Alan","Alan","Zombies"] |
[28,"Alan","Alan","Zombies"] |
||
[28,"Glory","Glory","Buffy"]</ |
[28,"Glory","Glory","Buffy"]</syntaxhighlight> |
||
===hashJoinArrays=== |
===hashJoinArrays=== |
||
< |
<syntaxhighlight lang="jq"># The tables should be arrays of arrays; |
||
# index1 and index2 should be the 0-based indices of the join columns. |
# index1 and index2 should be the 0-based indices of the join columns. |
||
# |
# |
||
Line 1,578: | Line 1,578: | ||
. + [ $r + $row[0:index2] + $row[index2+1:] ] ) |
. + [ $r + $row[0:index2] + $row[index2+1:] ] ) |
||
else . end) |
else . end) |
||
;</ |
;</syntaxhighlight> |
||
'''Example''' |
'''Example''' |
||
In the following example, the previously defined pretty-print function (pp) and tables (table1 and table2) |
In the following example, the previously defined pretty-print function (pp) and tables (table1 and table2) |
||
are used, so their definitions are not repeated here. |
are used, so their definitions are not repeated here. |
||
< |
<syntaxhighlight lang="jq">hashJoinArrays(table1; 1; table2; 0) | pp</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
< |
<syntaxhighlight lang="sh">$ jq -c -r -n -f HashJoinArrays.jq |
||
[27,"Jonah","Whales"] |
[27,"Jonah","Whales"] |
||
Line 1,591: | Line 1,591: | ||
[28,"Alan","Ghosts"] |
[28,"Alan","Ghosts"] |
||
[28,"Alan","Zombies"] |
[28,"Alan","Zombies"] |
||
[28,"Glory","Buffy"]</ |
[28,"Glory","Buffy"]</syntaxhighlight> |
||
=={{header|Julia}}== |
=={{header|Julia}}== |
||
{{works with|Julia|0.6}} |
{{works with|Julia|0.6}} |
||
For dataframes there is a builtin function join: |
For dataframes there is a builtin function join: |
||
< |
<syntaxhighlight lang="julia">using DataFrames |
||
A = DataFrame(Age = [27, 18, 28, 18, 28], Name = ["Jonah", "Alan", "Glory", "Popeye", "Alan"]) |
A = DataFrame(Age = [27, 18, 28, 18, 28], Name = ["Jonah", "Alan", "Glory", "Popeye", "Alan"]) |
||
Line 1,603: | Line 1,603: | ||
AB = join(A, B, on = :Name) |
AB = join(A, B, on = :Name) |
||
@show A B AB</ |
@show A B AB</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 1,634: | Line 1,634: | ||
Following the task hint: |
Following the task hint: |
||
< |
<syntaxhighlight lang="julia">function hashjoin(A::Array, ja::Int, B::Array, jb::Int) |
||
M = Dict(t[jb] => filter(l -> l[jb] == t[jb], B) for t in B) |
M = Dict(t[jb] => filter(l -> l[jb] == t[jb], B) for t in B) |
||
return collect([a, b] for a in A for b in get(M, a[ja], ())) |
return collect([a, b] for a in A for b in get(M, a[ja], ())) |
||
Line 1,652: | Line 1,652: | ||
for r in hashjoin(table1, 2, table2, 1) |
for r in hashjoin(table1, 2, table2, 1) |
||
println(r) |
println(r) |
||
end</ |
end</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 1,664: | Line 1,664: | ||
=={{header|Kotlin}}== |
=={{header|Kotlin}}== |
||
< |
<syntaxhighlight lang="scala">data class A(val age: Int, val name: String) |
||
data class B(val character: String, val nemesis: String) |
data class B(val character: String, val nemesis: String) |
||
Line 1,702: | Line 1,702: | ||
println("${c.rowB.character.padEnd(6)} ${c.rowB.nemesis}") |
println("${c.rowB.character.padEnd(6)} ${c.rowB.nemesis}") |
||
} |
} |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 1,719: | Line 1,719: | ||
===Literal=== |
===Literal=== |
||
Lua tables are implemented with hash keys, so this task is a bit anti-idiomatic for Lua. That is, if you knew in advance that this would be the primary operation on the data, then you'd likely (re-)structure the data to directly support it. But, to comply with the intent of the task, the data here is initially structured as an indexed (rather than hash-keyed) array, <i>then</i> hashed dynamically. (it's analogous to the Python solution, where a list is immediately converted to a dictionary - but could have <i>began</i> as a dictionary) |
Lua tables are implemented with hash keys, so this task is a bit anti-idiomatic for Lua. That is, if you knew in advance that this would be the primary operation on the data, then you'd likely (re-)structure the data to directly support it. But, to comply with the intent of the task, the data here is initially structured as an indexed (rather than hash-keyed) array, <i>then</i> hashed dynamically. (it's analogous to the Python solution, where a list is immediately converted to a dictionary - but could have <i>began</i> as a dictionary) |
||
< |
<syntaxhighlight lang="lua">local function recA(age, name) return { Age=age, Name=name } end |
||
local tabA = { recA(27,"Jonah"), recA(18,"Alan"), recA(28,"Glory"), recA(18,"Popeye"), recA(28,"Alan") } |
local tabA = { recA(27,"Jonah"), recA(18,"Alan"), recA(28,"Glory"), recA(18,"Popeye"), recA(28,"Alan") } |
||
Line 1,741: | Line 1,741: | ||
for _,row in pairs(hashjoin(tabA, "Name", tabB, "Character")) do |
for _,row in pairs(hashjoin(tabA, "Name", tabB, "Character")) do |
||
print(row.A.Age, row.A.Name, row.B.Character, row.B.Nemesis) |
print(row.A.Age, row.A.Name, row.B.Character, row.B.Nemesis) |
||
end</ |
end</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>27 Jonah Jonah Whales |
<pre>27 Jonah Jonah Whales |
||
Line 1,752: | Line 1,752: | ||
===Idiomatic=== |
===Idiomatic=== |
||
Or, at least semi-idiomatic / more-idiomatic, per comments above under the "Literal" implementation. Here, a "hashlist" structure is defined to allow retrieval either by indexed-list style (the database "rows") or by hashed-array-of-lists style (the database "index"), where the hash is maintained upon insert so that a later "hashjoin" operation becomes just a "join" operation. (Note that storage/performance issues are minimal, at least at this scale, as both the keys and rows in the hash are merely references, not copies.) |
Or, at least semi-idiomatic / more-idiomatic, per comments above under the "Literal" implementation. Here, a "hashlist" structure is defined to allow retrieval either by indexed-list style (the database "rows") or by hashed-array-of-lists style (the database "index"), where the hash is maintained upon insert so that a later "hashjoin" operation becomes just a "join" operation. (Note that storage/performance issues are minimal, at least at this scale, as both the keys and rows in the hash are merely references, not copies.) |
||
< |
<syntaxhighlight lang="lua">local hashlist = { |
||
new = function(self,key) |
new = function(self,key) |
||
return setmetatable({key=key, hash={}, list={}}, {__index=self}) |
return setmetatable({key=key, hash={}, list={}}, {__index=self}) |
||
Line 1,797: | Line 1,797: | ||
for _,row in pairs(tabB:join(tabA)) do |
for _,row in pairs(tabB:join(tabA)) do |
||
print(row.B.Age, row.B.Name, row.A.Character, row.A.Nemesis) |
print(row.B.Age, row.B.Name, row.A.Character, row.A.Nemesis) |
||
end</ |
end</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>27 Jonah Jonah Whales |
<pre>27 Jonah Jonah Whales |
||
Line 1,816: | Line 1,816: | ||
=={{header|LFE}}== |
=={{header|LFE}}== |
||
< |
<syntaxhighlight lang="lisp"> |
||
(defun hash (column table) |
(defun hash (column table) |
||
(lists:foldl |
(lists:foldl |
||
Line 1,840: | Line 1,840: | ||
(lc ((<- s (get-hash col-2 hashed))) |
(lc ((<- s (get-hash col-2 hashed))) |
||
(merge r s))))) |
(merge r s))))) |
||
</syntaxhighlight> |
|||
</lang> |
|||
Table definitions in the LFE REPL: |
Table definitions in the LFE REPL: |
||
< |
<syntaxhighlight lang="lisp"> |
||
> (set ss '((#(age 27) #(name "Jonah")) |
> (set ss '((#(age 27) #(name "Jonah")) |
||
(#(age 18) #(name "Alan")) |
(#(age 18) #(name "Alan")) |
||
Line 1,855: | Line 1,855: | ||
(#(nemesis "Zombies") #(name "Alan")) |
(#(nemesis "Zombies") #(name "Alan")) |
||
(#(nemesis "Buffy") #(name "Glory")))) |
(#(nemesis "Buffy") #(name "Glory")))) |
||
</syntaxhighlight> |
|||
</lang> |
|||
Output in LFE REPL: |
Output in LFE REPL: |
||
< |
<syntaxhighlight lang="lisp"> |
||
> (hash-join ss 'name rs 'name) |
> (hash-join ss 'name rs 'name) |
||
(((#(age 27) #(name "Jonah") #(nemesis "Whales"))) |
(((#(age 27) #(name "Jonah") #(nemesis "Whales"))) |
||
Line 1,867: | Line 1,867: | ||
(#(age 28) #(name "Alan") #(nemesis "Zombies"))) |
(#(age 28) #(name "Alan") #(nemesis "Zombies"))) |
||
((#(age 28) #(name "Glory") #(nemesis "Buffy")))) |
((#(age 28) #(name "Glory") #(nemesis "Buffy")))) |
||
</syntaxhighlight> |
|||
</lang> |
|||
=={{header|M2000 Interpreter}}== |
=={{header|M2000 Interpreter}}== |
||
<syntaxhighlight lang="m2000 interpreter"> |
|||
<lang M2000 Interpreter> |
|||
Module HashJoin { |
Module HashJoin { |
||
\\ normally we define variables when we put values to names |
\\ normally we define variables when we put values to names |
||
Line 1,949: | Line 1,949: | ||
} |
} |
||
HashJoin |
HashJoin |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
<pre > |
<pre > |
||
Line 1,967: | Line 1,967: | ||
Updated version is now able to join wider tables by giving the index. The smaller table is hashed but this might result in different column ordering. Uses Associations introduced in Mathematica Version 10 |
Updated version is now able to join wider tables by giving the index. The smaller table is hashed but this might result in different column ordering. Uses Associations introduced in Mathematica Version 10 |
||
< |
<syntaxhighlight lang="mathematica">hashJoin[table1_List,table1colindex_Integer,table2_List,table2colindex_Integer]:=Module[{h,f,t1,t2,tmp}, |
||
t1=If[table1colindex != 1,table1[[All,Prepend[Delete[Range@Length@table1[[1]],table1colindex],table1colindex]]],table1]; |
t1=If[table1colindex != 1,table1[[All,Prepend[Delete[Range@Length@table1[[1]],table1colindex],table1colindex]]],table1]; |
||
t2=If[table2colindex != 1, table2[[All,Prepend[Delete[Range@Length@table2[[1]],table2colindex],table2colindex]]],table2]; |
t2=If[table2colindex != 1, table2[[All,Prepend[Delete[Range@Length@table2[[1]],table2colindex],table2colindex]]],table2]; |
||
Line 1,976: | Line 1,976: | ||
Partition[Flatten[Map[f,{#[[2;;]],h[#[[1]]]}&/@t2 |
Partition[Flatten[Map[f,{#[[2;;]],h[#[[1]]]}&/@t2 |
||
]],Length[t1[[1]]]+Length[t2[[1]]]-1] |
]],Length[t1[[1]]]+Length[t2[[1]]]-1] |
||
];</ |
];</syntaxhighlight> |
||
Sample run: |
Sample run: |
||
<pre> |
<pre> |
||
Line 2,037: | Line 2,037: | ||
=={{header|Nim}}== |
=={{header|Nim}}== |
||
< |
<syntaxhighlight lang="nim">import strformat, tables |
||
type |
type |
||
Line 2,069: | Line 2,069: | ||
for row in hashJoin(table1, table2): |
for row in hashJoin(table1, table2): |
||
echo row.a, " ", row.b</ |
echo row.a, " ", row.b</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 2,082: | Line 2,082: | ||
=={{header|Oberon-2}}== |
=={{header|Oberon-2}}== |
||
Works with oo2c version 2 |
Works with oo2c version 2 |
||
< |
<syntaxhighlight lang="oberon2"> |
||
MODULE HashJoin; |
MODULE HashJoin; |
||
IMPORT |
IMPORT |
||
Line 2,177: | Line 2,177: | ||
DoJoinPhase(tableB,dict); |
DoJoinPhase(tableB,dict); |
||
END HashJoin. |
END HashJoin. |
||
</syntaxhighlight> |
|||
</lang> |
|||
Output: |
Output: |
||
<pre> |
<pre> |
||
Line 2,191: | Line 2,191: | ||
=={{header|OCaml}}== |
=={{header|OCaml}}== |
||
This exploits the fact that Hashtbl implements a hash table that can store multiple values for a key, for an especially simple solution: |
This exploits the fact that Hashtbl implements a hash table that can store multiple values for a key, for an especially simple solution: |
||
< |
<syntaxhighlight lang="ocaml">let hash_join table1 f1 table2 f2 = |
||
let h = Hashtbl.create 42 in |
let h = Hashtbl.create 42 in |
||
(* hash phase *) |
(* hash phase *) |
||
Line 2,198: | Line 2,198: | ||
(* join phase *) |
(* join phase *) |
||
List.concat (List.map (fun r -> |
List.concat (List.map (fun r -> |
||
List.map (fun s -> s, r) (Hashtbl.find_all h (f2 r))) table2)</ |
List.map (fun s -> s, r) (Hashtbl.find_all h (f2 r))) table2)</syntaxhighlight> |
||
Sample run: |
Sample run: |
||
<pre> |
<pre> |
||
Line 2,220: | Line 2,220: | ||
=={{header|Perl}}== |
=={{header|Perl}}== |
||
< |
<syntaxhighlight lang="perl">use Data::Dumper qw(Dumper); |
||
sub hashJoin { |
sub hashJoin { |
||
Line 2,249: | Line 2,249: | ||
foreach my $row (hashJoin(\@table1, 1, \@table2, 0)) { |
foreach my $row (hashJoin(\@table1, 1, \@table2, 0)) { |
||
print Dumper($row), "\n"; |
print Dumper($row), "\n"; |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 2,263: | Line 2,263: | ||
=={{header|Phix}}== |
=={{header|Phix}}== |
||
Phix dictionary keys must be unique, but storing/extending a sequence is no trouble, and in fact simplifies the scan phase. |
Phix dictionary keys must be unique, but storing/extending a sequence is no trouble, and in fact simplifies the scan phase. |
||
<!--< |
<!--<syntaxhighlight lang="phix">(phixonline)--> |
||
<span style="color: #008080;">with</span> <span style="color: #008080;">javascript_semantics</span> |
<span style="color: #008080;">with</span> <span style="color: #008080;">javascript_semantics</span> |
||
<span style="color: #008080;">constant</span> <span style="color: #000000;">A</span> <span style="color: #0000FF;">=</span> <span style="color: #0000FF;">{{</span><span style="color: #000000;">27</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"Jonah"</span><span style="color: #0000FF;">},</span> |
<span style="color: #008080;">constant</span> <span style="color: #000000;">A</span> <span style="color: #0000FF;">=</span> <span style="color: #0000FF;">{{</span><span style="color: #000000;">27</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"Jonah"</span><span style="color: #0000FF;">},</span> |
||
Line 2,299: | Line 2,299: | ||
<span style="color: #7060A8;">destroy_dict</span><span style="color: #0000FF;">(</span><span style="color: #000000;">MB</span><span style="color: #0000FF;">)</span> |
<span style="color: #7060A8;">destroy_dict</span><span style="color: #0000FF;">(</span><span style="color: #000000;">MB</span><span style="color: #0000FF;">)</span> |
||
<span style="color: #7060A8;">pp</span><span style="color: #0000FF;">(</span><span style="color: #000000;">C</span><span style="color: #0000FF;">,{</span><span style="color: #004600;">pp_Nest</span><span style="color: #0000FF;">,</span><span style="color: #000000;">1</span><span style="color: #0000FF;">})</span> |
<span style="color: #7060A8;">pp</span><span style="color: #0000FF;">(</span><span style="color: #000000;">C</span><span style="color: #0000FF;">,{</span><span style="color: #004600;">pp_Nest</span><span style="color: #0000FF;">,</span><span style="color: #000000;">1</span><span style="color: #0000FF;">})</span> |
||
<!--</ |
<!--</syntaxhighlight>--> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 2,312: | Line 2,312: | ||
=={{header|PHP}}== |
=={{header|PHP}}== |
||
< |
<syntaxhighlight lang="php"><?php |
||
function hashJoin($table1, $index1, $table2, $index2) { |
function hashJoin($table1, $index1, $table2, $index2) { |
||
// hash phase |
// hash phase |
||
Line 2,338: | Line 2,338: | ||
foreach (hashJoin($table1, 1, $table2, 0) as $row) |
foreach (hashJoin($table1, 1, $table2, 0) as $row) |
||
print_r($row); |
print_r($row); |
||
?></ |
?></syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 2,448: | Line 2,448: | ||
=={{header|PicoLisp}}== |
=={{header|PicoLisp}}== |
||
< |
<syntaxhighlight lang="picolisp">(de A |
||
(27 . Jonah) |
(27 . Jonah) |
||
(18 . Alan) |
(18 . Alan) |
||
Line 2,471: | Line 2,471: | ||
(let? Y (car (idx 'M (cons (char (hash (cdr X))) (cdr X)))) |
(let? Y (car (idx 'M (cons (char (hash (cdr X))) (cdr X)))) |
||
(for Z (caar Y) |
(for Z (caar Y) |
||
(println (car X) (cdr X) (cdr Y) Z) ) ) )</ |
(println (car X) (cdr X) (cdr Y) Z) ) ) )</syntaxhighlight> |
||
Output: |
Output: |
||
<pre>27 Jonah Jonah Spiders |
<pre>27 Jonah Jonah Spiders |
||
Line 2,483: | Line 2,483: | ||
=={{header|plainTeX}}== |
=={{header|plainTeX}}== |
||
Works with any TeX engine. |
Works with any TeX engine. |
||
< |
<syntaxhighlight lang="tex">\newtoks\tabjoin |
||
\def\quark{\quark} |
\def\quark{\quark} |
||
\def\tabA{27:Jonah,18:Alan,28:Glory,18:Popeye,28:Alan} |
\def\tabA{27:Jonah,18:Alan,28:Glory,18:Popeye,28:Alan} |
||
Line 2,502: | Line 2,502: | ||
} |
} |
||
\mergejoin |
\mergejoin |
||
\bye</ |
\bye</syntaxhighlight> |
||
pdf or dvi output: |
pdf or dvi output: |
||
Line 2,514: | Line 2,514: | ||
=={{header|Prolog}}== |
=={{header|Prolog}}== |
||
< |
<syntaxhighlight lang="prolog">% Name/Age |
||
person_age('Jonah', 27). |
person_age('Jonah', 27). |
||
person_age('Alan', 18). |
person_age('Alan', 18). |
||
Line 2,533: | Line 2,533: | ||
(person_age(Person, Age), character_nemisis(Person, Nemesis)), |
(person_age(Person, Age), character_nemisis(Person, Nemesis)), |
||
format('~w\t~w\t~w\t\t~w\n', [Age, Person, Person, Nemesis]) |
format('~w\t~w\t~w\t\t~w\n', [Age, Person, Person, Nemesis]) |
||
).</ |
).</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 2,550: | Line 2,550: | ||
=={{header|PureBasic}}== |
=={{header|PureBasic}}== |
||
< |
<syntaxhighlight lang="purebasic">Structure tabA |
||
age.i |
age.i |
||
name.s |
name.s |
||
Line 2,596: | Line 2,596: | ||
Next |
Next |
||
Input() |
Input() |
||
EndIf</ |
EndIf</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>Input A = 27 Jonah |
<pre>Input A = 27 Jonah |
||
Line 2,621: | Line 2,621: | ||
=={{header|Python}}== |
=={{header|Python}}== |
||
< |
<syntaxhighlight lang="python">from collections import defaultdict |
||
def hashJoin(table1, index1, table2, index2): |
def hashJoin(table1, index1, table2, index2): |
||
Line 2,643: | Line 2,643: | ||
for row in hashJoin(table1, 1, table2, 0): |
for row in hashJoin(table1, 1, table2, 0): |
||
print(row)</ |
print(row)</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 2,656: | Line 2,656: | ||
=={{header|Racket}}== |
=={{header|Racket}}== |
||
< |
<syntaxhighlight lang="racket">#lang racket |
||
(struct A (age name)) |
(struct A (age name)) |
||
(struct B (name nemesis)) |
(struct B (name nemesis)) |
||
Line 2,684: | Line 2,684: | ||
(key (in-value (B-name b))) |
(key (in-value (B-name b))) |
||
(age (in-list (hash-ref name->ages# key)))) |
(age (in-list (hash-ref name->ages# key)))) |
||
(AB key age (B-nemesis b)))</ |
(AB key age (B-nemesis b)))</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 2,701: | Line 2,701: | ||
{{works with|Rakudo|2016.07}} |
{{works with|Rakudo|2016.07}} |
||
<lang |
<syntaxhighlight lang="raku" line>sub hash-join(@a, &a, @b, &b) { |
||
my %hash := @b.classify(&b); |
my %hash := @b.classify(&b); |
||
Line 2,727: | Line 2,727: | ||
; |
; |
||
.say for hash-join @A, *[1], @B, *[0];</ |
.say for hash-join @A, *[1], @B, *[0];</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 2,739: | Line 2,739: | ||
=={{header|REXX}}== |
=={{header|REXX}}== |
||
< |
<syntaxhighlight lang="rexx">/*REXX program demonstrates the classic hash join algorithm for two relations. */ |
||
S. = ; R. = |
S. = ; R. = |
||
S.1 = 27 'Jonah' ; R.1 = "Jonah Whales" |
S.1 = 27 'Jonah' ; R.1 = "Jonah Whales" |
||
Line 2,769: | Line 2,769: | ||
if nems=='' then iterate /*No nemesis? Skip. */ |
if nems=='' then iterate /*No nemesis? Skip. */ |
||
say pad right(age,3) pad center(name,20) pad center(nems,30) /*display an "S". */ |
say pad right(age,3) pad center(name,20) pad center(nems,30) /*display an "S". */ |
||
end /*n*/ /*stick a fork in it, we're all done. */</ |
end /*n*/ /*stick a fork in it, we're all done. */</syntaxhighlight> |
||
'''output''' when using the in-code relations (data): |
'''output''' when using the in-code relations (data): |
||
<pre> |
<pre> |
||
Line 2,781: | Line 2,781: | ||
=={{header|Ring}}== |
=={{header|Ring}}== |
||
< |
<syntaxhighlight lang="ring">Table1 = [[27, "Jonah"], [18, "Alan"], [28, "Glory"], [18, "Popeye"], [28, "Alan"]] |
||
Table2 = [["Jonah", "Whales"], ["Jonah", "Spiders"], ["Alan", "Ghosts"], ["Alan", "Zombies"], ["Glory", "Buffy"]] |
Table2 = [["Jonah", "Whales"], ["Jonah", "Spiders"], ["Alan", "Ghosts"], ["Alan", "Zombies"], ["Glory", "Buffy"]] |
||
hTable = [] |
hTable = [] |
||
Line 2,822: | Line 2,822: | ||
next |
next |
||
ok |
ok |
||
return r</ |
return r</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 2,838: | Line 2,838: | ||
=={{header|Ruby}}== |
=={{header|Ruby}}== |
||
< |
<syntaxhighlight lang="ruby">def hashJoin(table1, index1, table2, index2) |
||
# hash phase |
# hash phase |
||
h = table1.group_by {|s| s[index1]} |
h = table1.group_by {|s| s[index1]} |
||
Line 2,859: | Line 2,859: | ||
["Glory", "Buffy"]] |
["Glory", "Buffy"]] |
||
hashJoin(table1, 1, table2, 0).each { |row| p row }</ |
hashJoin(table1, 1, table2, 0).each { |row| p row }</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 2,873: | Line 2,873: | ||
=={{header|Run BASIC}}== |
=={{header|Run BASIC}}== |
||
< |
<syntaxhighlight lang="runbasic">sqliteconnect #mem, ":memory:" |
||
#mem execute("CREATE TABLE t_age(age,name)") |
#mem execute("CREATE TABLE t_age(age,name)") |
||
Line 2,897: | Line 2,897: | ||
nemesis$ = #row nemesis$() |
nemesis$ = #row nemesis$() |
||
print age;" ";name$;" ";nemesis$ |
print age;" ";name$;" ";nemesis$ |
||
WEND</ |
WEND</syntaxhighlight>Output: |
||
<pre>27 Jonah Spiders |
<pre>27 Jonah Spiders |
||
27 Jonah Whales |
27 Jonah Whales |
||
Line 2,908: | Line 2,908: | ||
=={{header|Rust}}== |
=={{header|Rust}}== |
||
< |
<syntaxhighlight lang="rust">use std::collections::HashMap; |
||
use std::hash::Hash; |
use std::hash::Hash; |
||
Line 2,950: | Line 2,950: | ||
println!("{:<3} | {:^14} | {}", age, name, nemesis); |
println!("{:<3} | {:^14} | {}", age, name, nemesis); |
||
} |
} |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>Age | Character Name | Nemesis |
<pre>Age | Character Name | Nemesis |
||
Line 2,963: | Line 2,963: | ||
=={{header|Scala}}== |
=={{header|Scala}}== |
||
< |
<syntaxhighlight lang="scala">def join[Type](left: Seq[Seq[Type]], right: Seq[Seq[Type]]) = { |
||
val hash = right.groupBy(_.head) withDefaultValue Seq() |
val hash = right.groupBy(_.head) withDefaultValue Seq() |
||
left.flatMap(cols => hash(cols.last).map(cols ++ _.tail)) |
left.flatMap(cols => hash(cols.last).map(cols ++ _.tail)) |
||
Line 2,981: | Line 2,981: | ||
List("Glory", "Buffy")) |
List("Glory", "Buffy")) |
||
println(join(table1, table2) mkString "\n")</ |
println(join(table1, table2) mkString "\n")</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>List(27, Jonah, Whales) |
<pre>List(27, Jonah, Whales) |
||
Line 2,993: | Line 2,993: | ||
=={{header|Scheme}}== |
=={{header|Scheme}}== |
||
{{works with|Gauche Scheme}} |
{{works with|Gauche Scheme}} |
||
< |
<syntaxhighlight lang="scheme">(use srfi-42) |
||
(define ages '((27 Jonah) (18 Alan) (28 Glory) (18 Popeye) (28 Alan))) |
(define ages '((27 Jonah) (18 Alan) (28 Glory) (18 Popeye) (28 Alan))) |
||
Line 3,013: | Line 3,013: | ||
(print (list (list age name) |
(print (list (list age name) |
||
person))) |
person))) |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{output}} |
{{output}} |
||
<pre> |
<pre> |
||
Line 3,026: | Line 3,026: | ||
=={{header|Sidef}}== |
=={{header|Sidef}}== |
||
< |
<syntaxhighlight lang="ruby">func hashJoin(table1, index1, table2, index2) { |
||
var a = [] |
var a = [] |
||
var h = Hash() |
var h = Hash() |
||
Line 3,055: | Line 3,055: | ||
["Glory", "Buffy"]] |
["Glory", "Buffy"]] |
||
hashJoin(t1, 1, t2, 0).each { .say }</ |
hashJoin(t1, 1, t2, 0).each { .say }</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre>[[27, 'Jonah'], ['Jonah', 'Whales']] |
<pre>[[27, 'Jonah'], ['Jonah', 'Whales']] |
||
Line 3,067: | Line 3,067: | ||
=={{header|SQL}}== |
=={{header|SQL}}== |
||
{{works with|oracle}} |
{{works with|oracle}} |
||
< |
<syntaxhighlight lang="sql">-- setting up the test data |
||
create table people (age number(3), name varchar2(30)); |
create table people (age number(3), name varchar2(30)); |
||
Line 3,085: | Line 3,085: | ||
select 'Alan' , 'Zombies' from dual union all |
select 'Alan' , 'Zombies' from dual union all |
||
select 'Glory', 'Buffy' from dual |
select 'Glory', 'Buffy' from dual |
||
;</ |
;</syntaxhighlight> |
||
Doing the join is trivial. Normally we would let the optimizer select the join method. However, to force a hash join, we can use an optimizer hint, USE_HASH. |
Doing the join is trivial. Normally we would let the optimizer select the join method. However, to force a hash join, we can use an optimizer hint, USE_HASH. |
||
< |
<syntaxhighlight lang="sql">select /*+ use_hash */ * from people join nemesises using(name);</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 3,105: | Line 3,105: | ||
{{trans|Rust}} |
{{trans|Rust}} |
||
< |
<syntaxhighlight lang="swift">func hashJoin<A, B, K: Hashable>(_ first: [(K, A)], _ second: [(K, B)]) -> [(A, K, B)] { |
||
var map = [K: [B]]() |
var map = [K: [B]]() |
||
Line 3,146: | Line 3,146: | ||
for (age, name, nemesis) in hashJoin(t1, t2) { |
for (age, name, nemesis) in hashJoin(t1, t2) { |
||
print("\(age) | \(name) | \(nemesis)") |
print("\(age) | \(name) | \(nemesis)") |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 3,162: | Line 3,162: | ||
=={{header|Tcl}}== |
=={{header|Tcl}}== |
||
Tcl uses hash tables to implement both its associative arrays and its dictionaries. |
Tcl uses hash tables to implement both its associative arrays and its dictionaries. |
||
< |
<syntaxhighlight lang="tcl">package require Tcl 8.6 |
||
# Only for lmap, which can be replaced with foreach |
# Only for lmap, which can be replaced with foreach |
||
Line 3,200: | Line 3,200: | ||
foreach row $joined { |
foreach row $joined { |
||
puts $row |
puts $row |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 3,216: | Line 3,216: | ||
Generic hash join. Arguments <code>left-key</code> and <code>right-key</code> are functions applied to the elements of the <code>left</code> and <code>right</code> sequences to retrieve the join key. |
Generic hash join. Arguments <code>left-key</code> and <code>right-key</code> are functions applied to the elements of the <code>left</code> and <code>right</code> sequences to retrieve the join key. |
||
< |
<syntaxhighlight lang="txrlisp">(defvar age-name '((27 Jonah) |
||
(18 Alan) |
(18 Alan) |
||
(28 Glory) |
(28 Glory) |
||
Line 3,234: | Line 3,234: | ||
^(,l-entry ,r-entry))))) |
^(,l-entry ,r-entry))))) |
||
(format t "~s\n" [hash-join age-name second nemesis-name first])</ |
(format t "~s\n" [hash-join age-name second nemesis-name first])</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 3,242: | Line 3,242: | ||
=={{header|VBScript}}== |
=={{header|VBScript}}== |
||
<syntaxhighlight lang="vb"> |
|||
<lang vb> |
|||
Dim t_age(4,1) |
Dim t_age(4,1) |
||
t_age(0,0) = 27 : t_age(0,1) = "Jonah" |
t_age(0,0) = 27 : t_age(0,1) = "Jonah" |
||
Line 3,273: | Line 3,273: | ||
Next |
Next |
||
End Sub |
End Sub |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{Out}} |
{{Out}} |
||
Line 3,288: | Line 3,288: | ||
=={{header|Visual FoxPro}}== |
=={{header|Visual FoxPro}}== |
||
Hashing using the common key (name) gives ambiguous results as the name column is not unique in either table (a unique key could be formed by using the age and name columns) . This implementation forces a unique key on the people table. |
Hashing using the common key (name) gives ambiguous results as the name column is not unique in either table (a unique key could be formed by using the age and name columns) . This implementation forces a unique key on the people table. |
||
< |
<syntaxhighlight lang="vfp"> |
||
LOCAL i As Integer, n As Integer |
LOCAL i As Integer, n As Integer |
||
CLOSE DATABASES ALL |
CLOSE DATABASES ALL |
||
Line 3,343: | Line 3,343: | ||
RETURN a[1] |
RETURN a[1] |
||
ENDFUNC |
ENDFUNC |
||
</syntaxhighlight> |
|||
</lang> |
|||
{{out}} |
{{out}} |
||
<pre> |
<pre> |
||
Line 3,356: | Line 3,356: | ||
=={{header|Wren}}== |
=={{header|Wren}}== |
||
{{libheader|Wren-fmt}} |
{{libheader|Wren-fmt}} |
||
< |
<syntaxhighlight lang="ecmascript">import "/fmt" for Fmt |
||
class A { |
class A { |
||
Line 3,408: | Line 3,408: | ||
} |
} |
||
} |
} |
||
}</ |
}</syntaxhighlight> |
||
{{out}} |
{{out}} |
||
Line 3,425: | Line 3,425: | ||
=={{header|zkl}}== |
=={{header|zkl}}== |
||
Join two tables by hashing on the common key (name). The resulting join is the intersection of the two tables. |
Join two tables by hashing on the common key (name). The resulting join is the intersection of the two tables. |
||
< |
<syntaxhighlight lang="zkl">ageName:=T(27,"Jonah", 18,"Alan", 28,"Glory", 18,"Popeye", 28,"Alan"); |
||
nameNemesis:=T("Jonah","Whales", "Jonah","Spiders", "Alan","Ghosts", |
nameNemesis:=T("Jonah","Whales", "Jonah","Spiders", "Alan","Ghosts", |
||
"Alan","Zombies", "Glory","Buffy"); |
"Alan","Zombies", "Glory","Buffy"); |
||
Line 3,448: | Line 3,448: | ||
val:=d[name]; if (not val[1])return(Void.Skip); |
val:=d[name]; if (not val[1])return(Void.Skip); |
||
String(name,":",d[name][1].concat(",")); |
String(name,":",d[name][1].concat(",")); |
||
})</ |
})</syntaxhighlight> |
||
zkl Dictionaries only have one key |
zkl Dictionaries only have one key |
||
<pre> |
<pre> |