Proposed improvement in T-SQL JOIN syntax

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

I have a proposal to enhance the T-SQL JOIN syntax, to simplify writing joins that use an existing PK/FK relationship.

The examples in this post uses the Adventureworks2012 database, and the [HumanResources].[Employee] and [Person].[Person] (Some parts edited out of CREATE scripts for readability)

OBS: This is regged as a CONNECT item, and if you think this is a good idea, you can vote for it here:

https://connect.microsoft.com/SQLServer/feedback/details/773543/proposed-improvement-in-t-sql-join-syntax

— Start Setup script

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [HumanResources].[Employee]

(  [BusinessEntityID] [int] NOT NULL,  

[NationalIDNumber] [nvarchar](15) NOT NULL,  

[LoginID] [nvarchar](256) NOT NULL,  

[OrganizationNode] [hierarchyid] NULL,  

[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),  

[JobTitle] [nvarchar](50) NOT NULL,  

[BirthDate] [date] NOT NULL,  

[MaritalStatus] [nchar](1) NOT NULL,  

[Gender] [nchar](1) NOT NULL,  

[HireDate] [date] NOT NULL,  

[SalariedFlag] [dbo].[Flag] NOT NULL,

 [VacationHours] [smallint] NOT NULL,  

[SickLeaveHours] [smallint] NOT NULL,  

[CurrentFlag] [dbo].[Flag] NOT NULL,  

[rowguid] [uniqueidentifier] NOT NULL,  

[ModifiedDate] [datetime] NOT NULL,  

CONSTRAINT [PK_Employee_BusinessEntityID]

PRIMARY KEY CLUSTERED (  [BusinessEntityID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) )

 

ALTER TABLE [HumanResources].[Employee] 

WITH CHECK ADD  CONSTRAINT [FK_Employee_Person_BusinessEntityID] FOREIGN KEY([BusinessEntityID])

REFERENCES [Person].[Person] ([BusinessEntityID])

GO

ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Person_BusinessEntityID]

GO

 

CREATE TABLE [Person].[Person]

(  [BusinessEntityID] [int] NOT NULL,  

[PersonType] [nchar](2) NOT NULL,  

[NameStyle] [dbo].[NameStyle] NOT NULL,  

[Title] [nvarchar](8) NULL,  

[FirstName] [dbo].[Name] NOT NULL,  

[MiddleName] [dbo].[Name] NULL,  

[LastName] [dbo].[Name] NOT NULL,  

[Suffix] [nvarchar](10) NULL,  

[EmailPromotion] [int] NOT NULL,  

[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,  

[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,  

[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,  

[ModifiedDate] [datetime] NOT NULL,  

CONSTRAINT [PK_Person_BusinessEntityID]

PRIMARY KEY CLUSTERED (  [BusinessEntityID] ASC )

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

— End Setup Script

 

Now, 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 idea 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.