Questions about subquery in WHERE EXISTS(...)

Question

I'm confused about this example in this tutorial page. http://www.postgresqltutorial.com/postgresql-subquery/

SELECT
    first_name,
    last_name
FROM
    customer
WHERE
    EXISTS (
        SELECT
            1
        FROM
            payment
        WHERE
            payment.customer_id = payment.customer_id
    );

Could you please tell me the point of the subquery?

I understand that EXISTS converts the result set to a boolean "true" if there's at least one result returned from the subquery. But in the WHERE clause of the sub query, it would seem like it would always be "true", so a row will always be selected, so the EXISTS will always be "true".

Was that example meant to do this in the subquery?

WHERE
    customer.customer_id = payment.customer_id

Also, I assume that a subquery that is part of the WHERE will run once for every "customer". Is that right?

Thanks.

you will find somthing here http://stackoverflow.com/questions/14190788/subqueries-with-exists-vs-in-mysql — Manwal, Oct 24 '13 at 03:29
Thanks, but I don't understand how that is related to what I'm asking. — Blue Skies, Oct 24 '13 at 03:36
There is indeed a nasty typo in that example. WRT the last question: Yes and No. The query can be considered to run **as if** a boolean probe is executed for every row of the outer table. But **in practice** a very ingeneous query plan is generated, basically a join reduced to a boolean outcome plus the outer tuple. Check the query plan and you will see. — joop, Oct 24 '13 at 12:47
@joop: Thank you, that's great info to have. The main motivation for the question was to ensure that I could think of the subquery as running against each individual `customer.customer_id`. Good to know that things are optimized. I'll take a look at the query plan as you suggest. — Blue Skies, Oct 24 '13 at 15:34
Well, things are only optimised if the join field (or fields) is a PK or FK or if an index is available. Which is normally the case. — joop, Oct 24 '13 at 16:59

score 4 · Accepted Answer · answered Oct 24 '13 at 03:34

4

You are absolutely right. That seems to be a typo in the PostgreSQL documentation... and quite a confusing one, by the way.

Regarding the last question, thinking of it running for each customer is a good approach too.

answered Oct 24 '13 at 03:34

Mosty Mostacho

42,742
16
96
123

1

Alright, thanks. I stared at it for a while and thought something must be wrong with the tutorial. I think this is from a 3rd party site, so I don't think it's actually the official docs. And thanks for clearing up the last question too. – Blue Skies Oct 24 '13 at 03:40

Questions about subquery in WHERE EXISTS(...)

1 Answers1