Skip to main content
    • Knowledge Base/
    Compare Resultsets

    How to Validate if Two Queries Return the Same Result-sets

    Learn how to validate that two ClickHouse queries produce identical result-sets using hash functions and comparison techniques.

    Question​

    How can I validate that two queries return the same resultsets?

    Answer​

    You can use the below approach:

    The example uses a CTE to calculate sums of the cityHash value of each row in these two queries and will return 1 if the two resultsets are identical.

    Using some integers sequence data and some pretty formatting:

    will return:

    While this can be handy in many scenarios, it can't be considered as a silver bullet to validate equality of resultsets for all types and there are caveats to using it, for example if any row contains NULL values the above approach will fail.

    · 2 min read