Search code examples
mysqljoininner-join

MySQL Self-Join Clause (Incrementing ID)

I have a table called "stocks", and its records are described in the following

ID| Date       | Qty
1 | 2017-01-03 | 10
2 | 2017-02-11 | 15
3 | 2017-03-15 | 16
4 | 2017-04-25 | 30
5 | 2017-06-20 | 40

I want to find the difference between the "Qty" of each successive rows. For that purpose, I use the query:

SELECT first_table.id as "First Table ID"
     , first_table.date AS "From"
     , first_table.qty AS "First Table Qty"
     , second_table.id as "Second Table ID"
     , second_table.date AS "To"
     , second_table.qty AS "Second Table Qty"
     , (second_table.qty - first_table.qty) AS Quantity_Difference 
  FROM stocks first_table 
  JOIN stocks second_table 
    ON first_table.id + 1 = second_table.id

The following depicts the result that I got from the above query.

link

My questions are:

1) In the above query, what does the clause first_table.id + 1 = second_table.id mean?

2) In the JOIN clause, I add "1" on the first_table ID (i.e. first_table.id + 1). But, in the result that I got, why does the second_table ID that get incremented? I thought that, by adding 1 to the first_table ID, the first_table ID that should be incremented instead of the second table ID.

Solution

  • In the above query, what does the clause first_table.id + 1 = second_table.id mean?

    It means to join rows in the table whose IDs differ by 1.

    But, in the result that I got, why does the second_table ID that get incremented?

    It's not incrementing IDs, it's adding 1 to the ID of one row and comparing that with the ID of another row. When first_table.id = 2, first_table.id + 1 is 3, so it joins that row with second_table.id = 3.

    The addition is only done in the WHERE clause, you're not returning the result in the SELECT list. So it selects the original first_table.id, not first_table.id + 1.

    As mentioned in the comments, this query will only work properly when IDs all increment by 1. If there are any gaps in the ID sequence, you'll skip the first_table.id before the gap and second_table.id after the gap. See Subtract Quantity From Previous Row MySQL for a better way to subtract values from adjacent rows that doesn't depend on IDs being sequential.