Optimal SQLite query Remove duplicates

Question

I have two tables with the following setting:

category: (id, name) item: (id, name, category_id) - category_id is foreign key to category table 

Now I am writing a query to retrieve a subset of the categories table of only the categories used:

 SELECT c.id, c.name FROM category c WHERE c.id IN (SELECT DISTINCT category_id FROM item) 

The above query is working fine. I'm just wondering if this is the most optimal way to execute a request or if there is something else that I could do through the connection or something

Answer 1

Converting IN (SELECT) to EXISTS (SELECT ... WHERE ) can help:

 SELECT c.id, c.name FROM category c WHERE EXISTS (SELECT 1 FROM item WHERE item.category_id = c.id) 

Another possibility (I expect it to be slower, but always depends on your db):

 SELECT c.id, c.name FROM category c INNER JOIN item ON item.category_id = c.id GROUP BY c.id 

Or you can use DISTINCT instead of GROUP BY :

 SELECT DISTINCT c.id, c.name FROM category c INNER JOIN item ON item.category_id = c.id 

And if speed is important, remember to call ANALYZE from time to time:

http://www.sqlite.org/lang_analyze.html

Some other options for fun:

 SELECT c.id, c.name FROM category c INNER JOIN (SELECT DISTINCT item.category_id ) AS i_c ON i_c.category_id = c.id 

Other:

 SELECT c.id, c.name FROM category c EXCEPT SELECT c.id, c.name FROM category c LEFT JOIN item ON item.category_id = c.id WHERE item.category_id IS NULL 

Answer 2

Use Join:

 SELECT c.id, c.name FROM category c JOIN item i on c.id=i.category_id GROUP BY c.id, c.name