Joining two buckets backwards

SQL++
1

I have two buckets, one containing users and one containing devices.
One user has many devices, however the reference is kept in the device, i.e. each device has a reference to his user.
in users bucket there are no references to devices bucket.

I can perform a select like that:
SELECT * FROM devices JOIN users ON KEYS devices.user_id

But i would like to do the join reversed, like that:
SELECT * FROM users JOIN devices

is it possible?

2

Hi @eyalrubichi,

This is not yet documented and officially supported, but in 4.1, you can do:

Note the ON KEY instead of ON KEYS. This syntax is not yet official and may change.

You must have an index on devices.user_id.

3

Hi, @geraldss

When will this functionality be supported officially? Is it production ready currently?

4

What about star-schema, if there are a fact-table and several JOINs of dimension-tables? It is necessary to filter by fileds from dimensions. Can indexes be used in such case?
(e.g. SELECT d1.name, d2.name, d3.name FROM facts JOIN d1 ... JOIN d2 ... JOIN d3 WHERE d1.field='value' AND d2.field='value')

5

This functionality is production quality and is already deployed in production at select customers. It is the syntax, documentation, and training that have not been officially publicized.

To your second question, yes, you can use star schema. The first dimension will use an index scan. Choose the most selective first dimension. The fact table will use a reverse JOIN. The remain dimensions will use forward JOINs.

EXPLAIN SELECT *
FROM d1
JOIN fact f ON KEY f.d1_id FOR d1
JOIN d2 ON KEYS f.d2_id
JOIN d3 ON KEYS f.d3_id
WHERE d1.field = 'value' AND d2.field='value' ...;
6

I’ll try it. Thanks!