Обсуждение: Subselect with no records results in final empty set
![]() | This email has been checked for viruses by Avast antivirus software. |
DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.
Sterpu Victor wrote > Hello > > I have this select where the last subselect will return a empty set and > because of this the whole select will be empty. > How can I change this syntax so I will have a row result even if the > last select is empty? > > SELECT * FROM (SELECT 1 AS t1, 2 AS t2) as t, (SELECT 3 AS t3) as s, > (SELECT * FROM atc WHERE id = '1231222' LIMIT 1 OFFSET 0) AS s3; Use explicit join syntax; and then pick the "LEFT [OUTER] JOIN" variant. SELECT FROM t LEFT JOIN s3 ON (TRUE) David J. -- View this message in context: http://postgresql.nabble.com/Subselect-with-no-records-results-in-final-empty-set-tp5836011p5836014.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
It works as you sugested, this is the syntax I used: SELECT * FROM (SELECT 1 AS t1, 2 AS t2) AS t1 LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (null) Thank you. ------ Original Message ------ From: "David G Johnston" <david.g.johnston@gmail.com> To: pgsql-general@postgresql.org Sent: 1/29/2015 10:03:38 PM Subject: Re: [GENERAL] Subselect with no records results in final empty set >Sterpu Victor wrote >> Hello >> >> I have this select where the last subselect will return a empty set >>and >> because of this the whole select will be empty. >> How can I change this syntax so I will have a row result even if the >> last select is empty? >> >> SELECT * FROM (SELECT 1 AS t1, 2 AS t2) as t, (SELECT 3 AS t3) as s, >> (SELECT * FROM atc WHERE id = '1231222' LIMIT 1 OFFSET 0) AS s3; > >Use explicit join syntax; and then pick the "LEFT [OUTER] JOIN" >variant. > >SELECT >FROM t >LEFT JOIN s3 ON (TRUE) > >David J. > > > >-- >View this message in context: >http://postgresql.nabble.com/Subselect-with-no-records-results-in-final-empty-set-tp5836011p5836014.html >Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
I changed the final query to SELECT * FROM (SELECT 1 AS t1, 2 AS t2) AS t1 LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (1=1) ON(null) never matched. ------ Original Message ------ From: "Sterpu Victor" <victor@caido.ro> To: "David G Johnston" <david.g.johnston@gmail.com>; pgsql-general@postgresql.org Sent: 1/29/2015 10:22:28 PM Subject: Re: [GENERAL] Subselect with no records results in final empty set >It works as you sugested, this is the syntax I used: >SELECT * FROM >(SELECT 1 AS t1, 2 AS t2) AS t1 >LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (null) > >Thank you. > >------ Original Message ------ >From: "David G Johnston" <david.g.johnston@gmail.com> >To: pgsql-general@postgresql.org >Sent: 1/29/2015 10:03:38 PM >Subject: Re: [GENERAL] Subselect with no records results in final empty >set > >>Sterpu Victor wrote >>> Hello >>> >>> I have this select where the last subselect will return a empty set >>>and >>> because of this the whole select will be empty. >>> How can I change this syntax so I will have a row result even if the >>> last select is empty? >>> >>> SELECT * FROM (SELECT 1 AS t1, 2 AS t2) as t, (SELECT 3 AS t3) as s, >>> (SELECT * FROM atc WHERE id = '1231222' LIMIT 1 OFFSET 0) AS s3; >> >>Use explicit join syntax; and then pick the "LEFT [OUTER] JOIN" >>variant. >> >>SELECT >>FROM t >>LEFT JOIN s3 ON (TRUE) >> >>David J. >> >> >> >>-- >>View this message in context: >>http://postgresql.nabble.com/Subselect-with-no-records-results-in-final-empty-set-tp5836011p5836014.html >>Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >>-- >>Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>To make changes to your subscription: >>http://www.postgresql.org/mailpref/pgsql-general > > >--- >This email has been checked for viruses by Avast antivirus software. >http://www.avast.com > > > >-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
It works as you sugested, this is the syntax I used:
SELECT * FROM
(SELECT 1 AS t1, 2 AS t2) AS t1
LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (null)
Thank you.
On 1/29/2015 12:36 PM, Sterpu Victor wrote: > > ON(null) never matched. NULL is neither true nor false. ON somefieldinthejoin IS NULL would be a valid syntax. except, that's NOT a join condition, a join condition would be ON left_table.something = right_table.something > ON (1=1) equivalent to ON TRUE but that will cross join everything, so if the left table has N rows and the right table has M rows, you'll end up with N*M rows in the result. is that really what you want ?? -- john r pierce 37N 122W somewhere on the middle of the left coast
I always have a single row in these selects so the result will have only a row. I must make a single select for a framework that takes as parameter a single select to complete a XML template in a particular situation. ------ Original Message ------ From: "John R Pierce" <pierce@hogranch.com> To: pgsql-general@postgresql.org Sent: 1/29/2015 10:52:25 PM Subject: Re: [GENERAL] Subselect with no records results in final empty set >On 1/29/2015 12:36 PM, Sterpu Victor wrote: >> >>ON(null) never matched. > >NULL is neither true nor false. > >ON somefieldinthejoin IS NULL would be a valid syntax. except, that's >NOT a join condition, a join condition would be ON left_table.something >= right_table.something > >>ON (1=1) > >equivalent to ON TRUE > >but that will cross join everything, so if the left table has N rows >and the right table has M rows, you'll end up with N*M rows in the >result. is that really what you want ?? > > > > >-- john r pierce 37N 122W >somewhere on the middle of the left coast > > > >-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com