Possible improvement on join query

Question

I have the following query which retrieve all those who have ordered a book_id n°10 and but never ordered the book with id n°1. http://sqlfiddle.com/#!9/1bb9e8/44

|user_id | book_id |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 10|
| 2 | 9 |
| 3 | 5 |
| 3 | 10|
| 4 | 6 |
| 4 | 7 |
| 4 | 10|
| 8 | 8 |

select u.user_id,o.book_id
from users u
inner join c
  on u.user_id = o.user_id 
    and  o.book_id = 10 
 where u.user_id not in (
 select 02.user_id
   from orders o2
  where o2.book_id = 1 );

The keyword "except" is not available on MariaDB 10.1 What are the others way can this be written in SQL aside using a CTE that would be efficient ?

Answer 1

By improvement, I suppose you have performance in mind because your queries are fairly basic and not hard to understand.

I have had a look at your SQL fiddle and your tables have no indexes. Absent any other optimization mechanism the database engine will have to perform a full table scan to fetch the wanted rows. Thus, I don't expect much difference between a join or a subselect, or CTE. On a large table, the bottleneck is likely going to be row filtering, on a small table the performance hit should be negligible.

First, consider adding indexes on your tables, for example the user ID field could be a primary key. Then use the explain command, preferably on a larger dataset for more representative results. For this you could write a script to generate a large bunch of random records.