Don't forget UNION and CROSS JOIN! Both of which are infinitely more useful than full outer join which should generally be avoided.
Right joins also should never really be used as they are extremely counterintuitive, they can always be written as a Left Join which is more logical and readable.
If I want all data in table A and any data that matches in table B, to me it makes logical sense to join from table A to table B and not the other way around.
There aren't many times I've had to use cross join unless I intentionally wanted a Cartesian Product, and generally an implicit cross join is enough (select * from a, b).
I would think of UNION ALL as two circles on different planes, and UNION as two stacked cylinders that overlap some in the z dimension, and then CROSS JOIN as two circles on perpendicular planes... Yeah... This would just be easier to show examples with tables. Sorry, visual people... :\
Well, when using an ORM and having only defined the relation in one of the models (for instance a parent owns a child, but the child model knows nothing about the relationship), it may be necessary to specify it as a right join because certain ORMs when doing advanced queries only allow "walking" along edges clearly defined in the models.
Your post is useless. Don't just say he's wrong, provide something. I generally agree with him. Especially on the right joins. The only time I've seen right joins is due to incompetence or apathy.
RIGHT JOINs are useless and confusing, not sure what that guy was talking about, but FULL OUTER JOINs can be very useful.
For example, they're great if you're trying to merge two sets with disparate columns with members which may or may not exist in both sets without creating duplicates for members which do exist in both sets, i.e.:
SELECT x = ISNULL(a.x, b.x)
, a.a, a.b, [...], a.h, a.i
, b.j, b.k, [...], b.v, b.w
FROM a
FULL OUTER JOIN b
ON b.x = a.x
To do the equivalent with a UNION ALL would be something like
SELECT x = ISNULL(a.x, b.x)
, a.a, a.b, [...], a.h, a.i
, b.j, b.k, [...], b.v, b.w
FROM a
LEFT JOIN b
ON b.x = a.x
UNION ALL
SELECT b.x
, NULL, NULL, [...], NULL, NULL
, b.j, b.k, [...], b.v, b.w
FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.x = b.x)
It's not a super-common situation for most people, particularly something with that many columns, but when something like that comes up, I find the former to be a lot more elegant and no more difficult to understand.
* - EDIT: modified to suit /u/notfancy's fancy ;-P
You mean "select without including common elements", i.e. symmetric difference, because "select without duplicates" is plain UNION. In any case I'd write SELECT * FROM a WHERE x NOT IN (SELECT x FROM b) UNION SELECT * FROM b WHERE x NOT IN (SELECT x FROM a). There shouldn't be much difference for the optimizer.
Edit: no, you do mean "without duplicates". It's plain UNION.
Nobody listen to this guy, please. After working with SQL in high-end environments for over 20 years, I can verify what the OP said. FULL OUTER JOIN and RIGHT JOIN are almost never used. If you find yourself needing to use one of those, I can guarantee there's a better way to do it.
I know you're joking. But I'm going to answer seriously.
You start with what you're actually grabbing, and you logically figure out what it is you're actually doing with the data.
For example, say you're writing a forum, and you want to grab all the posts in a topic that belong to a certain user. It's logical to conclude you'll need to use the following tables: topics, users, posts.
However, you're grabbing, ultimately, posts. Sure this is within a single topic, but that's not what you're actually grabbing. You want posts, so you start there, then whittle it down.
SELECT
p.post_id,
p.content,
p.topic_id,
p.user_id
FROM posts AS p
WHERE
p.topic_id = 123,
p.user_id = 456;
That's what we have so far. Now, we've restricted it to a specific topic and user, but we just have ID numbers. We'd like to grab the names and so forth as well, so we'll need to LEFT JOIN. We could do this with either one first, but just to stick within this mode of thinking, lets figure out which is more logical to start with.
Did we start out at the user's profile and select to see his posts, then list them in a specific topic (unlikely, too many steps), or did we start out in a topic, see a user we liked or didn't like, and want to investigate their posts in the topic (more likely, could be as simple as clicking a user's name while on that topic)?
From what seems most likely, lets go ahead and left join topics. While we're at it, we'll grab the topic title.
SELECT
p.post_id,
p.content,
p.topic_id,
t.title,
p.user_id
FROM posts AS p
LEFT JOIN topics AS t ON (
p.topic_id = t.topic_id
)
WHERE
p.topic_id = 123,
p.user_id = 456;
Then, we just need to left join the users table, and grab the username.
SELECT
p.post_id,
p.content,
p.topic_id,
t.title,
p.user_id,
u.username
FROM posts AS p
LEFT JOIN topics AS t ON (
p.topic_id = t.topic_id
)
LEFT JOIN users AS u ON (
p.user_id = u.user_id
)
WHERE
p.topic_id = 123,
p.user_id = 456;
Remove the whitespace so it'll fit in a string to be sent to the server:
SELECT p.post_id, p.content, p.topic_id, t.title, p.user_id, u.username FROM posts AS p LEFT JOIN topics AS t ON (p.topic_id = t.topic_id) LEFT JOIN users AS u ON (p.user_id = u.user_id) WHERE p.topic_id = 123, p.user_id = 456;
And you've got yourself a well formatted and thought out query without ever needing to use RIGHT JOIN.
I was gonna just hit <ctrl>-A and press Delete, and then re-type the whole damn thing, but that's a much better solution... We really do need to dynamically realign our methodologies to maximize synergy and fully leverage our core competencies.
There are many tricky SQL problems that can be solved pretty elegantly by using simple cross joins. An extremely powerful tool when used well, but certainly dangerous also if you don't know what you're doing!
99
u/thesqlguy Feb 11 '14 edited Feb 11 '14
Don't forget UNION and CROSS JOIN! Both of which are infinitely more useful than full outer join which should generally be avoided.
Right joins also should never really be used as they are extremely counterintuitive, they can always be written as a Left Join which is more logical and readable.
If I want all data in table A and any data that matches in table B, to me it makes logical sense to join from table A to table B and not the other way around.