Working with complex queries (those that have more than a handful of joins), one of the things that is difficult for novice developers is to keep track of where they are and which objects have already been referenced on the query.
One of the developers just walked up to me the other day with the following error (formatted for the sake of brevity):
Msg 1013, Level 16, State 1, Line 3 The objects "Person.Person" and "Person.Person" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
Now, I did not recollect if I had ever seen the error before, so my first response was to take a look at the query. As soon as I looked at the query, I immediately realized the problem – the query had a self join and no object aliases were used to distinguish between the two instances of the same object!
USE AdventureWorks2014; GO SELECT * FROM Person.Person INNER JOIN Person.Person ON BusinesEntityID = BusinessEntityID; GO
Once the objects in the query were given aliases, the error was resolved.
However, this incident enforces what I have always practiced and believed to be a best practice – always ensure that object names, queries and other literals used in a query have proper aliases.
Using proper aliases ensures that as a developer, reviewer or as a support engineer, we always know exactly which instance of the object is being referred.
Until we meet next time,
Be courteous. Drive responsibly.