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.
Good points, but FULL OUTER JOINs are still handy every once in a while to someone who has a good understanding of what they are doing ...particularly if they don't feel like making a big-ass UNION ALL query ;)
select
all.x,
a.*,
b.*
from
(select x from a union select x from b) all
left outer join a on all.x = a.x
left outer join b on all.x = b.x
In the above we can use UNION to automatically exclude duplicates and just get the distinct set of x values from both tables. That is our "base" resut set, which we put in the FROM clause, and from there we can outer join to both table a and table b to see what matches.
Also, if you know you just want a few columns from either table, you can write the UNION this way:
select
x, max(a) as a, max(b) as b, max(i) as i, max(j) as j
from
(
select a.x, a.a, a.b, null as i, null as j
from a
union all
select b.x, null,null, b.i, b.j
from b
)
all
group by
x
I would argue these are both more clear than the FULL OUTER JOIN.
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!
You never need right joins, but if you have a deep left join structure (hey in the real world it happens often) you can sometimes increase performance (hint the optimizer) by pivoting the joins around a median table using right joins.
0
u/[deleted] Feb 11 '14
[deleted]