Reverse join in MySQL

Question

SO,

The problem

My question is about - how to join table in MySQL with itself in reverse order? Suppose I have:

id  name
1   First
2   Second
5   Third
6   Fourth
7   Fifth
8   Sixth
9   Seventh
13  Eight
14  Nine
15  Tenth

-and now I want to create a query, which will return joined records in reverse order:

left_id name    right_id    name 
   1    First      15   Tenth 
   2    Second     14   Nine 
   5    Third      13   Eight 
   6    Fourth      9   Seventh 
   7    Fifth       8   Sixth 
   8    Sixth       7   Fifth 
   9    Seventh     6   Fourth 
  13    Eight       5   Third 
  14    Nine        2   Second 
  15    Tenth       1   First

My approach

I have now this query:

SELECT 
  l.id AS left_id, 
  l.name, 
  (SELECT COUNT(1) FROM sequences WHERE id<=left_id) AS left_order, 
  r.id AS right_id,
  r.name,
  (SELECT COUNT(1) FROM sequences WHERE id<=right_id) AS right_order 
FROM 
  sequences AS l 
  LEFT JOIN 
    sequences AS r ON 1
HAVING
  left_order+right_order=(1+(SELECT COUNT(1) FROM sequences));

-see this fiddle for sample structure & code.

Some background

There's no use case for that. I was doing that in application before. Now it's mostly curiosity if there's a way to do that in SQL - that's why I'm seeking not just 'any solution' (like mine) - but as simple as possible solution. Source table will always be small (<10.000 records) - so performance is not a thing to care, I think.

The question

Can my query be simplified somehow? Also, it's important not to use variables. Order could be included in result (like in my fiddle) - but that's not mandatory.

While we're thinking - could you provide a use case for that? It's curious — zerkms, Oct 01 '13 at 11:01
I guess you'll limited to MySQL? Otherwise it would be quite easy using window functions. — , Oct 01 '13 at 11:52
@a_horse_with_no_name I'm limited with `SQL` itself, actually (in ideal case, query should work in Postgres & MySQL) - so I think best way is to avoid DBMS-related features — Alma Do, Oct 01 '13 at 11:53
Well window functions are ANSI standard which doesn't make them "DBMS specific" (actually MySQL is the only major DBMS to not have them). — , Oct 01 '13 at 11:56
Hm, did not know that. Well, since MySQL is supposed to be used - I can't use them (and so why I want to avoid MySQL variables as well). Best solution could probably be - do that in application (100% SQL-portable :p ) - but I have curiosity about SQL solution — Alma Do, Oct 01 '13 at 11:59

score 2 · Accepted Answer · answered Oct 01 '13 at 11:43

The only thing i can think to be improved is

SELECT 
  l.id AS left_id, 
  l.name ln, 
  (SELECT COUNT(1) FROM sequences WHERE id<=left_id) AS left_order, 
  r.id AS right_id,
  r.name rn,
  (SELECT COUNT(1) FROM sequences WHERE id>=right_id) AS right_order 
FROM 
  sequences AS l 
  LEFT JOIN 
    sequences AS r ON 1
HAVING
  left_order=right_order;

There are 2 changes that should make this a little bit faster:

1) Calculating right order in reverse order in the first place

2) avoid using SELECT COUNT in the last line.

Edit: I aliased the ln,rn because i couldn't see the columns in fiddle

score 1 · Answer 2 · edited May 23 '17 at 12:03

Without the SQL standard RANK() OVER(...), you have to compute the ordering yourself as you discovered.

The RANK() of a row is simply 1 + the COUNT() of all better-ranked rows. (DENSE_RANK(), for comparison, is 1 + the COUNT() of all DISTINCT better ranks.) While RANK() can be computed as a scalar subquery in your SELECT projection — as, e.g., you have done with SELECT (SELECT COUNT(1) ...), ... — I tend to prefer joins:

    SELECT lft.id AS "left_id", lft.name AS "left_name",
           rgt.id AS "right_id", rgt.name AS "right_name"
      FROM (   SELECT s.id, s.name, COUNT(1) AS "rank"     -- Left ranking
                 FROM sequences s
            LEFT JOIN sequences d ON s.id <= d.id
             GROUP BY 1, 2) lft
INNER JOIN (   SELECT s.id, s.name, COUNT(1) AS "rank"     -- Right ranking
                 FROM sequences s
            LEFT JOIN sequences d ON s.id >= d.id
             GROUP BY 1, 2) rgt
           ON lft.rank = rgt.rank
  ORDER BY lft.id ASC;

This also looks good with `JOIN` (actually, I prefer `JOIN` rather than subqueries) — Alma Do, Oct 01 '13 at 13:52

score 0 · Answer 3 · answered Oct 01 '13 at 11:10

0

SET @rank1=0;
SET @rank2=0;

SELECT *
FROM (SELECT *, @rank1 := @rank1 + 1 AS row_number FROM sequences ORDER BY ID ASC) t1
INNER JOIN (SELECT *, @rank2 := @rank2 + 1 AS row_number FROM sequences ORDER BY ID DESC) t2
on t1.row_number = t2.row_number

For some reason sql fiddler does show only 3 columns for this, not sure if my query is bad.

answered Oct 01 '13 at 11:10

athabaska

455
3
22

I don't want to use variables (due to portability reasons) - so this is not what I'm seeking for – Alma Do Oct 01 '13 at 11:13

Reverse join in MySQL

3 Answers3