Hi Allen,
Is the explain plan the same when you do the join the other way around (i.e. A join B vs B join A)? What tool are you using to run this query, and does it have a ‘Limit Rows’ feature? Cheers, Matt *From:* 彭昶勳 [mailto:[email protected]] *Sent:* 19 March 2015 09:17 *To:* [email protected] *Subject:* Re: Join not returning any results in Phoenix Hi Sun, There is no any error or exception for my query without result. The query and the explain plan are as following select * from myTableB b join myTableA a on b.telnum = a.chargedpartyid where a.date between b.startdate and t.termdate CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEB CLIENT MERGE SORT PARALLEL INNER-JOIN TABLE 0 CLIENT PARALLEL 3-WAY FULL SCAN OVER MYTABLEA CLIENT MERGE SORT AFTER-JOIN SERVER FILTER BY (B.STARTDATE <= A.DATE AND A.DATE <= B.TERMDATE) thanks, Allen 2015-03-19 16:58 GMT+08:00 Fulin Sun <[email protected]>: Hi, Did you see any error or exception info for your join query without results? Can you paste your explain plan results for the query? Thanks, Sun. ------------------------------ ------------------------------ *Certus**Net* *From:* 彭昶勳 <[email protected]> *Date:* 2015-03-19 16:42 *To:* user <[email protected]> *Subject:* Join not returning any results in Phoenix Hi guys, I encountered some problems when doing Join queries with Phoenix. I can get correct answer from simple query, but some Join queries return no results. I can get correct result by the following query select * from myTableA a join myTableB b on b.telnum = a.chargedpartyid where a.date between b.startdate and t.termdate but I get no result if I try select * from myTableB b join myTableA a on b.telnum = a.chargedpartyid where a.date between b.startdate and t.termdate And I can get correct result from both following queries: select count(*) from myTableB b join myTableA a on b.telnum = a.chargedpartyid where a.date between b.startdate and t.termdate select count(*) from myTableA a join myTableB b on b.telnum = a.chargedpartyid where a.date between b.startdate and t.termdate I have tried different size of my tables(10 rows, 1krow, 10k rows) and the results are all the same. Many thanks for any suggestions
