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.
精彩评论