logo
down
shadow

Are Hive's implicit joins always inner joins?


Are Hive's implicit joins always inner joins?

By : Anon Anon
Date : November 21 2020, 03:00 PM
I hope this helps you . Not always. Your queries are equivalent. But without WHERE t1.id = t2.id AND t2.id = t3.id it will be CROSS JOIN.
Update:
code :
insert into table A
 select 1, 'row one' union all
 select 2, 'row two';

insert into table B
 select 1, 'row one' union all
 select 3, 'row three';
hive> select * from A;
OK
1       row one
2       row two
Time taken: 1.29 seconds, Fetched: 2 row(s)
hive> select * from B;
OK
1       row one
3       row three
Time taken: 0.091 seconds, Fetched: 2 row(s)
hive> select a.c1, a.c2, b.c1, b.c2 from a,b;
Warning: Map Join MAPJOIN[14][bigTable=a] in task 'Stage-3:MAPRED' is a cross product
Warning: Map Join MAPJOIN[22][bigTable=b] in task 'Stage-4:MAPRED' is a cross product
Warning: Shuffle Join JOIN[4][tables = [a, b]] in Stage 'Stage-1:MAPRED' is a cross product

OK
1       row one 1       row one
2       row two 1       row one
1       row one 3       row three
2       row two 3       row three
Time taken: 54.804 seconds, Fetched: 4 row(s)
hive> select a.c1, a.c2, b.c1, b.c2 from a,b where a.c1=b.c1;
OK
1       row one 1       row one
Time taken: 38.413 seconds, Fetched: 1 row(s)
hive> select a.c1, a.c2, b.c1, b.c2 from a,b where (a.c1=b.c1) OR (b.c1 is null);
OK
1       row one 1       row one
Time taken: 57.317 seconds, Fetched: 1 row(s)
 select a.c1, a.c2, b.c1, b.c2 from a left join b;
OK
1       row one 1       row one
1       row one 3       row three
2       row two 1       row one
2       row two 3       row three
Time taken: 37.104 seconds, Fetched: 4 row(s)
select a.c1, a.c2, b.c1, b.c2 from a left join b where a.c1=b.c1;
OK
1       row one 1       row one
Time taken: 40.617 seconds, Fetched: 1 row(s)
 select a.c1, a.c2, b.c1, b.c2 from a left join b where a.c1=b.c1 or b.c1 is null;
OK
1       row one 1       row one
Time taken: 53.873 seconds, Fetched: 1 row(s)
hive> select a.c1, a.c2, b.c1, b.c2 from a left join b on a.c1=b.c1;
OK
1       row one 1       row one
2       row two NULL    NULL
Time taken: 48.626 seconds, Fetched: 2 row(s)
hive> select a.c1, a.c2, b.c1, b.c2 from a left join b on a.c1=b.c1 where a.c1=b.c1;
OK
1       row one 1       row one
Time taken: 49.54 seconds, Fetched: 1 row(s)
hive> select a.c1, a.c2, b.c1, b.c2 from a left join b on a.c1=b.c1 where a.c1=b.c1 or b.c1 is null;
OK
1       row one 1       row one
2       row two NULL    NULL
Time taken: 55.951 seconds, Fetched: 2 row(s)


Share : facebook icon twitter icon
Is there an Oracle official recommendation on the use of explicit ANSI JOINs vs implicit joins?

Is there an Oracle official recommendation on the use of explicit ANSI JOINs vs implicit joins?


By : user3660519
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I haven't seen it if there is. The reason for preferring ANSI syntax for outer joins in particular (apart from the non-standarrd, Oracle-specific (+) symbol) is that more outer joins are expressible using the ANSI syntax. The restriction "ORA-01417: a table may be outer joined to at most one other table" applies to (+) outer joins but not to ANSI outer joins. Other restrictions on (+) that do not apply to ANSI outer joins are documented here.
One highly respected Oracle expert actually recommends sticking to the old syntax for inner joins - see Jonathan Lewis's blog. He says there that ANSI joins are transformed to traditional Oracle joins under the covers anyway. I don't agree with him 100% (I prefer ANSI joins myself in general), but would not claim to have a fraction of his knowledge on the topic.
multiple implicit joins and explicit joins in a query

