SQL Server - Lack of Natural Join/X Join Y Using(Field)

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

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

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

• Natural join in SQL Server
• SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)

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

CROSS JOIN vs INNER JOIN in SQL

Cross join does not combine the rows, if you have 100 rows in each table with 1 to 1 match, you get 10.000 results, Innerjoin will only return 100 rows in the same situation.

These 2 examples will return the same result:

Cross join

select * from table1 cross join table2 where table1.id = table2.fk_id

Inner join

select * from table1 join table2 on table1.id = table2.fk_id

Use the last method

NATURAL JOIN vs WHERE IN Clauses

Theoretically the two queries are equivalent. I think it's just poor implementation of the MySQL query optimizer that causes JOIN to be more efficient than WHERE IN. So I always use JOIN.

Have you looked at the output of EXPLAIN for the two queries? Here's what I got for a WHERE IN:

+----+--------------------+-------------------+----------------+-------------------+---------+---------+------------+---------+--------------------------+
|  1 | PRIMARY            | t_users           | ALL            | NULL              | NULL    | NULL    | NULL       | 2458304 | Using where              |
|  2 | DEPENDENT SUBQUERY | t_user_attributes | index_subquery | PRIMARY,attribute | PRIMARY | 13      | func,const |       7 | Using index; Using where |
+----+--------------------+-------------------+----------------+-------------------+---------+---------+------------+---------+--------------------------+

It's apparently performing the subquery, then going through every row in the main table testing whether it's in -- it doesn't use the index. For the JOIN I get:

+----+-------------+-------------------+--------+---------------------+-----------+---------+---------------------------------------+------+-------------+
| id | select_type | table             | type   | possible_keys       | key       | key_len | ref                                   | rows | Extra       |
+----+-------------+-------------------+--------+---------------------+-----------+---------+---------------------------------------+------+-------------+
|  1 | SIMPLE      | t_user_attributes | ref    | PRIMARY,attribute   | attribute | 1       | const                                 |   15 | Using where |
|  1 | SIMPLE      | t_users           | eq_ref | username,username_2 | username  | 12      | bbodb_test.t_user_attributes.username |    1 |             |
+----+-------------+-------------------+--------+---------------------+-----------+---------+---------------------------------------+------+-------------+

Now it uses the index.

---

Related Topics