MySQL self-join issue-CodePudding

I have a source table "pairs":

    | r  |  x  | y  |
    | 1  | 20  | 20 |
    | 2  | 20  | 20 | 
    | 3  | 20  | 21 |
    | 4  | 21  | 20 |
    | 5  | 22  | 23 |
    | 6  | 23  | 22 |

After applying query with self-join

  select p1.r, p1.x, p2.y, p2.r
    from pairs p1 join pairs p2 on (p1.x = p2.y)
    order by p1.r;

I receive the result as expected:

r   x   y   r
1   20  20  1
1   20  20  2
1   20  20  4
2   20  20  1
2   20  20  2
2   20  20  4
3   20  20  1
3   20  20  2
3   20  20  4
4   21  21  3
5   22  22  6
6   23  23  5

In particular, for r=1, x=20 in one table is aligned (r=1, y=20),(r=2,y=20) and (r=4,y=20) in second table.

But when I apply the following join

select p1.r, p1.x, p2.y, p2.r
  from pairs p1 join pairs p2 on (p1.y = p2.x)
  order by p2.r;

I receive something strange:

r   x   y   r
4   21  20  1
2   20  20  1
1   20  20  1
4   21  20  2
2   20  20  2
1   20  20  2
4   21  21  3
2   20  21  3
1   20  21  3
3   20  20  4
6   23  23  5
5   22  22  6

Taking first row, I cant understand, why for r=1, y=20 is aligned (r=4, x=21).
After all (y=20) < (x=21), which contradicts with join condition: p1.y = p2.x

Here is sample

CodePudding user response：

It is doing EXACTLY what you asked it to.

select p1.r, p1.x, p2.y, p2.r from pairs p1 join pairs p2 on (p1.y = p2.x) order by p2.r;

Your JOIN condition is based on p1.y = p2.x. So, if we look at your original sample data

r    x    y
1    20   20 
2    20   20  
3    20   21 
4    21   20 
5    22   23 
6    23   22

Your OUTPUT columns of X and Y are reversed from the JOIN condition.

So, your resulting first line showing x=21 and y=20. Look at the corresponding rows.

r   x   y   r
4   21  20  1

Row 4 = x=21,  y=20
Row 1 = x=20,  y=20

So, the Row4 y value of 20 IS equal to row1 x value of 20. But you have the output pulling the row4 X value and row1 Y value hence the 21 and 20 as properly output.

If you changed your query to match the JOIN correlation, it would probably make more sense.

select p1.r, p1.y, p2.x, p2.r from...