Normally you would write a join like this:
USE AdventureWorks2012;
GO
SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
What I propose is a simple improvement in syntax, that makes use of the informationa already contained in the datamodel, namely the foreign key relationship between the two tables. The syntax cold then look like the following:
-- Full syntax
USE AdventureWorks2012;
GO
SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON REFERENCE
-- Short Syntax
USE AdventureWorks2012;
GO
SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON REF
The ideas would be that this would save time writing queries, and be more safe in case of a FK/PK column rename, or Foreign Key redefinition, as the query would still be valid, and not need to be updated, if the datamodel changes in this way.
SQL Server should look up the actual definition of the FK relationship at compiletime, to obtain exact information of wich columns to use for the join. This would only work for joins of EQUAL (=) type, not for NOT EQUAL (<>), LARGER THAN (>), SMALLER THAN (<)and so on.