0

Why does the order of the "FROM" seem to cause different returned results.

This returns 0 rows:

SELECT 
D.CODE, 
D.BVCMTDQTY,  
H.NUMBER, 
H.CUST_PO_NO 
FROM SALES_HISTORY_HEADER AS H 
JOIN SALES_HISTORY_DETAIL AS D ON D.NUMBER = H.NUMBER 
WHERE H.NUMBER > '0000974286'  
AND H.NUMBER < '0000974289'

This returns 8 rows:

SELECT 
D.CODE, 
D.BVCMTDQTY, 
H.NUMBER, 
H.CUST_PO_NO 
FROM SALES_HISTORY_DETAIL AS D
JOIN SALES_HISTORY_HEADER AS H ON D.NUMBER = H.NUMBER 
WHERE H.NUMBER > '0000974286'  
AND H.NUMBER < '0000974289'

Am I missing something? Thanks for any feedback.

Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
joe
  • 31
  • 4
  • I'm not a SQL expert, but I think FROM and JOIN order is important. I know it's a shitty comment, but hey, it's a comment. – The Onin Jun 16 '16 at 20:01
  • 1
    What RDBMS are you using? Can you provide a sample of rows from both tables above? For an inner join (implicit when using `JOIN` alone), the order does not matter. – Michael Berkowski Jun 16 '16 at 20:01
  • 1
    Also, what is the data type of `SALES_HISTORY_HEADER.NUMBER`? – Michael Berkowski Jun 16 '16 at 20:03
  • This is querying a Pervasive database. – joe Jun 16 '16 at 20:07
  • @wildplasser, OP isn't questioning the order of the records. Instead, OP is wondering why one query returns 8 records and the other, 0. – JNevill Jun 16 '16 at 20:09
  • OP, better edit your title to something like "JOIN and FROM precedence" else you'll never hear the end of people who don't read telling you that tables have no default order :) – jleach Jun 16 '16 at 20:11
  • @jdl134679 I've updated the title to help folks avoid confusion. – JNevill Jun 16 '16 at 20:14
  • @JNevill - thank you – joe Jun 16 '16 at 20:14
  • 1
    @Michael Berkowski - SALES_HISTORY_HEADER.NUMBER is a string – joe Jun 16 '16 at 20:15
  • Tip: Most people have no idea what `JOIN` does. Always use `LEFT JOIN`, `INNER JOIN`, or `RIGHT JOIN` so that there's no confusion. – Jonathan Allen Jun 16 '16 at 20:15
  • 1
    Do `D.NUMBER` and `H.NUMBER` use the same character set and collation? – ostrokach Jun 16 '16 at 20:18
  • Is `Number` field that you are joining on a `String` in both tables? I wonder if the cardinality of your join is causing one of the tables to cast to the other, which then effects the `WHERE` clauses restrictions. – JNevill Jun 16 '16 at 20:20
  • 1
    Sounds like a bug in Pervasive. – Jonathan Allen Jun 16 '16 at 20:21
  • @JNevill Oops, thanks. – wildplasser Jun 16 '16 at 20:30
  • @JNevill - yes Number is a String in both tables. Not sure what the casting effect is. – joe Jun 16 '16 at 20:31
  • @ostrokach - I'll have to look into how to determine the character sets for each. – joe Jun 16 '16 at 20:32
  • Ok, lots of trial and error and finally found the answer here: http://stackoverflow.com/questions/17305454/query-returning-nothing I can't explain it as well as they do, but the gist of it is connected to how Sage BusinessVision indexes in the Pervasive db and how the join requires "and RECNO > 0" to make it work properly. – joe Jul 05 '16 at 04:05

0 Answers0