Preserve order over an intersect or except operation

SQL++
1

Please tell me there’s some way to do this:

I have (say) an order document with a foreign key to a customer. I need to get page worth’s of customers from the orders. I have indexing in place to quickly retrieve customer ids given the ‘order’ predicates.

I need to return the customers ordered by a field on the ‘order’

the problem is that I need to filter by a field or two on the ‘customer’.

So I can’t do a join because that will fetch all the customers in the db before the filtering.

So I’m looking to use intersect like this:

Select raw customerId from (Select customerId from default where type = ‘order’ order by createDate desc)
Intersect
Select raw customerId from default where type = ‘customer’ and predicate 1…n (using covering indexes)

then I could apply my offset + limit + join to the customer doc.

Unfortunately it does not look like ordering is preserved upon exiting the intersect/except

And I don’t think I can include the ordering field in the projection for the first query (because then Intersect wont work)

Any ideas?

2

Unless you have explicit ORDER on top level query, results will not be ordered.

JOIN can produce 1:n (i.e. m:m*n) if you going to use paginate based on order offset make sure you don’t skip data (i.e. it may terminate in middle and must start where left off.

Also checkout last point (subquery) if you can use that .

1 Like
3

thank you very much @vsr1

the hash table optimization is awesome.

a subquery in list will work for me, but I have to know whether the execution engine is smart enough to evaluate that subquery => hash table only once (and not again and again for every row in the in the potential results)

4

If IN list is constant it only builds once.

1 Like
5

I am sorry to be obtuse. When you say ‘if is constant’ does that include where it is an uncorrelated subquery, which is constant only in the sense of some level of isolation, but not technically constant. @vsr1

6

hashing of IN clause (build hash once and probe) will automatically enabled when (right side of IN must be constant, named/positional parameter, CTE variable , non-correlated subquery and number of elements IN must be more than 16). i.e. value must not change for each document.

1 Like