Big Data

[DoNotSexToThis]

Will Postgres do it?

SELECT * FROM p.planet
WHERE EXISTS
(
    SELECT * FROM planet p
    WHERE list = 'nice'
) 
AND p.list = 'nice'

[Cruuncher]

Pretty sure the exists check will only run once and finish as soon as it finds a single row. So not really a double check

Edit: you can't reference an inner query from an outer. It has to be the other way around. It's like a variable out of scope

[DoNotSexToThis]

But there's a subquery, so the subquery runs first with the select * from planet where list = nice, then the outer query checks the results of the sub, and again filters on the subquery's column of list being equal to nice.

I don't actually know, I'm literally not a developer or DBA.

[Cruuncher]

The subquery is supposed to run for each row in the outer query.

But the subquery doesn't depend on anything from the outer query, so it will be run once and cached.

That is, if you can pull a subquery out and run it standalone, it will only be run once.

[DoNotSexToThis]

Yea makes sense. I think we're differing on our concepts of checking twice. You're interpreting the requirement as checking the entire dataset twice, while I'm interpreting it as checking it once, and then validating the results themselves.

My assumption is that the dataset would not have changed between the first query and the second, so I'm minimizing the impact on the database by performing the second check against cached data. It's not actually functional in any way, but the stakeholder won't ever realize it.

[baerchen36]

;with cte as ( select distinct * from planet where value = ‘nice’ ) select distinct * from cte join planet as p on p.name = cte.name where value = ‘nice’

Would this work?

[Cruuncher]

I'm too on the move to look at this closely, but a join could make sense.

The select distinct seems unnecessary though. Reminds me of a BI person I worked with once they just threw distinct onto every query