2

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?

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
j.doe
  • 305
  • 3
  • 16
  • If the questions and answers are on the same table, I'd avoid doing a join and use your 2nd approach, as long as there are indexes on Id and ParentId. – EzLo Nov 14 '19 at 07:23

1 Answers1

1

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 (and ON in first query) which greatly depend on indexes.

Since Id columns are very often idexed, second query seems to be very efficient.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69