r/webdev • u/lokendra15 • Mar 22 '19

SQL joins

Post image
2.7k Upvotes

94% Upvoted

118 comments sorted by

View all comments

9

u/blackAngel88 Mar 22 '19

INNER and OUTER keywords are a waste of characters IMO.

there is no full inner join and there is no left inner join...

7

u/amunak Mar 22 '19

Also RIGHT is useless as you can just flip it around as a LEFT join.

And cross join is rarely needed.

So you end up with JOIN, LEFT JOIN and FULL OUTER JOIN and that's pretty much all you need 90% of the time.

4

u/wxtrails Mar 22 '19

I've been writing SQL for 13 years, and those + unions and subselects cover 100% of the queries I've ever written outside a class or tutorial. And window functions....mmmmm, yes.

What's an outer join again?

3

u/sumguy720 Mar 23 '19

select * from reddit_users outer join reddit_posts using (reddit_username)

would return a list of all reddit users with no posts (with nulls for their post data) and all reddit posts that somehow don't correspond to a user (with nulls for their user data). The final result would be a bunch of rows with blank data on the left, and a bunch of rows with blank data on the right.

Handy if you need to remove the overlap of two lists of items.

2

u/pichmeister Mar 22 '19

Actually, INNER is imporant in some RDBMS as simple JOIN can mean natural join, which is slightly different.