SQL multiple alias in subquery

Question

I have a setup where I would like to support a user defined subquery inside of a spatial query to identify overlapping polygons. Below is an example:

SELECT i1.id, i2.id
FROM
    (SELECT * FROM Images WHERE id > 600) i1,
    (SELECT * FROM Images WHERE id > 600) i2
WHERE ST_INTERSECTS(i1.footprint_latlon, i2.footprint_latlon) = TRUE
AND i1.id > i2.id

The above is working, but I more canonical way to do this exists.

The inner query is completely arbitrary here and the important component is that I am defining i1 and i2 using an identical query. I am doing this so that I have i1 and i2 aliases for the outer, spatial query.

Is it necessary to execute the inner query twice or does a better way to create the i1, and i2 aliases exist? Lots of example on executing subqueries with a single (mandatory alias), but I am not seeing any examples of 'multi-aliases'.

Even with WITH i AS (foo) SELECT bah FROM i AS i1, i AS i2 WHERE... the sub query is effectively resolved twice. To avoid that YOU need to preserve the result, such as with a temp table. But doing so may not be a good idea; you need to write the results somewhere and lose the indexes, etc. For that reason caching the results may be more expensive than resolving it twice. Also, note that sql is a declarative language and the rdbms is free to rearrange the execution, so as to make use of indexes, etc. In short, don't prematurely optimise. Be concerned with maintainable code. — MatBailie
– MatBailie, Commented Mar 28, 2018 at 5:28
@MatBailie I very much appreciate the point about premature optimization and the nature of SQL withr espect to how the query is performed. Interesting to know that indices are stripped, but that makes total sense once you highlighted that issue. — Jzl5325
– Jzl5325, Commented Mar 28, 2018 at 13:27
@Jzl5325 No, the indices are not stripped (what is that?) The CTE is executed exactly once, and every time the CTE is referenced, its (materialized) result set is used. And: this resultset has no (sub)structure, at most it will have a defined order. [see my answer on how to avoid the CTE] — wildplasser
– wildplasser, Commented Mar 28, 2018 at 13:53
@wildplasser Thanks! Makes sense. I am reading about CTE right now and trying out each of the answers to make sure I understand how they are working. By stripped, I mean removed and not used, but it sounds like that is not the case. This question looks to be pretty good for filling in my knowledge gap on a CTE. — Jzl5325
– Jzl5325, Commented Mar 28, 2018 at 14:00
@wildplasser - My comment noted that if the OP persists the intermediate results such as with a temp table then he would lose the indexes. — MatBailie
– MatBailie, Commented Mar 30, 2018 at 13:55

Turophile · Accepted Answer · 2018-03-28 10:22:05Z

4

I don't think there is a clean easy way to "alias an alias". You could do something like this:

WITH 
   i1 AS (SELECT * FROM Images WHERE id > 600),
   i2 AS (SELECT * FROM i1)
SELECT i1.id, i2.id
FROM
    i1, i2
WHERE ST_INTERSECTS(i1.footprint_latlon, i2.footprint_latlon) = TRUE
AND i1.id > i2.id

EDIT and much nicer, as suggested by @MatBailie :

WITH 
   i AS (SELECT * FROM Images WHERE id > 600)
SELECT i1.id, i2.id
FROM
    i AS i1, i AS i2
WHERE ST_INTERSECTS(i1.footprint_latlon, i2.footprint_latlon) = TRUE
AND i1.id > i2.id

edited Mar 28, 2018 at 10:22

answered Mar 28, 2018 at 5:07

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatBailie Over a year ago

WITH i AS (foo) SELECT bah FROM i AS i1, i AS i2 WHERE...

Turophile Over a year ago

@MatBailie - nice improvement to my suggestion.

wildplasser · Accepted Answer · 2018-03-28 10:47:51Z

2

My inclination would be one of the following:

select i1.id, i2.id
from images i1 join
     images i2
     on st_intersects(i1.footprint_latlon, i2.footprint_latlon)
where i1.id > 600 and i2.id > i1.id;

Or:

with ix as (
      select i.*
      from images i
      where i.id > 600
     )
select i1.id, i2.id
from ix AS i1 join
     ix AS i2
     on st_intersects(i1.footprint_latlon, i2.footprint_latlon)
where i2.id > i1.id;

I don't think there is a shortcut on performing the self join.

edited Mar 28, 2018 at 10:47

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

answered Mar 28, 2018 at 5:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

wildplasser · Accepted Answer · 2018-03-28 13:47:09Z

1

If you don't want to repeat the (complicated) subquery, you could either use a CTE (see Gordon Linoff's answer) or use a (temp) view containing the subquery [a CTE behaves differently for the optimiser/planner, for larger resultsets this could cause subobtimal performance] :

CREATE TEMP VIEW vv AS
     -- Put your complex subquery here ...
SELECT * FROM Images WHERE id > 600
        ;

SELECT i1.id, i2.id
FROM vv i1
JOIN vv i2 ON ST_INTERSECTS(i1.footprint_latlon, i2.footprint_latlon)
AND i1.id > i2.id
        ;

edited Mar 28, 2018 at 13:47

answered Mar 28, 2018 at 10:46

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Comments

Jay Kumar R · Accepted Answer · 2018-03-28 05:00:29Z

0

SELECT i1.id, i2.id
FROM Images i1,Images i2, 
WHERE i2.id > 600
  AND i1.id > i2.id
  AND ST_INTERSECTS(i1.footprint_latlon, i2.footprint_latlon) = TRUE

answered Mar 28, 2018 at 5:00

Jay Kumar R

5372 silver badges7 bronze badges

1 Comment

MatBailie Over a year ago

This totally misses the point of the question.

The inner query is completely arbitrary here and the important component is that I am defining i1 and i2 using an identical query.

Collectives™ on Stack Overflow

SQL multiple alias in subquery

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related