Sql-server – ON clause of JOIN specific syntax

join;sql serversql-server-2005

I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just created as a subquery in the JOIN, for instance:

SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
FROM oldcontracts
JOIN (SELECT DISTINCT name, identifier FROM jobs)
ON oldcontracts.job = jobs.name
;

The thing here is, how would we go about setting an ON condition that would work? I say this because the syntax is of the following form:

table1.column <condition> table2.column

Where apparently, SQL 2005 is very strict about where we get those columns from. Meaning I get an error on jobs.name saying it is not a valid identifier. I assume SQL throws this error because jobs was not the table that is being joined in the operation.

I know that the way around this is to simply set jobs as the table2 to use in the JOIN, and adjust the SELECT as need be, then everything would work, but I'm curious to know if there's a way to address a column of a subquery, given that that specific subquery is the one being used as table2 in the JOIN operation?

Best Answer

Normally, you'd simply alias the subquery (which I thought was required in SQL Server)

SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
  FROM oldcontracts
       JOIN (SELECT DISTINCT name, identifier 
               FROM jobs) mysubquery
         ON oldcontracts.job = mysubquery.name

I would strongly suggest that you always alias any tables in your query and that you use those aliases for every column in the select list. That makes it much easier to see where the different data elements are coming from and makes it easier when you add additional joins in the future.

Related Question