Det kan ibland upplevas svårt att skriva en update med join. Ett sätt att göra…
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:
— 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] (CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
[Demographics] (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.