Translating a QueryExpression into SQL: what's a Natural join?

Question

I am trying to translate a QueryExpression that is in some existing code into a T-SQL select statement.

I've run across the following statement and I'm having trouble understanding what they mean by a Natural Join:

linkEntity1.JoinOperator = JoinOperator.Natural;

Would this be equivalent to an Inner Join in T-SQL? Googling has not been much help.

Here's the rest of the QueryExpression Code:

QueryExpression query = new QueryExpression();

query.EntityName = "showinfo";

ColumnSet columns = new ColumnSet();
columns.Attributes = new String[] { "company" };
query.ColumnSet = columns;

query.Criteria = new FilterExpression();
query.Criteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "company";
condition1.Operator = ConditionOperator.NotNull;
query.Criteria.Conditions = new ConditionExpression[] { condition1 };

LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Natural;
linkEntity1.LinkFromEntityName = "show";
linkEntity1.LinkFromAttributeName = "showid";
linkEntity1.LinkToEntityName = "showintegration";
linkEntity1.LinkToAttributeName = "showcode";

linkEntity1.LinkCriteria = new FilterExpression();
linkEntity1.LinkCriteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition2 = new ConditionExpression();
condition2.AttributeName = "showend";
condition2.Operator = ConditionOperator.Null;

linkEntity1.LinkCriteria.Conditions = new ConditionExpression[] { condition2 };
query.LinkEntities = new LinkEntity[] { linkEntity1 };

Have a look at http://en.wikipedia.org/wiki/Join_(SQL)#Natural_join — Ray, Sep 27 '11 at 16:40
I did actually look at that, that makes sense. But the context I have this in doesn't. The CRM object linkEntity1 gives explicit columns for the join, that's why I'm confused. I will edit my question to add a bit more of the code. — Moose, Sep 27 '11 at 16:54

score 3 · Accepted Answer · edited May 23 '17 at 12:03

3

There is no equivalent in SQL Server of a natural join where table intersect is based on column names by the RDBMS.

I'm glad of that because it is at best ambiguous and at worst dangerous. JOINs should be explicit. Examples why:

having a InsertedBy column in both tables (quite common): should we have to prefix with the table name to remove ambiguity?
future DDL that add columns that change JOIN semantics

See

Edit:

It looks like natural join means "don't repeat the column in the output" (like USING in MySQL would do) according to the JoinOperator Enumeration.

If I understand this (debatable!) it's misleading. Especially when I read the "LeftOuter" narrative..

edited May 23 '17 at 12:03

Community

1
1

answered Sep 27 '11 at 17:00

gbn

422,506
82
585
676

+1 because this explains why some of the OP's original query use natural joins and some use explicit joins; if tables have columns of the same name which you do not want to join on, e.g. `InsertedBy`, you must not use a natural join. – Dour High Arch Sep 27 '11 at 17:07
I'm really after the meaning of Natural join as CRM 4.0 interprets it. I am currently attempting to actually run the code to see what it returns, and compare it to my query. – Moose Sep 27 '11 at 17:21
@Moose: my update mentions that. Given SQL Server doesn't have natural join I'd be interested how it works. Can you try with an "InsertedBy" column (or something) please too as a test – gbn Sep 27 '11 at 17:25
1

I hate it when developers (in this case Microsoft developers who definitely should know better) use terms that mean something specific in a database context and change the meanings for this one case. That left join one really bothered me. I wonder how much bad code is out there based on people knowing what left join means and not looking up what it means to CRM? – HLGEM Sep 27 '11 at 17:36
Well, I've run the test, and it appears that, at least in this code's case, that JoinOperator.Inner and JoinOperator.Natural are identical. They return identical rows with identical fields. This is why I don't use this query syntax. @HLGEM, I agree. Everything about the JoinOperator enumeration is messed up. – Moose Sep 27 '11 at 17:49
1

The T-SQL I wrote using INNER JOIN returns the same rows, that's all I'm worried about. I'm going to accept this one because I don't have the time to pursue it further to really determine what CRM means by Natural, and you did more work for it and got closer than the other answers. – Moose Sep 27 '11 at 17:53

score 1 · Answer 2 · answered Sep 27 '11 at 16:36

1

A natural join compares all columns in the two tables that have the same column names. It's equivalent to an inner join with the matching columns explicitly listed.

answered Sep 27 '11 at 16:36

Joe Stefanelli

132,803
19
237
235

score 0 · Answer 3 · answered Sep 27 '11 at 16:34

0

Yes - the natural join is inner join - so you can write:

select * from tab1, tab2 where tab1.col1 = tab2.col1

as

select * from tab1 inner join tab2 on tab1.col1 = tab2.col1

answered Sep 27 '11 at 16:34

Michal Barcik

662
4
6

AFAIK A natural join only returns the joining column(s) once so it isn't quite the same as you have it. – Martin Smith Sep 27 '11 at 16:49
This is implicit (older, bad) vs explicit JOIN (since ANSI 92 SQL, good) format. See these http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338 – gbn Sep 27 '11 at 17:01

Translating a QueryExpression into SQL: what's a Natural join?

3 Answers3