INNER JOIN clause returning no rows while NATURAL JOIN does

Question

I am stuck with doing a simple join in SQLite.

I have two tables - one with data, and other relational one with pointers:

• References contains ReferenceID and Reference
• REL_References_Pages contains ReferenceID and PageID

Normal join query works OK and returns good result(s):

SELECT Reference
FROM "References" 
NATURAL JOIN REL_References_Pages
WHERE PageID = 6

But if I try to do an explicit JOIN, the result is without an error, but returns no result. Where it is stuck is on the ON clause:

SELECT Reference
FROM "References" 
JOIN REL_References_Pages ON "REL_References_Pages.ReferenceID" = "References.ReferenceID"
WHERE PageID = 6

Any ideas?

I could just use NATURAL JOIN, but I am wondering why normal join doesn't work.

Also, the table References has a stupid name, but it is what it is.

Answer 1

I believe the reason you're getting empty output (no rows) is because in your on clause you are trying to compare two strings that are not equal and thus returning false.

Simply put, below command would yield a false boolean output:

SELECT "REL_References_Pages.ReferenceID" = "References.ReferenceID"

Try without quotation marks around whole table.column construct where it's not needed. In your case only References table is encapsulated originally in the data model. At least I assume that.

SELECT Reference
FROM "References" 
JOIN REL_References_Pages
  ON REL_References_Pages.ReferenceID = "References".ReferenceID
WHERE PageID = 6

Or even better with the use of aliases:

SELECT Reference
FROM "References" AS r 
JOIN REL_References_Pages AS rp
  ON r.ReferenceID = rp.ReferenceID
WHERE PageID = 6

You could also shorten the code by applying a USING expression instead since your column names are exactly the same in both tables and you won't need to assign aliases to tables in your query this way at all:

SELECT Reference
FROM "References"
JOIN REL_References_Pages USING ( ReferenceID )
WHERE PageID = 6