SQL Server: Can you join implicitly using key relationships? If I have two tables;

Table A
id | etc
1  | etc
2  | etc
etc


And

Table B
id | A_id | etc
1  | 2    | etc
2  | 2    | etc
3  | 1    | etc
etc


The relationship between Table A and Table B is defined in the database as a foreign key with cascading delete. Do I still have to explicitly declare join columns between these tables? Several ORMs that I have previously worked with do not require this (for example the PHP Silverlight ORM), and I was thinking that if you requested unambiguous column names, why not? It seems to me that defining FK relationships should automatically define which columns to join a table one

For example, I want to know if I can write this query:

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A ON B.A_id = A.id


As

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A
You always have to specify the columns you are joining on, regardless of any foreign key relationships.

Why? Because otherwise you couldn't do a full outer join if a foreign key existed between the tables, which would lead to identical queries producing different results for non-obvious reasons.

Also, what if you explicitly join on a different field? Would SQL behave as if you had included both fields in the join statement? Would you have to somehow tell it to ignore the foreign-key relationship for this one query?

FYI, the term "implicit join" in SQL actually refers to something entirely different, a SELECT statement made using a comma instead of a JOIN keyword:

 SELECT *
 FROM table_A, table_B
 WHERE table_A.key = table_B.key


vs an "Explicit" join:

 SELECT *
 FROM table_A JOIN table_B
 ON table_A.key = table_B.key


(This "old style" of implicit joins still works, but is not recommended.)