How to use Subqueries In SQL

Another approach is to use subqueries to retrieve the values of the previous row and then calculate the delta.

SELECT 
    t1.value,
    t1.value - t2.value AS delta
FROM 
    example_table t1
JOIN 
    example_table t2 ON t1.id = t2.id + 1;

Explanation:

• SELECT t1.value, t1.value – t2.value AS delta: This part of the query selects the table’s first occurrence of example_table (also called t1) value column and then formulates the difference between the current row value (t1.value) and previous row value (t2.value) row id one less than current row through calculating.
• t1.value: Selects the value column from the first occurrence of example_table.
• t1.value – t2.value AS delta: Computes the delta (which is the difference between the value column of the current row (t1.value) and the value column of the row with id one less than the current row (t2.value)), and aliases it as delta.
• FROM example_table t1: This part of the query shows the first occurrence of example_table and aliases it as t1.
• JOIN example_table t2 ON t1.id = t2.id + 1: This part of the query does the inner join of t1 and t2 in both the table. It combines rows where the id of t1 is the same as the id of t2 incremented by 1.

Output:

In PostgreSQL the modern relational database system, two types of queries are supported: SQL for the traditional relational databases and JSON for the non-relational databases. It is free and anybody can use it. The difference (delta) computation between the values of two columns of PostgreSQL at different rows can be done with various techniques.

In data manipulation and data analysis tasks, the numerical values on different rows in two columns are often compared. The difference (delta) is computed to do such a comparison. This article explores various techniques to achieve this task efficiently and effectively.