# LINQ inner joins and select all in Query.jl

**URL:** <https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556>\
**Category:** New to Julia\
**Tags:** question, query, dataframes\
**Created:** [September 13, 2020, 5:54pm UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556 "2020-09-13T17:54:02Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![mkarikom](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mkarikom/32/7096_2.png) [@mkarikom](https://discourse.julialang.org/u/mkarikom)\
**Post date:** [September 13, 2020, 5:54pm UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/1 "2020-09-13T17:54:02Z")

</div>

This MWE is supposed to

1. do an inner join (`i.a equals j.c || i.b equals j.e`) and
2. output all the elements of the first df:

```julia
df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
df2 = DataFrame(c=[2,4,2], d=["John", "Jim","Sally"],e=[1.,1.,3.])

x = @from i in df1 begin
    @join j in df2 on i.a equals j.c || i.b equals j.e
    @select i
    @collect DataFrame
end

println(x)

[1] top-level scope at /home/au/Downloads/scratch.jl:23
ERROR: syntax: "j.e" is not a valid function argument name
Stacktrace:
[1] top-level scope at /home/au/Downloads/scratch.jl:7

```

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 14, 2020, 4:31am UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/2 "2020-09-14T04:31:20Z")

</div>

I am trying to solve it.

BTW, what attracted you to use Query.jl in the first place? Is it because it sounds like the equiavelnt of tidyverse in Julia?

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 14, 2020, 4:38am UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/3 "2020-09-14T04:38:48Z")

</div>

doesn’t look like possible. better ask on query github repo.

---

<div class="post-metadata">

**Author:** ![mkarikom](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mkarikom/32/7096_2.png) [@mkarikom](https://discourse.julialang.org/u/mkarikom)\
**Post date:** [September 14, 2020, 6:49am UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/4 "2020-09-14T06:49:33Z")

</div>

This works (new example data so correct result is not empty):

```julia
df1 = DataFrame(a=[1,2,3], b=[1.,2.,3.])
df2 = DataFrame(c=[2,4,3], d=["John", "Jim","Sally"],e=[1.,1.,3.])

x = @from i in df1 begin
    @join j in df2 on (i.a,i.b) equals (j.c,j.e)
    @select i
    @collect DataFrame
end

@test x[1,:] == df1[3,:]

```

---

<div class="post-metadata">

**Author:** ![mkarikom](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mkarikom/32/7096_2.png) [@mkarikom](https://discourse.julialang.org/u/mkarikom)\
**Post date:** [September 14, 2020, 6:58am UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/5 "2020-09-14T06:58:57Z")

</div>

> [@xiaodai](#):
>
> what attracted you to use Query.jl in the first place?

mainly:

1. the structured query interface makes my blocks of ugly data-munging code into concise, (often re-useable) statements
2. by facilitating joins across all kinds of data it lets me avoid issues stemming from loading huge flat files (I have an obscene amount of these) into memory to get at some random little bit of information

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 14, 2020, 11:23am UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/6 "2020-09-14T11:23:08Z")

</div>

> [@mkarikom](#):
>
> the structured query interface makes my blocks of ugly data-munging code into concise, (often re-useable) statements

I’d say between DataFrames.jl DataFramesMeta.jl Pipe.jl DataConvenience.jl (for it’s export of Lazy.jl’s `@>` macro), you have everything covered, if I am not wrong the above is just an inner join which is just a one-liner in DataFrames.jl

```julia
x = innerjoin(df1, df2, on = [:a => :c, :b => :e])

```

---

<div class="post-metadata">

**Author:** ![aplavin](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aplavin/32/222056_2.png) [@aplavin](https://discourse.julialang.org/u/aplavin)\
**Post date:** [September 14, 2020, 12:59pm UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/7 "2020-09-14T12:59:47Z")

</div>

As far as I understand, a major difference of those packages is that they materialize the dataframe after each operation, be it mapping/filtering/etc. Query.jl is lazy in this sense, and only collects the results when needed.

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [September 14, 2020, 2:59pm UTC](https://discourse.julialang.org/t/linq-inner-joins-and-select-all-in-query-jl/46556/8 "2020-09-14T14:59:43Z")

</div>

> [@aplavin](#):
>
> Query.jl is lazy in this sense and only collects the results when needed.

that might true. But performance-wise, I couldn’t get good group-by performance from Query.jl and I think it’s a limitation of its design. So personally, I don’t see myself using it, but I want to understand why ppl use it.