Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Natural join in SQL Server

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?

like image 400
grenade Avatar asked Jan 28 '11 09:01

grenade


People also ask

Does Natural join exist in SQL Server?

Note. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result that you are expecting. The natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

What is natural join in SQL with example?

Natural join is an SQL join operation that creates join on the base of the common columns in the tables. To perform natural join there must be one common attribute(Column) between two tables. Natural join will retrieve from multiple relations.

What is the difference between a join and a natural join?

The primary difference between an inner and natural join is that inner joins have an explicit join condition, whereas the natural join's conditions are formed by matching all pairs of columns in the tables that have the same name and compatible data types, making natural joins equi-joins because join condition are ...

What is the difference between natural and outer join?

A full outer join preserves unmatched rows from both tables. That is, a full outer join returns all matching and non-matching rows from the left and right table. This natural join example returns rows that have matching values for the column Prodid.


2 Answers

No, and thank the lucky stars

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

Related links:

  • SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)
  • is NATURAL JOIN any better than SELECT FROM WHERE in terms of performance ?

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.

like image 164
gbn Avatar answered Sep 22 '22 04:09

gbn


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.

like image 29
Rémy Schumm Avatar answered Sep 20 '22 04:09

Rémy Schumm