Home Dashboard Directory Help
Search

NATURAL JOIN and USING clause in joins by Ole Kristian Bangaas


Status: 

Active


50
2
Sign in
to vote
Type: Suggestion
ID: 153679
Opened: 7/1/2006 10:26:01 AM
Access Restriction: Public
0
Workaround(s)
view

Description

I would like to see NATURAL JOIN and the USING clause of ISO/IEC 9075-2:2003 section 7.7 implemented in Microsoft SQL Server.
Details
Sign in to post a comment.
Posted by BillB1234 on 11/1/2013 at 1:25 PM
I too would vote for NATURAL JOIN. Anyone who did not want to use it could still type the INNER JOIN just as they do now.

It would be especially time saving in star schema data warehouse queries. The BI user would not have to remember the FK columns and have less typing.

As a developer, I would never use NATURAL JOIN, but it has its uses.
Posted by CDT652 on 7/10/2013 at 6:36 AM
Please consider me one of the many that would love for this to happen! I use this all the time in Oracle and it saves me a lot of typing! In fact, this is one of the few things I think Oracle does right, so please consider that.
Posted by tee_owe on 4/15/2013 at 7:06 AM
I really wish this Connect item was split. Support for the USING clause is a great idea; not only is it easier to read and more concise, it potentially saves a redundant column on the output.

But include me in the camp that thinks NATURAL JOINs are a bad idea. It is always better to declare JOIN criteria in a declarative language. The practical application should be rare by design and the risk of changing schema might return unexpected results.
Posted by Ken Evans on 1/26/2013 at 1:30 PM
Since natural joins are a feature of relational algebra, then I think that Microsoft should re-open the voting on this.
I would vote for the natural join to be included.
Posted by Steven Yampolsky on 12/12/2012 at 2:26 PM
The benefit of using natural joins is particularly great when joining fact tables with many dimensions. For example, I have a fact table and a fact attribute table(I'm skipping definitions of dimension tables since they are not needed for demonstration):

CREATE TABLE FactCar
(
CarTypeKey int FOREIGN KEY DimCarType(CarTypeKey),
ManufacturerKey int FOREIGN KEY DimManufacturer(ManufacturerKey),
ProductionPlantKey int FOREIGN KEY DimProductionPlant(ProductionPlantKey),
NumberOfDoort int not null,
EngineDisplacement decimal(6,2) not null
)
CREATE TABLE FactCarAttributes
(
CarTypeKey int FOREIGN KEY DimCarType(CarTypeKey),
ManufacturerKey int FOREIGN KEY DimManufacturer(ManufacturerKey),
ProductionPlantKey int FOREIGN KEY DimProductionPlant(ProductionPlantKey),
ColorKey int FOREIGN KEY DimColors(ColorKey),
MakeName nvarchar(50) not null,
SafetyRating nvarchar(50) not null
)

Without Natural Join, the select statement would look like this:

SELECT *
FROM
FactCar
     INNER JOIN
FactCarAttributes
     ON
     FactCar.CarTypeKey = FactCarAttributes.CarTypeKey AND
     FactCar.ManufacturerKey = FactCarAttributes.ManufacturerKey AND
     FactCar.ProductionPlantKey = FactCarAttributes.ProductionPlantKey

Now, this example only has three common dimensions. Lot's of text to type and mistakes to make. With Natural Join, things become much easier:

SELECT *
FROM
FactCar
     NATURAL JOIN
FactCarAttributes
     USING(CarTypeKey,ManufacturerKey,ProductionPlantKey)

The code looks clean, there are fewer places to make a mistake and therefore, better productivity.
Posted by Tom Hunter on 11/30/2012 at 2:53 AM
Why is voting no longer enabled? I would like to vote for this..
Posted by Microsoft on 5/15/2012 at 10:23 AM
Hello,
Just a quick note, this item has been kept open based on more feedback. We will look at it for a future version of SQL Server.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 3/10/2011 at 2:59 PM
Hello,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen it and we will take another look.

--
Umachandar, SQL Programmability Team
Posted by Rob Thijssen on 1/28/2011 at 7:32 AM
I guess it missed 2008. Any plans on including this in the next release?
Posted by Microsoft on 8/24/2006 at 2:31 PM
Thanks for your feedback. We will look into your request for one of the upcoming releases.

Best regards
Michael
Sign in to post a workaround.