3

I am given the schema:

BOOKING (hotelNo, guestNo, dateFrom, dateTo)

GUEST (guestNo, guestName, guestAddress)

I an asked to formulate the query for: List the guest numbers associated with the first name ‘Peter’ having made bookings with unknown dateTo without using an explicit or implicit joins.

My attempted query is:

  (select guestNo from GUEST where guestName LIKE 'Peter')
  INTERSECT ALL
  (SELECT guestNo FROM BOOKING WHERE dateTo IS NULL);

But this does not return duplicates only at most 1 entry per guestNo, as shown in fiddle where returned values should be (1,1) rather than (1). I thought about using UNION but then this will return guestNo's if dateTo is NULL or name is Peter, i.e. both conditions may not necessarily hold.

Database fiddle: https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17

Do you guys have any suggestions?

7
  • 1
    Have you thought about using a subquery instead of a join?
    – J. Maes
    Apr 11, 2021 at 16:57
  • What @J.Maes said. In addition, when I read "having made bookings", I think exists. Apr 11, 2021 at 17:03
  • Ohhh so something like : "SELECT guestNo from booking B where dateTo is NULL AND EXISTS (select guestNo from guest G where guestName='Peter' AND B.guestNo = G.guestNo);"
    – pk00
    Apr 11, 2021 at 17:05
  • I wouldn't do it in that order: you usually want to query the smaller table and probe the larger table for the existence of records. There are probably more open-ended bookings than there are guests with first name Peter. Apr 11, 2021 at 17:10
  • I tried running the query the way you said but that returned (1) rather than expected (1,1). Think this is because Guest uses GuestNo as primary key so it only checks each guestNo once.
    – pk00
    Apr 11, 2021 at 17:38

2 Answers 2

4

In an almost literal translation from English to SQL, I would write the query as follows:

select guestNo
from guest
where guestName like 'Peter%'
and exists (
    select
    from booking
    where guestNo = guest.guestNo
    and dateTo is null
);

NB Postgres unlike some other DBMSes doesn't need to have any columns in the select clause! That's ideal in the subselect here as we're only interested in the existence of a record, not in its value).

See https://www.db-fiddle.com/f/tpzgVMwkQGAHBFxyMkyJvj/17

2
  • 1
    I've been using Postgres since '90s and still I learn new things. That's why I love Pg <3 Many people write ... EXISTS (SELECT 1 FROM ... or ... SELECT true FROM... +1 for this Apr 11, 2021 at 17:54
  • I agree, as someone who's not super well versed with PostgreSQL, that's a cool tidbit about not needing to actually specify a column list in the SELECT clause. Nice, +1.
    – J.D.
    Apr 11, 2021 at 19:38
0

All these queries will return 1, 1 and optimize to the same plan (for your schema and data):

  1. SELECT  guestNo
    FROM    booking
    WHERE   guestNo = ANY
            (
            SELECT  g.guestNo
            FROM    guest AS g
            WHERE   guestName = 'Peter'
            )
            AND dateTo IS NULL
    
  2. SELECT  guestNo
    FROM    booking
    WHERE   guestNo IN
            (
            SELECT  g.guestNo
            FROM    guest AS g
            WHERE   guestName = 'Peter'
            )
            AND dateTo IS NULL
    
  3. SELECT  guestNo
    FROM    booking b
    WHERE   EXISTS
            (
            SELECT  g.guestNo
            FROM    guest AS g
            WHERE   guestName = 'Peter'
                    AND g.guestNo = b.guestNo
            )
            AND dateTo IS NULL
    

Because guest.guestNo is the primary key, this is the same result and the same plan a query with the join would optimize to:

SELECT  guestNo
FROM    booking
JOIN    guest
USING   (guestNo)
WHERE   guestName = 'Peter'
        AND dateTo IS NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.