40

Is there any support for natural joins in recent Microsoft SQL Server editions? Or is there a good alternative for making SQL Server work out the predicates that would have been in the ON clauses based on the referential integrity?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
grenade
  • 31,451
  • 23
  • 97
  • 126
  • Natural joins are not based on referential integrity, they are based on common column names. No querying is based on integrity, it is based on (base & query result) table meanings--what a row says when in a table. One does not need to know constraints to query. If constraints hold then additonal expressions return the same results as the expressions one could have used & could still use without knowing constraints. Constraints are for the DBMS to enforce integrity. – philipxy Sep 13 '19 at 02:00
  • Note: `ON` clause is not hard to write. You can use autocomplete in IDEs like Rider / DataGrip – Evgeny Nozdrev Jun 10 '21 at 18:46

3 Answers3

45

No, and thank the lucky stars

I can't believe that you'd want the engine to guess the JOIN for you

Related links:

Edit, to explain why

  • The JOIN (whether USING or ON) is clear and explicit
  • I should be able to name my columns for the entity stored in the table, without worrying about what a column is called in another table, without NATURAL JOIN side effects

Quoting Bill Karwin in this excellent answer:

I never use NATURAL JOIN because I don't like the possibility that the join could do something I don't intend just because some column name exists in both tables.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 21
    It's only guessing if the implementation is done badly (column names). SQL Server has a precise way of defining references so it stands to reason that it has all the information it needs to create an accurate predicate. Or am I mistaken? – grenade Jan 28 '11 at 09:45
  • 10
    I agree with @mrwaim, OP asked if it was possible to do, not if it was correct. – shortstuffsushi May 03 '13 at 15:53
  • 48
    Promoting is fine, smug remarks as to why the OP is "doing it wrong" is just rude, and one of the biggest SO problems, really. What difference does it make what they user wants to do with it? They asked a question for a factual answer, not your opinion of their approach. There are other portions of SO for that. – shortstuffsushi May 04 '13 at 21:11
  • 15
    Downvoted for rudeness. A well designed database schema is suited for natural joins and if I'm not mistaken it's part of ANSI SQL 92. Not that any other vendor seems to care much about that standard, but that doesn't make it right not to support it. – Peter Becker Oct 13 '15 at 23:05
  • [Criticisms of natural join are ubiquitously unsoundly articulated.](https://stackoverflow.com/a/35034568/3404097) – philipxy Feb 14 '20 at 00:29
7

MS SQL does not support natural join, neither join using (). You have to explicitly write down all your attributes used in the join.

If the datamodel changes, you have to change all "natural join" written by hand and make sure your join condition is ok again.

Frits
  • 7,341
  • 10
  • 42
  • 60
Rémy Schumm
  • 151
  • 1
  • 11
5

I wouldn't expect to see it any time soon. A Connect suggestion from 2006 has very little info other than:

Thanks for your feedback. We will look into your request for one of the upcoming releases.

And has only received ~30 upvotes

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 4
    @gbn - natural join, in itself, isn't a bad idea. It doesn't "guess", or infer based on foreign keys. It simply matches on all identically named columns on both sides of the join. If you're joining between two tables that have identically named columns that represent different things, I'd generally see that as a broken schema; Besides, it's an opt in feature - it's not going to penalize or change the interpretation of any existing code. – Damien_The_Unbeliever Jan 30 '11 at 16:02
  • 5
    I was more thinking of "Insertedby" and "InsertedOn" type columns. Or where you don't prefix a Comment column with the table name so you have many Comment columns instead of the tautological Thing.ThingComment and Stuff.StuffComment – gbn Jan 30 '11 at 16:08
  • 4
    @Damien_The_Unbeliever - sorry to disagree but i see NATURAL JOIN as a terrible bad practice. You could be joinning columns that you are note seeing and you can have columns like IsActive, StatusID, Name, Description, etc on several tables witch does not mean a broken schema. And you could be doing an error without noticing. Its the same as SELECT *. A simple change to a data model and you are changing your procedures result sets without noticing. Adding a column with the same name to another table will automatically change your JOIN without noticing. – Tiago Guedes Nov 25 '14 at 15:21