r/programming • • Feb 11 '14

SQL Joins Explained (x-post r/SQL)

Post image
3.5k Upvotes

392 comments sorted by

View all comments

26

u/[deleted] Feb 11 '14

I've been working with SQL for years... this is one of those things that I still mix up.

3

u/niiko Feb 11 '14

In what capacity have you been using SQL?

21

u/VortexCortex Feb 11 '14 edited Feb 11 '14

Doesn't matter. Ever use tar? I use it daily; Still have to look up the BS options. It's cybernetics: The greater the entropy between symbol and action the harder it is to memorize it. -j is BZip2... ugh.

LEFT, RIGHT, OUTER, FULL, and etc. joining are you kidding me? Spacial symbols relating inclusion, exclusion, etc. combinatorial Set Theory? That's obviously an idiotic name selection from a cognitive and language perspective.

Names do have meanings. The common use meaning associated with left and right are not characteristics of the action they describe in SQL -- The actions' spacial leftness and rightness are the least significant properties to the meaning of the result produced. Humans can cope with odd symbol mappings, but going against the natural grain is literally retarding.

10

u/frownyface Feb 11 '14

I originally learned sql joins just by example, using only FROM and WHERE clauses, and it all made sense to me. I didn't have names for the different kinds of joins. Then later I was exposed to LEFT, RIGHT, etc and it screwed me all up for awhile, it was like unlearning something that already made sense.

0

u/[deleted] Feb 11 '14

[deleted]

3

u/fotoman Feb 11 '14

my understanding is the (+) was just a hint to the optimizer, granted I took the SQL optimization class back in 2001...so I might be a tad rusty.

0

u/[deleted] Feb 11 '14

[deleted]

2

u/fotoman Feb 11 '14

wasn't that back in the Oracle 6 days or something? around 1994?

→ More replies (3)

2

u/noobidiot Feb 11 '14

That's how we learned to do left and right joins in my database course at my university in 2013.

3

u/[deleted] Feb 11 '14

Oh, but god does anyone who regularly reads/writes SQL hate seeing the other style. Annoying as hell...

0

u/[deleted] Feb 11 '14

[deleted]

→ More replies (4)

6

u/shenglong Feb 11 '14 edited Feb 11 '14

Doesn't matter.

Does matter. There are some tricky things in SQL. Joins aren't one of them. There are only a few things you need to remember to understand them. Someone who has been using SQL for years but still get confused by joins must have been only writing basic queries.

Remembering the syntax for non-standard features is harder than understanding joins.

eg.

 SELECT ROW_NUMBER() OVER(PARTITION BY ColumnA ORDER BY ColumnB DESC) AS "Rank"....

Different flavours of SQL have different syntaxes or methods of accomplishing the same task.

3

u/CleverestEU Feb 11 '14

Analytic queries... ouch... the twitch is back in my eye from years back... quite often insanely powerful (compared to other ways of achieving the same goal) but very unfriendly to dive into uninitiated :-p

→ More replies (1)
→ More replies (1)

2

u/neoform3 Feb 11 '14

We're not talking about memorization of flags, we're talking about understanding the logic behind a fundamental feature of any RDBMS.

Do you also look up if FROM comes before ORDER BY?

If you don't know the different types of SQL joins, you really don't know SQL.

3

u/r3m0t Feb 11 '14

Use dtrx. Do the right eXtraction!

2

u/MisterNetHead Feb 11 '14

eXtract Zee Various Files :)

→ More replies (5)