Menu
I was wondering, is it possible to join the result of a query with itself, using PostgreSQL?
368
A subquery can be used with JOIN operation. In the example below, the subquery actually returns a temporary table which is handled by database server in memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select statement.
A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
What Does Self-Join Mean? A self-join, also known as an inner join, is a structured query language (SQL) statement where a queried table is joined to itself. The self-join statement is necessary when two sets of data, within the same table, are compared.
The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.
You can do so with WITH:
WITH subquery AS( SELECT * FROM TheTable ) 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 ... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
