SQL - my syntax works with a subquery but it seems like there should be a cleaner way?-CodePudding

I am trying to select rows from one table where the id referenced in those rows matches the unique id from another table that relates to it like so:

SELECT *
FROM booklet_tickets
WHERE bookletId = (SELECT id
                   FROM booklets
                   WHERE bookletNum = 2000
                     AND seasonId = 9
                     AND bookletTypeId = 3)

With the bookletNum/seasonId/bookletTypeId being filled in by a user form and inserted into the query.

This works and returns what I want but seems messy. Is a join better to use in this type of scenario?

CodePudding user response：

If there is even a possibility for your subquery to return multiple value you should use in instead:

SELECT *
FROM booklet_tickets
WHERE bookletId in (SELECT id
                    FROM booklets
                    WHERE bookletNum = 2000
                      AND seasonId = 9
                      AND bookletTypeId = 3)

But I would prefer exists over in :

SELECT *
FROM booklet_tickets bt
WHERE EXISTS (SELECT 1 
              FROM booklets b
              WHERE bookletNum = 2000
                AND seasonId = 9
                AND bookletTypeId = 3 
                AND b.id = bt.bookletId)

CodePudding user response：

It is not possible to give a "Yes it's better" or "no it's not" answer for this type of scenario.

My personal rule of thumb if number of rows in a table is less than 1 million, I do not care optimising "SELECT WHERE IN" types of queries as SQL Server Query Optimizer is smart enough to pick an appropriate plan for the query.

In reality however you often need more values from a joined table in the final resultset so a JOIN with a filter WHERE clause might make more sense, such as:

SELECT BT.*, B.SeasonId
FROM booklet_tickes BT 
INNER JOIN booklets B ON BT.bookletId = B.id
WHERE B.bookletNum = 2000
    AND B.seasonId = 9
    AND B.bookletTypeId = 3

To me it comes down to a question of style rather than anything else, write your code so that it'll be easier for you to understand it months later. So pick a certain style and then stick to it :)

The question however is old as the time itself :)

SQL JOIN vs IN performance?