Optimal SQLite Query Remove Duplicates

Question

I have two tables with the following setup:

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 from the category table of only used categories:

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

The above query works fine. I'm just wondering if this is the most optimal way of doing the query or if there's something else that I could do via a join or something

biziclop · Accepted Answer · 2012-08-12 23:09:05Z

5

Transforming the IN (SELECT) to EXISTS (SELECT ... WHERE ) might 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 it 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 could 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 that important, don't forget to call ANALYZE from time to time:

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

Some other variants 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

Another:

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

edited Aug 12, 2012 at 23:09

answered Aug 12, 2012 at 22:59

biziclop

14.6k3 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

My bet is on the query, just after "fun".

BizApps · Accepted Answer · 2012-08-12 22:58:32Z

0

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

answered Aug 12, 2012 at 22:58

BizApps

6,1409 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

Optimal SQLite Query Remove Duplicates

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related