SQL Joins

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

SQL Joins

Post by StuartR »

I loved this simple diagram that went past on my Twitter feed today...
You do not have the required permissions to view the files attached to this post.
StuartR


User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: SQL Joins

Post by Rudi »

The value of simple diagrams....The circles speak louder than the SQL.
TX for this illustration :thumbup:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL Joins

Post by HansV »

Useful diagram!

For users of Microsoft Access: FULL OUTER JOIN is available in SQL Server, but *not* in Access SQL. In Access, one has to use the UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT <select_list> FROM TableA AS A LEFT JOIN TableB AS B ON A.Key = B.KEY
UNION
SELECT <select_list> FROM TableA AS A RIGHT JOIN TableB AS B ON A.Key = B.KEY
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: SQL Joins

Post by StuartR »

Thanks for the education Hans
StuartR


User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: SQL Joins

Post by Rudi »

And just to "complete" the diagram...since one more combination is missing... :grin:
Spoiler
1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL Joins

Post by HansV »

That's not really valid SQL... You could use

SELECT <select_list>
FROM TableA A INNER JOIN TableB B
WHERE False
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: SQL Joins

Post by Rudi »

Your syntax might be correct, but my diagram is accurate. :grin:

PS: On a serious note, and as Stuart said...TX for the education.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: SQL Joins

Post by StuartR »

I had noticed that the eighth combination was missing, Rudi, but it seemed to be of limited value.
StuartR


User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: SQL Joins

Post by HansV »

Returning an empty recordset does have its uses - it can be used

a) to inspect a set of field names,
b) to create a new empty table with the correct set of fields, or
c) as the first SELECT statement in a UNION query to specify the field names
(If you create a UNION query in which the constituent parts have different field names, the first part prevails)
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: SQL Joins

Post by StuartR »

Thank you, that is informative, as usual
StuartR