How to join three or more tabels


#1

Hi, I am new to mapd and I am trying to run tpch on mapd. Now I am confused on how to join three or more tables on mapd use mapd join .
For example, I rewrote the Q5 of tpch, it will damage the performance severely. so I want to know if there are some rules for join that I ignored?

1 select
2 n_name,
3 sum(l_extendedprice * (1 - l_discount)) as revenue
4 from
5 (select * from nation join region on n_regionkey = r_regionkey) join
6 (select * from (select * from orders join lineitem on o_orderkey = l_orderkey) join
7 (select * from supplier join customer on s_nationkey = c_nationkey) on o_custkey = c_custkey where s_suppkey = l_suppkey) on n_nationkey = s_nationkey
8 where
9 r_name = ‘ASIA’
10 and o_orderdate >= date ‘1994-01-01’
11 and o_orderdate < date ‘1994-01-01’ + interval ‘1’ year
12 group by
13 n_name
14 order by
15 revenue desc


#2

As a rule of thumb i would specify the columns needed in the query instead of *.
If you are forced to rewrite the query i suggest to use WITH clause to make query more intellegible


#3

Hi

Why are you trying to rewrite the query like this?

The standard query 5 runs as written in the spec?

mapdql> select
..>         n_name,
..>         sum(l_extendedprice * (1 - l_discount)) as revenue
..> from
..>         customer,
..>         orders,
..>         lineitem,
..>         supplier,
..>         nation,
..>         region
..> where
..>         c_custkey = o_custkey
..>         and l_orderkey = o_orderkey
..>         and l_suppkey = s_suppkey
..>         and c_nationkey = s_nationkey
..>         and s_nationkey = n_nationkey
..>         and n_regionkey = r_regionkey
..>         and r_name = 'ASIA'
..>         and o_orderdate >= date '1994-01-01'
..>         and o_orderdate < date '1994-01-01' + interval '1' year
..> group by
..>         n_name
..> order by
..>         revenue desc;
n_name|revenue
INDONESIA|55502041.1697
VIETNAM|55295086.9967
CHINA|53724494.2566
INDIA|52035512.0002
JAPAN|45410175.6954
5 rows returned.
Execution time: 95 ms, Total time: 97 ms


What SF factor are you trying to use?

Are you just looking to rewrite using explicit join syntax?

regards