Natural Join in SQL Server

Natural join in SQL Server

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.

Natural Join Explanation

If I read your question correctly, you are trying to learn two concepts at the same time. The first is INNER JOIN (sometimes called equijoin). The second is natural join.

It's easier to explain natural join, assuming you already know how INNER JOIN works. A natural join is simply an equijoin where the column names indicate to you what the join condition ought to be. In your case, the fact that CustNo appears in both tables is the only clue you need in order to devise the correct join condition. You also include the join field only once in the result.

Column names are actually quite arbitrary, and could have been made very different in this case. for example, if the column Customer.CustNo had been named Customer.ID instead, you wouldn't be able to do a natural join.

for a correct solution in your case, see the answer provided by JamieC.

SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)

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.

I do use the USING join syntax occasionally, but just as often it turns out that I need a more complex join condition than USING can support, so I convert it to the equivalent ON syntax after all.

Natural full outer join does not work in my SQL Server Management Studio

There are no Natural JOINS in Sql server, You have to explicitly tell sql server on which fields you want table to join.

1) Using ON clause and explicitly using the column names from both tables.

2) Or in older JOIN Syntax explicitly using the column names from both tables in WHERE Clause.

for another nice answer on this topic Read here

sql natural join and cross join should give same output

The cross join can be seen also as a cartesian product: so every row of the first table is combined with a row on the second one, so the resulting number of rows will be rows(TableA) * rows(TableB).

To emulate a natural join with a cross join you'll need to compare the foreign/primary key of the different tables to see if they are the same like this:

SELECT PRODUCT_NAME, QUANTITY_ON_HAND, WAREHOUSE_NAME
FROM WAREHOUSES
    CROSS JOIN INVENTORIES
    CROSS JOIN PRODUCT_INFORMATION
WHERE WAREHOUSES.WAREHOUSES_ID = INVENTORIES.WAREHOUSE_ID AND
      INVENTORIES.PRODUCT_ID = PRODUCT_INFORMATION.PRODUCT_ID
ORDER BY PRODUCT_NAME;

If you need more information about cross-join or natural join.
If it can help you try to think at it like this:

• First you create a table with all the possible combination of rows
  between the three tables
• Than you start compare on each row the common and repetitive field, you've on each row 2 PRODUCT_ID and WAREHOUSE_ID columns, each of them coming from his specific table (try to execute the query taking all the columns with * to see this).
• After that you take only the rows where the 2 type of columns have the same value (Emulating a natural join)

You're just doing a normal join....with extra steps.

---

Related Topics