Since User Link is fully explained in this answer, I'll explain about Post Link below.
This is a feature that exists in SEDE from the very beginning, mentioned in the original announcement by Sam who created this project:
If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site.
As for the technical part, we can see for ourselves in the source code since SEDE is open source. The code responsible for this resides in class called QueryRunner. Here is how it works:
When the magic column Post Link exists, a query is executed to select the Title of all the questions that are "taking part" in the query using the SQL "In" clause. For example, suppose the original query returns values of 100, 500, and 2000 for the column marked Post Link:
select p1.Id, isnull(p1.Title,p2.Title) from Posts p1
left join Posts p2 on p1.ParentId = p2.Id where p1.Id in (100, 500, 2000)
- The titles are being read and saved into a list in the code, aka mapping.
- Before displaying, a loop is going over all rows in the recordset (reader), changing the value of magic columns to the value stored in the above mapping.
Note that using such magic columns might cause queries which returns large amount of rows to become very heavy and freeze, as reported here: Out-of-resources when trying to run a popular DataExplorer query