Not to used natural join

Question

I was debate with my TL over natural join, he told natural join should not be used, so on what cases we are not going with natural join, equjoin and go with inner join.

Answer 1

Please rephrase your question like What are the implications between a Natural Join and Inner JOIN? 'What are the limitations of these JOINS in SQL Server 2008?

SQL Server gets rid of Natural Joins, so there also is a scalable aspect to their usage. Without getting too specific, a NATURAL JOIN is essentially like an INNER JOIN except it

• A) returns distinct columns (think INTERSECT/UNION except tables can have differing columns)
• B) add implicitly an EQUI JOIN on all of the available columns.

Illustration: Note, this was designed in SQL Server 2012

  DECLARE @TableA TABLE (Col1 VARCHAR(10)
                       , Col2 VARCHAR(10) );
  DECLARE @TableB TABLE (Col1 VARCHAR(10)
                       , Col2 VARCHAR(10)
                       , Col3 VARCHAR(10) );
  INSERT INTO @TableA (Col1, Col2)
  VALUES ('C', 'D');
  INSERT INTO @TableB (Col1, Col2, Col3)
  VALUES ('C', 'D', 'E');

  SELECT *
  FROM  @TableA
  NATURAL JOIN (SELECT Col1, Col2, Col3
                FROM    @TableB) AS B

  -- returns
  Col1 | Col2 | Co3
  'C'    'D'    'E'

  SELECT *
  FROM  @TableA AS A
  INNER JOIN (SELECT Col1, Col2, Col3
              FROM  @Table B) AS B ON A.Col1 = B.Col1
                                  AND A.Col2 = B.Col2

  -- returns A = @TableA, B = @TableB
  A.Col1 | B.Col1 | A.Col2 | B.Col2 | B.Col2
    'C'     'C'      'D'      'D'      'E'

Do you see the difference? Rather significant, no? With the Inner you still could compare the two table's results, but a Natural JOIN is like a INTERCEPT, only it groups the columns together. You lose the relation in your result set.

Conclusion:

• Everyone is entitled to their own opinion, but SQL will parse your query the same regardless.
• Learning about how the joins work helps you understand what business use those joins can function...or at least work with other SQL languages.
• TSQL removed the NATURAL JOIN in favor of UNION, UNION ALL, INTERSECT and EXCEPT.

If you can, ask your TL the 'whys' behind the business logic of using one or the other. Find out how he understands SQL Querying. You might either get something insightful or...well, be prepared to hear unconventional things...but at least you are learning more about SQL, your company, and how to ask questions.

A Win/Win, I say.