SQL on Stack Exchange Data Explorer - defining values

Question

I'm new to Stack Overflow and am a very novice coder. I'm using this tutorial to learn how to write queries to search the Stack Exchange Data Explorer.

I'm looking at this line of code:

SELECT p.Title, p.Id, p.Score, a.Score AS "Accepted Score", 
u.DisplayName AS "Asker", au.DisplayName AS "Answerer"

FROM Posts p
JOIN Posts a ON p.AcceptedAnswerId = a.Id
JOIN Users u ON p.OwnerUserId = u.Id
JOIN Users au ON a.OwnerUserId = au.Id
WHERE p.PostTypeId = 1
AND p.Score >= 25
and p.AcceptedAnswerId IS NOT NULL
ORDER BY p.Score DESC

... and I want to make sure I understand it. The part where I'm a little stuck is:

JOIN Posts a ON p.AcceptedAnswerId = a.Id
JOIN Users u ON p.OwnerUserId = u.Id
JOIN Users au ON a.OwnerUserId = au.Id

Am I correct that (1) we're essentially defining "a", "u", and "au", and (2) "a" represents all user Ids of posts that have an an accepted answer (3) "u" represents user Ids that appear in both posts and user profiles (4) and "au" represents the cross section of answer posts and users?

I guess I'm confused why you need to define "u" here. Is it so that the results will return a hyperlink to the user's actual profile, rather than just giving a number?

Answer 1

'a' represents the post, by joining that to post represented as 'p' along with the AcceptedAnswerId your dataset is going to get filtered with only the AcceptedAnswers. This is also known as self join here 'p' contains the user informtion who has asked the question and 'a' contains the information of the answerer, when 'u' the alias for the table contains user info is joined with the ownerid of 'p' so that it returns the user info of the asker, similarly again the user table is used as 'au' to retrive the answerer information.