1

I am trying to fetch some records using INNER JOIN. The problem is that I am not sure what's the best (recommended) way of doing it.

What "pros" and "cons" have the queries shown below? All of them work correctly and do the same thing.

#This query seems to perform faster than the
#following one but is it ok to select all the columns?
#Needed is only one column (`id`) 

SELECT book_name, book_type FROM books b INNER JOIN (
    SELECT * FROM authors WHERE user_id = ? AND male = ?
) a ON b.author_id = a.id;
SELECT book_name, book_type FROM books b INNER JOIN (
    SELECT id FROM authors WHERE user_id = ? AND male = ?
) a ON b.author_id = a.id;
SELECT book_name, book_type FROM books b INNER JOIN authors a
ON b.author_id = a.id WHERE a.user_id = ? AND a.male = ?;

I am also not sure about the functionality and performance. For example, in the first case, the subquery selects some records (there may be let's say five of them), then joins with the table to return the result set. In the last example (I suppose) the full tables will join (a lot more records) and then the records are filtered.

1

The actual execution plans produced by a DBMS optimizer does not need to follow exactly the SQL code. It only has to produce the same results as if it followed the code.

The optimizer is free to do any optimizations and is usually better at it when the code is simpler. Some optimizers (for example MYSQL before 5.6) would create a better execution plan with the 3rd query. Others (like PostgreSQL, Oracle, SQL Server) would produce almost identical or identical plans for all 3 queries, as the queries are semantically equivalent and simple enough for the optimizer to understand this.

The 3rd query is more simple (no derived tables) and I would personally prefer it for clarity (and for MySQL for efficiency, too).

Also note that there more ways to write the query, also equivalent, using IN or EXISTS (this assumes that author (id) is the primary key and you want only columns from books in the select list):

SELECT b.book_name, b.book_type 
FROM books b 
WHERE EXISTS
    ( SELECT *
      FROM authors a
      WHERE a.user_id = ? 
        AND a.male = ?
        AND b.author_id = a.id
    ) ;
2

I would opt for the third option purely for readability and simplicity. Have you looked at the execution plans for all three, I expect they may give the same result for each. I would have expected the bracket query with the * would have been slower.

  • I've expected that too. I measured speed using the profiler (SHOW PROFILES) and * seems to be faster. – 0101 Apr 15 '16 at 13:20
0

Third option is preferable, however with braces like: SELECT book_name, book_type FROM books b INNER JOIN authors a ON (b.author_id = a.id) WHERE a.user_id = ? AND a.male = ?;

  • The parentheses are not needed - although they don't hurt. – ypercubeᵀᴹ Apr 15 '16 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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