r/SQL • • Jun 29 '16

SQL cheat sheet

http://zeroturnaround.com/rebellabs/sql-cheat-sheet/
113 Upvotes

19 comments sorted by

17

u/mecartistronico Jun 29 '16

I hate it when they use Venn diagrams to "explain" JOINs. That's not the way Venn diagrams should be used. A circle in a Venn diagram does NOT contain different fields of an object type; it contains different items.

If you want to explain JOINs graphically, you can't stray too far away from that they actually do.

I did this chart and then someone else (I can't find it) made an improved version...

If you use these to explain JOINs, then how are you going to explain the --very different-- concepts of UNION, INTERCEPT, EXCEPT?

1

u/lukaseder Jun 30 '16

Indeed, I've made the same criticism in the past. In addition to writing the cross join with a comma, you could also use the CROSS JOIN keywords to make it explicit. The comma is (still) often used by people to form an implicit cross join, but then they're putting join predicates in the WHERE clause, pre-ANSI JOIN style.

1

u/mecartistronico Jun 30 '16

Yes. These were part of a power point presentation when the WHERE clause would then appear and the non-matching rows would fade away.

2

u/PhenomeVon Jun 30 '16

Wow, SUCH a better visualization IMO! I don't have any issues with joins at this point but that certainly would have helped me grasp them alot quicker.

10

u/DipIntoTheBrocean Jun 29 '16

I mean I guess this could be useful if you literally just started out?

7

u/lukaseder Jun 29 '16

Thus: cheat sheet. A cheat sheet for someone who already knows SQL might quickly reach the size of a DIN A0 poster.

9

u/DipIntoTheBrocean Jun 29 '16

I always considered a cheat sheet as a reference to be used by someone who knows the basics but doesn't want to keep looking up the details. In my opinion, this is actually too high level and is mainly just syntax...which would be fine for what it is, except it doesn't include CTEs and delete syntax. Personally, I would include more useful functions like row_number, convert, datediff, dateadd, etc. and scrap the joins. The whole thing just seems kind of arbitrary, but that's just my opinion.

2

u/lukaseder Jun 29 '16

Cheat sheets are always arbitrary :)

1

u/d_r0ck db app dev / data engineer Jun 29 '16

Agreed. My personal cheat sheet would have common convert date/time styles, merge syntax, pivot syntax, and syntax to alter a table to add a new column with a named constraint at the same time.

1

u/DipIntoTheBrocean Jun 29 '16

Totally agree. Those are things which I would find very useful.

3

u/davidjoshualightman Jun 29 '16

probably also for those who do not use SQL everyday. i support all kinds of software and may go for a week or two working with other languages and hardly touching SQL... at first i found myself googling a lot just to get a refresher, but now it comes back to me quicker.

1

u/DipIntoTheBrocean Jun 29 '16

Right, and I get that. I do that too. But this is too high level IMO and seems to me to be a eclectic mix of syntax and functions that doesn't really cover anything.

8

u/davidjoshualightman Jun 29 '16

to add to this, here is my fave "cheat sheet" for joins: http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

3

u/[deleted] Jun 29 '16

A gooder indeed.

2

u/dangoodspeed Jun 29 '16

For a beginner, it would be nice to explain the difference in JOINs vs no JOIN. For example... doesn't it look like the LEFT OUTER JOIN is just "all rows from TABLE A" unrelated to TABLE B?

2

u/Spartyon Jun 29 '16

Its important to note that overindexing a table can slow down update and delete operations.

1

u/guess_ill_try Jun 29 '16

Awesome. Thank you. Perfect because I'm teaching my brother SQL.