Sql-server – SQL Server: Can you join implicitly using key relationships

join;sql server

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

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A

Best Answer

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

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Sql-server – How many LEFT JOINS make a VIEW unusable

The link provided by Martin Smith to an existing SO question provided me with the answer:

The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Sql-server – How many LEFT JOINS make a VIEW unusable

Related Question