How to use SELF JOIN In SQL
Another approach for computing the delta between two columns on different rows is, by using self-join to retrieve the value of the previous row and then calculate the value of the difference or delta.
Use the below command to compute the delta between two columns on different rows using self-join.
SELECT
A.geek_value,
A.geek_value - B.geek_value AS Delta
FROM
geeks_table A
JOIN
geeks_table B ON A.geek_id = B.geek_id + 1;
Output:
geek_value |
delta |
---|---|
200 |
100 |
300 |
100 |
400 |
100 |
500 |
100 |
Explanation:
- SELECT A.geek_value,A.geek_value – B.geek_value AS Delta: This part of the query selects the table’s first occurrence of geeks_table (A) geek_value column and then formulates the difference between the current row value (A.geek_value) and previous row value (B.geek_value).
- JOIN geeks_table B ON A.geek_id = B.geek_id + 1: This part of the query does the inner join or self join of A and B in both the table. It combines rows where the geek_id of A is the same as the geek_id of B incremented by 1.
Compute a Difference (Delta) Between Two Columns on Different Rows
MySQL is an open-source, Relational Database Management System that stores data in a structured format using rows and columns. It’s software that enables users to create, manage, and manipulate databases. Similar to SQL we use queries to store and access data in the MySQL databases.
In this article, we’ll learn how to calculate the difference (delta) between two columns on different rows. Before moving into this article, you need to install MySQL. To do so you can refer to the below articles,