What’s the best query trick you are using?

Community Manager
Community Manager

Hi everyone,

We at Exasol often have a good understanding of what best practices are for our users. However, oftentimes, the real value is only seen by you and sometimes only develops over time. This is why we are interested in hearing what your best query trick is.

It could be a query trick, which only has a small impact on one query but performed often is almost invaluable to you. Or it is a practice you use every day or you use it one-time only with great impact. 

What is your best query trick?

Best regards,




Hmmm, I guess there´s one thing above most others: using the SQL pre-processor to bend unruly SQLs to the will of the optimizer 😉

Team Exasol
Team Exasol

Hehe, @mwellbro opening the box of pandora right in the first reply, I like the vibe 😁
You are right, it is so powerful, every advanced Exasol user should know about it.

I am not sure if it is a good idea to do so with every other database technology, but this "best practice" can bring some order into bigger, self-written  statements - and even front ends like Microstrategy can generate it:
I am talking about common table expressions (CTE, with-clause).
Reason: This approach is structuring your query so that someone else can understand it easily, and you don't get lost yourself in your approach to write a query.
Especially if you use multiple of them at the beginning of a statement.
My template:

temp_01 AS
    SELECT ...
temp_02 AS
    SELECT [...] from temp_01 JOIN [...]
temp_03 AS
    SELECT [...] from temp_02 GROUP BY [...]
SELECT [...];

In the below article from @exa-Nico, last example. Note they are in fact different from "named subselects":

Community Manager
Community Manager
Sounds good, can you share an example?
Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

Team Exasol
Team Exasol

EXPLAIN VIRTUAL uses the VS adapter to rewrite the original query before feeding it into the ExaLoader via IMPORT.

There are other, rarer cases, but let's focus on this one. If EXPLAIN VIRTUAL does not succeed, you know that the problem is either the connection to the remote system (80% of all cases) or the adapter itself. You can pretty much rule out the ExaLoader in this case. Except in rare situations where that has an extra connection problem --but that is a story for another article.

If EXPLAIN VIRTUAL succeeds on the other hand, you get the rewritten query, which you can then review.

If it is fine, you have a problem in the ExaLoader. If the query looks strange, it is the Virtual Schema adapter's fault. So you can do a lot of debugging with that simple trick without having to pull a single log file. Only if all those investigations are inconclusive, you really need to start digging deeper.


It's not really a query trick, since it's actually just one of my favourite exasol features.

But the local  alias is just soooo useful, especially when using it with analytical functions.

Being able to directly use the result of an analytical function or UDF in the same SELECT statement saved me hundreds of

lines of SQL.


WITH base AS
SELECT 'a' id_column, 112 number_column  UNION ALL
SELECT 'a' id_column, 134 number_column  UNION ALL
SELECT 'b' id_column, 11 number_column  UNION ALL
SELECT 'b' id_column, 232 number_column  UNION ALL
SELECT 'b' id_column, 242 number_column  UNION ALL
SELECT 'b' id_column, 223 number_column 
	lag(number_column) OVER (PARTITION BY id_column ORDER BY number_column) number_column_before,
	number_column - LOCAL.number_column_before difference2before



Community Manager
Community Manager

Maybe not a feature, but you can determine the timestamp that a session was opened by using the following query:

SELECT FROM_POSIX_TIME(<session ID>/1024/1024/1000);

Really helps to determine what day logs might be needed

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution


A UDF script that I find quite handy for simplifying some SQL is:

-- This aggregation function takes 2 arguments.
-- The first argument is a number to be summed up,
-- but the number is only summed once per unique second argument.
-- Given the following data:
--  ID  VAL
--  --- ----
--   a   1
--   a   1
--   b   2
-- then: SELECT sum_distinct(val, id) 
-- would return: 1 + 2 = 3 rather than 1 + 1 + 2 = 4
-- This is useful for grouped statements where extra joins
-- may cause underlying duplicate rows which would otherwise
-- mess up regular SUMs. With the right choice of the second
-- argument the SUM will ignore the duplicate rows.
-- This avoids having to do multiple levels of grouping via subqueries.
function run(ctx)
    local sum
    local rows_seen = {}
        if ctx[1] ~= NULL and
           ctx[2] ~= NULL and
           rows_seen[tostring(ctx[2])] ~= 1
            if sum == nil then sum = 0 end
            sum = sum + ctx[1]
            rows_seen[tostring(ctx[2])] = 1
    until not ctx.next()
    return sum

 As the comment mentions, this is handy in cases where duplicate rows (due to extra one-to-many joins) causes a regular SUM() to be wrong. Usually this requires sub-queries with multiple levels of grouping but with this UDF you can avoid the subqueries and sum at any level of grouping you wish.

Speaking of wishing, I wish Exasol had this as a native function. 😉

Community Manager
Community Manager
Right place to ask for this
Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...