multiple implicit joins and explicit joins in a query


By : Luis Alexander Jara
Date : March 29 2020, 07:55 AM
I wish this helpful for you My suggestion would be to change your query to use explicit joins for every one:
code :
SELECT THDR.FILENAME,
       SOURCE.TXN_SOURCE_CD AS TXN_SOURCE_CD,
       SOURCE.DESCR as TXN_SOURCE,
       THDR.TXN_HEADER_ID,
       THDR.TXN_HEADER_EXT_ID,
       THDR.TXN_HEADER_DTTM,
       THDR.UPLOAD_DTTM,
       L.DESCR,
       NVL(SUM(TDTL.TXN_VOL), 0) TOTAL_VOLUME
FROM ci_lookup_val_l L
INNER JOIN CI_TXN_HEADER   THDR
  ON THDR.BO_STATUS_CD = L.FIELD_VALUE
LEFT JOIN ci_txn_detail TDTL 
  ON THDR.TXN_HEADER_ID = TDTL.TXN_HEADER_ID       
LEFT JOIN ci_txn_source_l source
  on TRIM(SOURCE.TXN_SOURCE_CD) = TRIM(TDTL.TXN_SOURCE_CD)
  AND SOURCE.LANGUAGE_CD = 'ENG' 
WHERE L.FIELD_NAME = 'TXN_HEADER_STATUS' 
   AND L.LANGUAGE_CD = 'ENG'
   AND THDR.TXN_HEADER_ID='22222228'
GROUP BY THDR.FILENAME,
  SOURCE.DESCR,
  THDR.TXN_HEADER_ID,
  THDR.TXN_HEADER_EXT_ID,
  THDR.TXN_HEADER_DTTM,
  THDR.UPLOAD_DTTM,
  L.DESCR,
  SOURCE.TXN_SOURCE_CD
How to rewrite implicit joins to explicit joins?

How to rewrite implicit joins to explicit joins?


By : Valdrin Tafa
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I'm having some trouble re-writing these JOINS using explicit joins. I'm trying to understand how this is accomplished. Here is the original query using implicit joins that I've inherited:
code :
FROM prod.f31122 t      
LEFT JOIN prod.f4801 wo ON t.wtdoco = wo.wadoco AND wo.wammcu = t.wtmmcu 
JOIN prod.f30008 r ON t.wtmmcu = r.iwmcu 
JOIN prod.f0101 ab ON t.wtan8 = ab.aban8 
Hibernate/JPA: How to force implicit joins to use LEFT OUTER JOINS

Hibernate/JPA: How to force implicit joins to use LEFT OUTER JOINS


By : Daniel
Date : March 29 2020, 07:55 AM
will be helpful for those in need You can try putting @Fetch(FetchMode.JOIN) on the Article property. This is a Hibernate annotation, however.
Explicit JOINs vs Implicit joins?

Explicit JOINs vs Implicit joins?


By : Gwylim Williams
Date : March 29 2020, 07:55 AM
around this issue Your professor should speak with Gordon Linoff, who is a computer science professor at Columbia University. Gordon, and most SQL enthusiasts on this site, will almost always tell you to use explicit join syntax. The reasons for this are many, including (but not limited to):
Explicit joins make it easy to see what the actual join logic is. Implicit joins, on the other hand, obfuscate the join logic, by spreading it out across both the FROM and WHERE clauses. The ANSI 92 standard recommends using modern explicit joins, and in fact deprecated the implicit join which your professor seems to be pushing
shadow
Privacy Policy - Terms - Contact Us © voile276.org