spliffen
31674
1159
12
mostly saved for own usage, if anyone else can use it, even better
Nov 3, 2024 12:02 PM
spliffen
31674
1159
12
mostly saved for own usage, if anyone else can use it, even better
PixelSprite64
Wraid
erelyes
Alright here's a pro tip for you. ON is just WHERE. So last query for example, instead of "WHERE B.key IS NULL" write "AND B.key IS NULL" . That way you keep everything to do with that join in that join. Helpful if you then have to do like 10 other joins...
LebistusReticulatis
4 and 6 are “select * from A” and “select * from B”. No joins necessary
mikeatike
4 is if you want some columns of B but you are ok with them being null.
6 is just a dumb way of writing 4
SwissScars
Started SQL in the late 80s, wrote some amazing systems in the next 30 years, specializing in PL/SQL, and persuading people to write readable code - life's more fun if other people can maintain your code.
AllTheKitties
I always write my code with future me in mind. Then I get to thank past me for being so helpful.
thorinc
Thank you for thinking about future devs, it's been my mantra for 24 years as well. Too many new devs don't understand yet how they're just hurting their future selves.
Oracle sucks, though.
SwissScars
When i was dealing with them in the 80s in London (they had a sweet little office in Richmond) and in the 90s in Zurich, Oracle was fun to deal with. Things went downhill...a very steep hill.
Evi1Gav
I remember teaching one of our grad student Devs why the stored procedure she had written was problematic - making all the select variables to be incomplete could, theoretically, have allowed someone to download the entire database. This was 20 years ago, so bad in all sorts of ways. She's still a dev, still keep in touch, she still remembers the lesson.
LittleAzie
This chart is so much easier to understand than the wall of text my textbook in college used to explain it...
cyberimg
Fine, I's fav it. Mind you: select from A,B without any proper key-compare is way more fun, specially on large tables.
macgerdo
sadurdaynight
Left out the CROSS JOIN, where every item in A gets every item in B. Cross joins are useful when you create a new record that has automatic details that should go with it. EG you might have a table for car types, and every car type would have a hood, trunk, doors, wheels, etc. So, if you add a new car you can cartesian (no pun intended) cross join the car type table to the car detail table and use it to insert all the new details for the new car.
FeloniousMonk13
I work in finance, mostly legal- took an SQL course to explore possibilities for promotion- man I just couldn't get into it, just too dry for me.
fr0stbyte124
The textbooks are the worst. It's actually pretty intuitive to pick up, just fiddle around with a realistic test database like NorthWind and see how you would go about answering questions about the data it contains. I learned more in a week working on an already functioning database than I did in two semesters of abstract nonsense.
Also if a textbook says to use an employee's SSN as a primary key, throw that book in the fire.
surkhetandsukhad123
Before the digital era, infrared imaging was achieved using various infrared films that were available on the market. Most of these films were black and white, such as Kodak HIE and Rollei (R). However, there were also some false-color infrared films, with one of the most famous being Kodak’s EIR.
See an example image here:
link below."
https://url1.io/DXgeu
MidasTheAlchemist
I benefit from this when using KQL.
idalacn
but never SELECT *
cousteau
I see butts. Anyone else see butts? No? Just me? Alright then.
geotard
ANSI SQL is a simple implementation that Oracle and Microsoft then proceeded to butcher making moving between platforms extremely annoying. Oracle, lets change the syntax slightly, Microsoft, hold my beer, have you seen one of these '['. Oracle hold my beer and shot, MySQL, more like "My"SQL you go and play with Maria... Snowflake you guys are on-premises? Databricks, lol, databases, catchup we're doing work over here.
IlDepo
Goddamned Microsoft access does not support full joins.
AttilaTheHungover
Stupid fuckers bought FoxPro, then shelved it in favour of bloody Access.
thorinc
There's been a free version of SQL Server out for 15+ years now that is way, way better than Access and its JetDB engine. Try switching to that and you'll get your full joins.
IlDepo
Corporate computer, can't switch, I have to use whatever corporate provides/allow.
FoamingToad
I could have sworn it did, but you had to invoke via the qbe grid and not join the two tables. Granted, I don't recall the generated p-sql and I don't remember how additional joins worked, but really thought I'd done that in access.
FoamingToad
Yep, works (since Access 2010).
SELECT Table1.Field1, Table2.Field1
FROM Table1, Table2;
FoamingToad
Scalar join get.
IlDepo
Sure it does.
But makes a linear combination of all records in both tables. Do it with something like 3 tables with half a million records each. In Microsoft access.
FoamingToad
If the 2GB file limit is still a thing (I don't remember, use PostgreSQL these days and apart from yesterday haven't touched MSA in years) this is unlikely to work unless they're all joining on integers (does Access support GUIDs yet? Might check). To be fair though, this is not a problem an experienced DB user would want to be faced with, IME.
ElPerroDeLosCinco
Remember to always run the DROP TABLE command for improving efficiency.
SomebodyGet2020APuppers
TheSameDeviledEgg
SQuirreL!
DFTM06
Left, outer, and look for null values.
Hypothesist
lets hope that software devs were not waiting on this cheat sheet on imgur to get their work done
gablestout
@OP If you are new to SQL, may I suggest the book, "SQL Queries for Mere Mortals"?
That's the book I used when I got into BI 12 years ago. The only book. Between that and "Database Design for Mere Mortals" I'm a Senior BI Engineering Consultant now. If you want to stay as an analyst, you only need the "SQL Queries" book, plus Edward Tufte's books, starting with "The Visual Display of Quantitative Information". Then learn Tableau or PowerBI. DM me if you need any other tips.
spliffen
not new, just been a while, a refresher was needed
thekeyofe
The "INNER" keyword on the first JOIN is unnecessary. Using "JOIN B ON A.key = B.key" works the same, at least on Microsoft T-SQL. I'm not sure about the other dialects, like MySQL, PL/SQL, etc.
AllTheKitties
Yes, but it helps clarify especially for newbies.
FoamingToad
Yep. Inner and outer join declarations boil my piss, completely redundant.
terajack2048
Umm ansi would like a little chat with you…. If you never migrate to another db fine. If you commonly port your code whole different world
gelLord
The very last line is wrong. Should be WHERE A.key IS NULL.
AllTheKitties
Thank you, I was wondering if I was losing it, haha
apneax3n0n
Not necessary primary Key but please add an index
gablestout
Depends on the DBMS. Unless you're writing up an ERD and need to show primary and foreign keys on the map.
For example, neither Redshift nor Snowflake enforce keys or indexes, whereas MySql can barely breathe without them...
apneax3n0n
True
sushipig
Wildly inaccurate and unhelpful. SQL has UNION and INTERSECT operators which do correspond to Venn diagrams, but JOIN has completely different semantics (where one record joins to many) which are not represented or explained by these diagrams.
EngorgedPhallus
*laughs and cries at the same time in MongoDB*
thorinc
JavaScript devs using Mongo get used to all the BSON documents having different structures, and then learn all the syntactic shortcuts for possibly-missing properties.
"?." Is the Optional Chaining operator.
"??=" Is the Optional Assignment operator.
"??" is the Null-or-Undefined operator (which is not the same as the "||" Falsy operator).
That's my life now.
FlyingButtPliers
Like throwing out the seats and seatbelts to make the car go faster
Discgolfing
This is a new favorite quote, thanks
LeftRightThere
Hey, we built this data without structure, keys, or future planning (managers yell AGILE in the background), where should we store it? Obviously not in a SQL db!
UWAGAGABLAGABLAGABA
Who wants related data? That's so old school! /S
AttilaTheHungover
"It's web-scale!"
Vasnivy
I snort laughed at this.
Snooj
Isn't this just AND, OR, XOR, etc.?
LebistusReticulatis
Yes, but this is the database way of thinking
RuBisCO1
And xand!
cousteau
I know 3 of those 4 logic operators.
JustaSimplePlumber
This shit is fucking Greek to me. You computer kids impress the hell out of me. Give me a visibly broken pipe or something to repair any day.
Daemencer
It's not really so much about computers than it is about maths. It's translating maths concepts into words for humans to type easily to then be translated into computer language. It's very logical when you start at the beginning
SolHSA
if you want to de-greek it, I made an interactive tutorial: https://solhsa.com/g3/
What3Birds
SQL was invented in 1974. I have grey hair and that was before I was born...
TheNihilistsParadoxicalWashingMachine
Most of it is conceptually very simple, but sometimes by necessity and sometimes just because people like making it more impenetrable the exact language or magic phrases you need to intone to get it to work can be unintuitive and look Greek. The trick is explaining things in terms that everyone can easily grasp ( if you are trying to show people the possibilities and or teach them ).
RuminatingYak
Anything will look Greek if it's too advanced. This may be the basics of SQL, but it's still advanced knowledge compared to the actual basics of how computers work. If you actually want to learn, start with Boolean Algebra. You can't understand anything about computers until you understand that, but fortunately it's fairly simple.
FoamingToad
Plus, unless you have a bit of set theory or venn diagrams, pretty meaningless. 20 years of SQL, about 40 prog btw.
SadsPikkelson
The venn diagrams show it all. This is an explanation of how you select elements of two groups using different additive and subtractive operators. Think and, or, nor, xand, xor, etc. It's basic set logic.
LeftRightThere
Just tell your IT buddies to do a CROSS JOIN and DROP TABLE. Caps are very important.
gablestout
DandyLion23
cat readme.txt |
Thisisabigmistake
I can't do computers or pipes. Give me a law to draft and have conservative politicians take everything out of context cuz they won't hire lawyers to review and just go full "anti woke rage"
spliffen
It goes both ways, I'd just end up hurting myself, the pipe, and the neighborhood If I tried that :D
GuitarsAndCodeAndRage
Ouch, I feel personally attacked
Evillairforrent
Three ways actually.
I can't fix a pipe or do IT stuff.
But give me 18 gears and i'll make stuff disappear
LincLoud
I am not handy or good with computers. Pretty useless
Evillairforrent
Can always becone a truck driver.
mikeatike
This is SQL-92. How old are you?
https://en.m.wikipedia.org/wiki/SQL-92
ServerMonkeyKing
It's Classical Logic (of sets from Plato and his students) contextualized into code. [and, or, xor, not, nand, nor, xnor]
mmaissan7712
Left outer join is what I use
Felberin
I think that's like 99% of SQL queries in the production. Also on that note, this is good on demonstrating boolean algebra, but doesn't really explain why said left join is used :P
Alurkerforcedtologin
A left outer join returns data from the first table, and also matching data from the second table. An example: retrieving all products, and also including orders for those products but not excluding products which have never been ordered. An inner join in the same query would result in excluding products which weren't ordered.
Felberin
Yeah, but this graphic above is not great explaining that. So I personally don't find much value for it. Because it's like inner join between tables "Join_types" and "join_types_already_learned" :P
Carr0t
Isn't an outer join the default though, so the LEFT JOIN example above is that one?
OlaphOfTheNorth
right joins are an antipattern at this point, it just means you wrote the query and forgot about something you needed and didn't wanna rephrase it to make sense to future devs
gablestout
Right Joins are for people who didn't think out the query before they wrote it, and for people who want to see the world burn
mjcherbert
I *heart* PostgreSQLs lateral joins - effectively lets you do joins against the partial results of the query to that point as if that were a table ... the only way I'd imagine you might get there otherwise would be with temporary tables, views or some other inline thing ... that and CTEs are *chef's kiss*
ninty9notout
CTEs were created by the Gods. My favourite is recursive CTEs.
mjcherbert
I ... I hadn't even thought to dabble in the mystic arts ...
FoamingToad
Now do CTEs...
Hypothesist
with
sadurdaynight
I tried CTE's for a while, but it became a pain trying to debug code. I hammer out code with an obstacle course of temp tables, then convert them to CTE's later if it's all fleshed-out. Temp tables are just more versatile when sorting things out.
AllTheKitties
I <3 temp tables and I don't care who knows.
sadurdaynight
The biggest pain is being an analyst doing a lot of analysis working on a major project, writing massive sql's to dig into things, and asking the devs or dba's to take some of the core temp table or CTE code and add them to the db as views. Get a lot of push-back. Until they see the code and go "oh, yeah, that would actually be useful."
NebulousToucan
I only bother with CTEs for small queries. Pulling back a range of dates, and another with a range of items or whatever and then mashing em together at the end. Anything more complex tends to go into a temp table because debugging with a bunch of CTEs is a ballache. I inherited a 9000 line stored proc (I know) that was a mess of if/else logic and CTEs. It broke me of using them much
agentHoneyBadger
Where is the Cartesian join?
XSurvivor
There is CROSS JOIN and then there is CROSS APPLY. I still can't decide which one I hate more.
TheRidg
both make me cross, normally because (and more often than not) the person asking for "the quick report" could and should have done it in Excel
fr0stbyte124
We don't talk about the Cartesian join.
terajack2048
Still running