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,

How to use SELF JOIN In SQL

Compute a Difference (Delta) Between Two Columns on Different Rows

Similar Reads