Home Dashboard Directory Help
Search

Proposed improvement in T-SQL JOIN syntax by Steinar Andersen - SQL Service


Status: 

Closed
 as Duplicate Help for as Duplicate


1
0
Sign in
to vote
Type: Suggestion
ID: 773543
Opened: 12/5/2012 6:16:39 AM
Access Restriction: Public
0
Workaround(s)
view

Description


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)


-- 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
Details
Sign in to post a comment.
Posted by Microsoft on 1/3/2013 at 11:18 AM
Hello Steinar,
Thanks for your feedback. Your feature request is already covered in the item below:

http://connect.microsoft.com/SQLServer/feedback/details/153679/natural-join-and-using-clause-in-joins

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.