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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Jan 29, 2015 at 1:22 PM, Sterpu Victor <victor@caido.ro> wrote:

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.

You will notice that everyone responding to you is bottom-posting...
​

​Anyway, the use of "ON (null)" is unusual...

Also, the fact that your toy model is making considerable use of "CROSS
JOIN" is unusual; one common reality of "relational databases" is that
usually the things you are going together are "related" and the join
conditions reflect those relationships. I'd suggest using actual tables
(or CTE/WITH) with multiple rows of data to try and learn how to write
queries. The number of times you are going to join together multiple
results each only having a single row is slim.

David J.​

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general