开发者

SQL efficiently check if an association exists?

I have a database of notes and lists, with three association tables. Basically, lists are parents of notes and other lists, and notes can be parents of other notes in a outline like hierarchy.

I am using this query to return all notes within a list as well as the number of subnotes each note has.

SELECT count(n2.note_id) as Num_Subnotes, _id, title, details
FROM NOTES
JOIN NOTES开发者_开发知识库_IN_LISTS n1 ON NOTES._id=n1.note_id
LEFT JOIN NOTES_IN_NOTES n2 ON NOTES._id=n2.parent_note_id
WHERE n1.listId=12
GROUP BY NOTES._id
COLLATE NOCASE

This query works just fine, but it's overkill. I don't need to return count(n2.note_id) as Num_Subnotes because I'm only using that value to check if the note has ANY SUBNOTES AT ALL. Essentially it's a boolean value where 0 is false and > 0 is true.

It seems to me that counting all those records is a waste of time when I could just return 1 after finding the first matching value.

Is there a more efficient way to check if count(n2.note_id)>0 in the above query?


SELECT EXISTS (SELECT 1 FROM NOTES_IN_NOTES n2 WHERE n2.parent_note_id = n0._id) as Has_Subnotes, n0._id, n0.title, n0.details
FROM NOTES n0
INNER JOIN NOTES_IN_LISTS n1 ON n0._id = n1.note_id
WHERE n1.listId = 12
COLLATE NOCASE

It's been a while since I've used sqlite, but the key is to make use of EXISTS if it's supported, along with a subquery. If it doesn't work that way, you might have to use COALESCE in the Has_Subnotes column instead and do a LEFT JOIN to the NOTES_IN_NOTES table, but you wouldn't need to use the GROUP BY in that case.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