So I'm trying to query the stack overflow database for a question and its answers. So far I have come across two ways to do this:
SELECT questions.Id as [Post Link], questions.title, answers.body, questions.viewcount
FROM Posts answers
INNER JOIN Posts questions ON answers.parentid = questions.id
and the second way is this
SELECT * # Replace the actual fields
FROM posts
WHERE (Id = {POST_ID}) OR (ParentId = {POST_ID})
ORDER BY PostTypeId ASC, Score DESC
which approach is better and why ? is there a different way to do this ? and is there a term in sql for this parent child relationship. Any topics I could study up on how to design efficient queries on this?
If two results are just as good for you it all comes down to performance.
In terms of performance there's couple of things you could study, as indexes for example, and how they are used be the SQL engine.
So, in terms of performance, second query is likely to be better, because there you can query just one table instead of two (quite obvious).
Also you have
WHERE
clause (andON
in first query) which greatly depend on indexes.Since
Id
columns are very often idexed, second query seems to be very efficient.