Join on non-key element

I’m looking for a way to join on non-key elements.

To give a complete example, I would like to have the list of airport names where I can go starting from SEA.

Is this possible using N1QL ? I can’t find a way!

After a lot of tests, i cam across this:

SELECT distinct(r.airportname) FROM 'travel-sample' r WHERE r.type = "airport" AND r.faa WITHIN ( SELECT distinct(rr.destinationairport) FROM 'travel-sample' rr WHERE rr.type = "route" AND rr.sourceairport='SEA' ) ORDER BY r.faa

Isn’t it a better way of doing it ?

Not sure which version you are suing but if you are on 4.5 preview, I assume you have looked at this page for examples:

The page has JOIN syntax we now support in 4.5 that allows left or the right side to specify the driving JOIN attribute.

Hi @cambierr,

Unfortunately, all N1QL JOINs, including the new JOINs in 4.5, require the primary key of at least one term. In the travel-sample data, it would be possible to use the FAA code as a primary key for airports, assuming these codes are immutable. But the travel-sample doesn’t come that way out of the box, so something like your query is the way to do it in N1QL. I modified your query below to make it slightly more efficient:

SELECT DISTINCT r.airportname
FROM 'travel-sample' r 
WHERE r.type = "airport" AND r.faa IN (
SELECT DISTINCT RAW rr.destinationairport FROM 'travel-sample' rr WHERE rr.type = "route" AND rr.sourceairport='SEA'
ORDER BY r.faa

The following two indexes would help:

CREATE INDEX idx_name ON `travel-sample`( faa, airportname, type );

CREATE INDEX idx_source ON `travel-sample`( sourceairport, destinationairport, type );

Finally, your example surfaced a separate gap that we will fix for the upcoming beta, thanks! (Handling of IN for indexes).


Thank you for that link, it will be usefull. I’m just not sure about what solution will have the best performances ?


What about the link of @cihangirb ? Otherwise, thank you for perf improvements (I didn’t know about RAW)

Btw, what’s the difference between SELECT DINSTINCT … and SELECT DISTINCT(…) ?

Hi @cambierr, no difference, the parentheses are optional. The links are for documentation, which you should read. You now have the solution, so you are all set.