CODEWITHSUNDEEP
sqlpostgresql
Automatic Bazooty
Automatic Bazooty

Reputation: 553

SQL Alternative to using WHERE as a subquery

I'm learning SQL using PostgreSQL, and I'm trying to modify one of the queries from the PostgreSQL Tutorial website.

The original query is this:

SELECT film_id, title
FROM film
WHERE film_id IN (
    SELECT inventory.film_id
    FROM rental
    INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
    WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30'
);

This works, however I want to include rental.return_date in my output. I was able to achieve this using the following modifications, but it is terribly slow (takes 46096ms instead of 40ms):

SELECT film_id, title, return_date
FROM film, rental
WHERE film_id IN (
   SELECT inventory.film_id
   FROM rental
   INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
   WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30'
)
ORDER BY return_date;

I poked around this site and I suspect that I need to change the WHERE clause to an INNER JOIN, but my attempts have all failed thus far. Is there a simple way to speed up this query?

Upvotes: 3

Views: 1319

Answers (2)

TomDobbs
TomDobbs

Reputation: 941

Just to make it even smaller...

SELECT f.film_id, f.title, r.return_date
FROM film f
    INNER JOIN inventory i USING (film_id)
    INNER JOIN rental r USING (inventory_id)
WHERE r.return_date BETWEEN '2005-05-29' AND '2005-05-30'
ORDER BY r.return_date

Upvotes: 0

nhgrif
nhgrif

Reputation: 62052

You shouldn't need a subquery at all.

Just join the tables in what appears the only logical way to join the tables at all, and then borrow the where clause from the previous subquery to use as the where clause for your query.

SELECT film.film_id, film.title, rental.return_date
FROM film
    INNER JOIN inventory ON inventory.film_id = film.film_id
    INNER JOIN rental ON rental.inventory_id = inventory.inventory_id
WHERE rental.return_date BETWEEN '2005-05-29' AND '2005-05-30'
ORDER BY rental.return_date

Upvotes: 5

Related Questions