CODEWITHSUNDEEP
sqldatabase
boisvert
boisvert

Reputation: 3729

Why is the self join syntax so strange?

I repeat a student question here. I've given some sort of explanation, but hoping you have better, clearer ones.

A self-join is done with two aliases for the table:

SELECT parent.Name, child.Name
FROM Person parent
INNER JOIN Person child ON parent.Id = child.ParentId

But that just sounds like we're making things complicated. So:

(1) Are the aliases really needed? Why?

(2) It's possible, technically, to use just one alias. Why are all the examples, online, textbooks, tutorials, always using two aliases?

Upvotes: 0

Views: 219

Answers (1)

Corbin
Corbin

Reputation: 33447

1) Yes, you have to have at least one alias. Otherwise, how do you know which table-version is which?

2) You could just use one alias. Two are not strictly necessary. Using two aliases will almost always be clearer than one alias though.

Which is easier to understand at a glance?

SELECT parent.Name, child.Name
FROM Person parent
INNER JOIN Person child ON parent.Id = child.ParentId

Or

SELECT Person.Name, child.Name
FROM Person
INNER JOIN Person child ON Person.Id = child.ParentId

The first one shows intent a lot better.

By the way, you'd probably want to alias the columns as well. While two columns having the same name in a result is fine, it might be confusing, and if you tried to put result rows into some kind of associative container in the client code, it would break. So a better still version of the query, for example, would be:

SELECT parent.Name ParentName, child.Name ChildName
FROM Person parent
INNER JOIN Person child ON parent.Id = child.ParentId

Upvotes: 6

Related Questions