How should INNER JOIN query look

join;

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.

Best Answer

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
    ) ;
Related Question