Its time to enrich the SQLOperators package with new relational algebra operators useful in data manipulation and processing For those unfamiliar with SQL you can find a good introduction https://en.wikipedia.org/wiki/Relational_algebraThe newly added functions :- leftjoinSQL [R , S , {col1,..., coln} , {col1,..., coln} ] The result of the left outer join is the set of all combinations of tuples in R and S that are equal on their common attribute names, in addition (loosely speaking) to tuples in R that have no matching tuples in S. The non-matching tuples attributes are filled out with Missing[] value. If tables have attributes names in common the attributes from second table are suffixed with “2” - fulljoinSQL [R , S , {col1,..., coln} , {col1,..., coln} ] The result of the full outer join is the set of all combinations of tuples in R and S that are equal on their common attribute names, in addition to tuples in S that have no matching tuples in R and tuples in R that have no matching tuples in S in their common attribute names. The non-matching tuples attributes are filled out with Missing[] value. If tables have attributes names in common the attributes from second table are suffixed with “2” - semijponSQL[R , S , {col1,..., coln} , {col1,..., coln} ] The left semijoin is a joining similar to the natural join. The result is the set of all tuples in R for which there is a tuple in S that is equal on their common attribute names. - antijoinSQL [R , S , {col1,..., coln} , {col1,..., coln} ] The antijoin, is similar to the semijoin, but the result of an antijoin is only those tuples in R for which there is no tuple in S that is equal on their common attribute names.
Load SQL package
Load SQL package
Load the last version of SQLOperators package containing SQL function developed so far.
See my previous post : SQL operators as Wolfram Language functions ( https://community.wolfram.com/groups/-/m/t/2195893 )
See my previous post : SQL operators as Wolfram Language functions ( https://community.wolfram.com/groups/-/m/t/2195893 )
In[]:=
CloudImport["https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl"];
Sample data
Sample data
(*generatetables*)tb1=Table[{x,RandomReal[],"some text value",DateObject[{2000,1}]},{x,1,1000,1}]//tableSQL[#,{"id","value","label","date"}]&;tb2=Table[{x,RandomReal[],"some text value",DateObject[{2000,1}]},{x,50,10000,1}]//tableSQL[#,{"id","value","label","date"}]&;tb1//showTableSQLtb2//showTableSQL
Total Rows:1000 Elapsed:0.
Out[]//TableForm=
id | value | label | date |
1 | 0.0239667 | some text value | |
2 | 0.953503 | some text value | |
3 | 0.147774 | some text value | |
4 | 0.734449 | some text value | |
5 | 0.670043 | some text value | |
6 | 0.617034 | some text value | |
7 | 0.0571665 | some text value | |
8 | 0.650436 | some text value | |
9 | 0.619489 | some text value | |
10 | 0.02679 | some text value |
Total Rows:9951 Elapsed:0.
Out[]//TableForm=
id | value | label | date |
50 | 0.898204 | some text value | |
51 | 0.19423 | some text value | |
52 | 0.867942 | some text value | |
53 | 0.45666 | some text value | |
54 | 0.403806 | some text value | |
55 | 0.34218 | some text value | |
56 | 0.421109 | some text value | |
57 | 0.393213 | some text value | |
58 | 0.463636 | some text value | |
59 | 0.8331 | some text value |
Left Join
Left Join
(*leftjoinonsinglecolumn*)leftjoinSQL[tb1,tb2,{id},{id}]//showTableSQL(*leftjoinonmultiplecolumn*)leftjoinSQL[tb1,tb2,{id,date},{id,date}]//showTableSQL
Total Rows:1000 Elapsed:0.07508
Out[]//TableForm=
id | value | label | date | id2 | value2 | label2 | date2 |
50 | 0.909524 | some text value | 50 | 0.0240186 | some text value | ||
51 | 0.362563 | some text value | 51 | 0.21567 | some text value | ||
52 | 0.551502 | some text value | 52 | 0.999104 | some text value | ||
53 | 0.268239 | some text value | 53 | 0.391567 | some text value | ||
54 | 0.087411 | some text value | 54 | 0.894213 | some text value | ||
55 | 0.792859 | some text value | 55 | 0.698149 | some text value | ||
56 | 0.788068 | some text value | 56 | 0.459536 | some text value | ||
57 | 0.197657 | some text value | 57 | 0.6386 | some text value | ||
58 | 0.22902 | some text value | 58 | 0.865351 | some text value | ||
59 | 0.887598 | some text value | 59 | 0.326801 | some text value |
Total Rows:1000 Elapsed:0.100548
Out[]//TableForm=
id | value | label | date | id2 | value2 | label2 | date2 |
50 | 0.909524 | some text value | 50 | 0.0240186 | some text value | ||
51 | 0.362563 | some text value | 51 | 0.21567 | some text value | ||
52 | 0.551502 | some text value | 52 | 0.999104 | some text value | ||
53 | 0.268239 | some text value | 53 | 0.391567 | some text value | ||
54 | 0.087411 | some text value | 54 | 0.894213 | some text value | ||
55 | 0.792859 | some text value | 55 | 0.698149 | some text value | ||
56 | 0.788068 | some text value | 56 | 0.459536 | some text value | ||
57 | 0.197657 | some text value | 57 | 0.6386 | some text value | ||
58 | 0.22902 | some text value | 58 | 0.865351 | some text value | ||
59 | 0.887598 | some text value | 59 | 0.326801 | some text value |
(*checktherowsnotmatchingjoinconditionontb2.thenonmatchingrowsattributesarealwaysfilledoutwithMissing[]value*)leftjoinSQL[tb1,tb2,{id},{id}]//whereSQL[#,MissingQ[id2]]&//showTableSQL
Total Rows:49 Elapsed:0.083956
FullJoin
FullJoin
(*fulljoinonsinglecolumn*)fulljoinSQL[tb1,tb2,{id},{id}]//showTableSQL
Total Rows:10000 Elapsed:0.117645
Out[]=
Anti Join
Anti Join
Semi Join
Semi Join
The new joins types are performing well on 1 million row tables
If you enjoy the project or find it useful , any crypto donations are welcomed @ BTC wallet : bc1qm06aatgwn8ducjtexu6cf0wle0d9r00fy7yp7y
or via paystring : calin.damian.crypto$paystring.crypto.com
or via paystring : calin.damian.crypto$paystring.crypto.com