Search code examples
sql-servert-sqldynamics-crm-4

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

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 };
Solution

  • 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..