Self-join of a subquery

Question

Hi all,
I was wondering, is it possible to join the result of a query with itself?

(I'm using PostgreSQL)

Answer 1

You can do so with WITH:

WITH (
    SELECT * FROM TheTable
) subquery
SELECT *
FROM subquery q1
JOIN subquery q2 on ...

Or by creating a VIEW that contains the query, and joining on that:

SELECT *
FROM TheView v1
JOIN TheView v2 on ...

Or the brute force approach: type the subquery twice:

SELECT *
FROM (
    SELECT * FROM TheTable
) sub1
LEFT JOIN (
    SELECT * FROM TheTable
) sub2 ON ...

Answer 2

Do you mean, the result of a query on a table, to that same table. If so, then Yes, it's possible... e.g.

--Bit of a contrived example but...
SELECT * 
FROM Table
INNER JOIN
(
    SELECT 
          UserID, Max(Login) as LastLogin
    FROM
          Table
    WHERE 
          UserGroup = 'SomeGroup'
    GROUP BY
          UserID

) foo
ON Table.UserID = Foo.UserID AND Table.Login = Foo.LastLogin

Answer 3

Yes, just alias the queries:

SELECT  *
FROM    (
        SELECT  *
        FROM   table
        ) t1
JOIN    (
        SELECT  *
        FROM   table
        ) t2
ON      t1.column < t2.other_column