SQL Joins
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: SQL Joins
The value of simple diagrams....The circles speak louder than the SQL.
TX for this illustration
TX for this illustration
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Joins
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
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
Hans
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: SQL Joins
And just to "complete" the diagram...since one more combination is missing...
Spoiler
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Joins
That's not really valid SQL... You could use
SELECT <select_list>
FROM TableA A INNER JOIN TableB B
WHERE False
SELECT <select_list>
FROM TableA A INNER JOIN TableB B
WHERE False
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: SQL Joins
Your syntax might be correct, but my diagram is accurate.
PS: On a serious note, and as Stuart said...TX for the education.
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: SQL Joins
I had noticed that the eighth combination was missing, Rudi, but it seemed to be of limited value.
StuartR
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Joins
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)
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
Hans
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe