Possible To Execute Joins Using Non-Key Fields?

SQL++
1

The following join attempts to join Travel-Sample and Beer-Sample by a non-key field "country.

 Select b.country COUNTRY, b.name BREWERY, a.name AIRLINE 
 From `travel-sample` a 
 Join `beer-sample` b On Keys (a.country) 
 Order By a.country, b.name;

While the query is processed it returns a null result string, which is incorrect.

The discussion of the “On Keys” clause with respect to its use in joins does state that the key being referenced should be the primary key of the table being joined, so I understand that this query will not work in its present form. My question: Is there any way in N1QL to execute this join correctly?

btw: I’m trying to catalog all of the places where N1QL is different from SQL, and in fact the above join could be successfully executed in generic SQL.

2

Hi @morrie,

This pattern is currently not supported, but will be supported in Couchbase in the future. Can you share your N1QL vs. SQL catalog with us when it is ready?

On JOINs, we are incrementally adding to our JOIN support. In 4.0, the left keyspace had to contain the primary of the right table. In 4.1, we expanded the syntax to include the case where the right keyspace contains the primary of one of the left keyspaces.

Longer term, we should provide all the SQL JOIN functionality, while keeping the additional JSON support unique to N1QL.

1 Like
3

Hi @geraldss,

Could you give an example for how to use this new JOIN capability of 4.1 (the case where the right keyspace contains the primary of one of the left keyspaces).

Also, does 4.1 also support NEST with the similar requirement on keys as above?

Thanks!

4

Yes and yes. This right JOIN capability is not yet documented (not yet unveiled), but it is there and in use in production.

SELECT *
FROM a JOIN b ON KEY b.a_id FOR a;

SELECT *
FROM a NEST b ON KEY b.a_id FOR a;

Note that ON KEY is singular, not ON KEYS. Still converging on syntax. Finally, note that b.a_id must be indexed.

1 Like
5

Awesome, @geraldss. Thank you!!!

6

@a.reisberg, did you try it out?

1 Like
7

@geraldss Thanks for your follow up! I haven’t tried it yet, since I don’t have the enterprise edition (4.1). It will be one of the first things I try when it’s available as community edition (I’ve bookmarked your reply).

8

@geraldss is it possible
SELECT *
FROM a JOIN b ON KEY b.c_id FOR a.c_id;

9

Hi @muhammad1ammad, N1QL only supports JOINs on primary keys.