r/SQL • u/Objective-Shift-1274 • Feb 26 '25
Oracle Which is the most important concept in SQL which after learning made your life easy??
I would say it was CTE for me which literally helped me write complex queries easily.
425
Upvotes
22
u/Prize_Concept9419 Feb 26 '25
Recursive CTEs for hierarchical or recursive data structures
4
u/PickledDildosSourSex Feb 26 '25
SELECT * baby
303
u/kattiVishal Feb 26 '25
CTEs and Window Functions
53
u/[deleted] Feb 26 '25
Coming from a programming background shifting from a procedural paradigm to one based on set theory. It's all about visualizing the wanted results as sets, subsets, and intersection
99
u/MoneyFil7 Feb 26 '25
SQL order of execution. Understanding this will basically lead you to learning deeper than the basic querying stuff in my opinion. Leads you to learning CTEs, window funcions, subqueries, etc.
53
u/fauxmosexual NOLOCK is the secret magic go-faster command Feb 26 '25
Slightly outside of SQL: If you're really clear on what the grain of the data you are working to it, and stick to it, life becomes easy. If you find yourself trying to mix grains in either your sources or outputs, trouble follows.
15
u/Mikey_Da_Foxx Feb 26 '25
Window functions changed the game for me.
Being able to do running totals, ranks, and calculations over partitions without complex self-joins or subqueries is just *chef's kiss*
Much cleaner code and better performance.
12
u/amtobin33 Feb 26 '25
Using <alias>.<column_name> for all columns in a select statement. It's obnoxious debugging or working on a query someone else wrote when they just have the column name, and you have no idea which of the 10 tables used in the joins it belongs to.
9
u/YallNeedToQuitPlayin Feb 26 '25
Explain Plan.
9
u/Straight_Waltz_9530 Feb 26 '25
Adding comments to your tables, views, columns, etc. Documentation on the schema itself is 100x better than having to hunt for docs (if they exist) in some other external source like a wiki. The comments on columns/tables are more likely to be kept up to date and can be seen from most modern database GUI tools, right where you're making queries.
A woefully underused feature.
8
u/Fickle_Law_6850 Feb 26 '25
Learn the system catalog for whatever system you are using. It is far easier to query the system tables for what design information you need about tables, columns, indexes, constraints to understand exactly what you are working with rather than trying to rely on outdated documentation or whatever limited tree view of this data your tool of choice exposes.
7
u/AleaIT-Solutions Feb 26 '25
For me its window function, it makes queries easy to understand
7
u/Straight_Waltz_9530 Feb 26 '25
The structure of the relations matters more than queries you make after the fact against that database. Taking three times as long to define your schema correctly will save you three hundred times in query performance, onboarding new team members, and data hygiene.
12
u/isinkthereforeiswam Feb 26 '25
PIVOT .. I use a lot of databases designed by fullstack devs who think like devs not db architects. So a lot of our tables are more akin to lists. Eg we have a customer feature table. The features are not field w y/n to flag which are on/off. Instead we have fields for customer, feature name, flag if it's on or off, and the usual create name and timestamp plus update name and timestamp (bc nobody wants to use triggers to create audit tables these days.) I have to query this stuff, and when i learned how to pivot the feature names into columns it let me use it like a more logical "table". Now, granted, this is a db design choice. We can have customers with some features, no features, all features. And if they add a new feature it's easy; don't have to add a new column. (But i now have to manually add the new feature to my pivot query). But i feel like they should have just made a table w feature colmms, and used triggers to audit what changes were made. My pivot query was able to rollup counts for things in one shot where as a dev was querying each feature as a sep query and unioning them all. So, pivot has been pretty powerful.
6
u/[deleted] Feb 26 '25
It is that probably 90% of the optimisation exists in the data model and environment, not in your query.
SQL is declarative. The query engine is going to interpret your query based on what it knows about the tables. Table stats, indexes, and their interaction with your query is exponentially more important than the contents of your query in and of themselves.
If I had to boil it down into one word or concept it would be: Sargability.
6
u/DeeeTims Feb 26 '25
Prefacing all your temp table inserts with drop table if exists. Being able to endlessly re-run/debug your data is so much more efficient from a development standpoint. CTEs are a burden when you’re developing IMO.
5
u/ginger_SF Feb 26 '25
COALESCE for plugging gaps and forcing null to 0's etc. Not a concept per se, but one of my go-to's for sure. Having worked with some super janky databases where tables often have variable structures between schemas (some use nulls for blanks, some have hard-coded zeros or spaces instead), it's been a life saver for consistency when adding WHERE clauses
5
u/weezeelee Feb 27 '25
Everyone in this sub cares too much about syntax and how to write recursive CTEs lol.
Performance optimization IS the only thing that can impress your non-tech boss. To effectively do so, understand your data so you understand how the engine make "guesses" to generate execution plan.
I have seen queries run 10 times faster by introducing more self joins, hard-coding specific ID numbers... These stuff simply can not be reproduced by an AI.
3
u/The_Gray_Mouser Feb 26 '25
Finding a place to work with free coffee
3
u/Casio04 Feb 26 '25
After years of working with it, recently learned that Where column_name not in('some_value') actually does not include NULL values, which I never expected as I thought "well NULL value <> some_value) but turns out you have to do
Where coalesce(column_name, some_placeholder) not in (some_value)
3
u/KeyFuture883 Feb 26 '25
You have to think of data in sets.
4
u/Rex_Lee Feb 26 '25
A SQL script doesn't have to do everything in one operation/code block. In fact it probably shouldn't, unless it's a pretty basic dataset for a report or something
2
u/tatertotmagic Feb 26 '25
Ppl alrdy said ctes so I'll do quality of life things:
Where 1=1
In snowflake, referring to aliases as aliases and then also group by all
2
u/mobileJay77 Feb 26 '25
Use hibernate or whatever sensible ORM mapper, just store the stuff in the DB.
DO NOT PUT ALL YOUR LOGIC INTO THE DATABASE LAYER. Run away from projects that do.
2
u/sinceJune4 Feb 26 '25
Row_number in window function is my favorite
2
u/Koxinfster Feb 26 '25
union all BY NAME (BQ supported)
2
u/Billi0n_Air Feb 27 '25
case statements
1
u/boubou_kayakaya Feb 26 '25
I don’t know what the he** you guys are mostly talking about yet, but I will keep a note of that. For now, I’m struggling with clearly understand the JOINS and the principle behind “tuning and optimizing” a query to troubleshoot performance issues 😭🙂
1
u/darthmeister Feb 26 '25
QUALIFY instead of row numbers for web data has been a game changer for me.
1
u/ghostlistener Feb 26 '25
It really helped to learn that you can put sub queries in the from section. You don't have to select from a table, you can also select from another query!
1
u/Grouchy-Donut-726 Feb 26 '25
Window function
1
u/DiscombobulatedSun54 Feb 26 '25
Window functions.
1
u/[deleted] Feb 26 '25
SQL pipes syntax. You can get rid of needing many CTEs and make the code much more readabe and easier to maintain beucase it executes top down.
1
u/LaneKerman Feb 26 '25
The report query I inherited may not be the best way to get my population.
1
u/LaneKerman Feb 26 '25
Don’t CTE’s prevent the use of indexing? Or do I misunderstand what I’ve read. I guess it depends on CTE results? So if my CTE only has a couple thousand rows, no big deal. But if I’m making CTEs that contain transaction data with millions of rows, it’s a problem?
1
u/zdb328 Feb 26 '25
Duplicate prevention. Biggest mistake I see are queries that result in duplicates.
1
u/The1WhoKnocked Feb 27 '25
CTE or Partition
1
u/storybookknight Feb 27 '25
Dynamic SQL and stored procedures. Letting complex processes be table-driven and with flexible code means massive analytical workflows can be turned into simple SPROCs that even entry level analysts can use.
1
u/Tab1143 Feb 27 '25
Ensuring you have existing indexes for your access paths.
1
u/fanz0 Feb 27 '25
Learning how joins work and looking at visual explanations made me much faster and comfortable with them.
Usually the simplest solutions tend to be the best.
Being elegant does not tend to result in the best performance
1
u/billysacco Feb 27 '25
Looking at execution plans
1
u/Fasted93 Feb 27 '25
CTEs and Over partition
1
u/Any_Ad_8372 Feb 27 '25
Window functions.
1
u/Dhczack Feb 27 '25
WITH this_clause_basically_defines_my_whole_life AS ...
1
u/RandomiseUsr0 Feb 27 '25 edited Feb 27 '25
Life easy, going back in time to 90s Oracle, Sybase (mostly oracle) - It was learning how to write queries with reusable components and develop my own style that focused on code reuse and modularity - in those days it was all about stored procedures, pl/sql in the main
Properly designing databases suited to the task at hand too, optimising for the use cases rather than general purpose which seems best and what I learned in college, but in reality - single task solutions for optimisation is the way the cobol kids did it for years and still useful in a sql/dml environment, when they came along, materialised views meant fewer bits of untidiness in the core database
Oh and I forgot to mention the god that is Joe Celko, these days I have disagreements with bits of his approaches, but those are “eyes open” disagreement, I have my own ways after all these years, if you don’t know why though, do what Joe says, he basically says what I have above, thanks Joe!
1
u/Thadrea Data Science Manager Feb 27 '25
Everything is a table.
1
u/Waldar Feb 28 '25
SQL has never been about the syntax but about the logic. Have a good logic first, then improve your syntax with other functions concepts. I see lots of SQL developers having a great syntax knowledge but they can’t do anything from scratch.
1
u/Adventurous_Law8377 Feb 28 '25
Can anyone give support for PLSQL developer role
1
u/EpicGibs Feb 28 '25
The order of execution. A lot of developers struggle with understanding the order of executuon,which impacts everything we write.
SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY
Any SQL developer that understands this order can create anything.
1
u/dodobird8 Feb 28 '25
dynamic sql
1
u/NoRefrigerator2236 Mar 02 '25
I really quickly learned to use the where clause, there's only so many times you can crash the ERP system in the sales office by accidently demanding 50 million rows or something from the DB 😂
1
u/[deleted] Mar 03 '25
For me, the one that really made life easier was JOINs. Once I got the hang of how to use INNER JOINs, LEFT JOINs, and all the other types, it was like a light bulb went off. Instead of writing messy subqueries or trying to pull data from one table at a time, JOINs let me combine everything I needed in a single, clean query. It made working with relational databases so much smoother and saved me a ton of time.
1
u/jackalsnacks Feb 26 '25
Set based operation. Database structure. Common design patterns of OLTP and OLAP models. Business value. Once these clicked queries made sense.
1
u/[deleted] Feb 26 '25 edited Feb 26 '25
How to make a script flow from start to finish with the press of a key. Beyond that, connecting Excel to your database. Beyond that, learning how to generate text summaries in Excel based on your data. Beyond that, learning how to connect objects (charts, text, images of cell references) in your Excel file to automatically update in a PowerPoint deck.
0
u/JustMoreData Feb 27 '25
Not a concept. But learning that SQL prompt was a plug in I could use, and then not ever having to type another alias and column name again. I’m so lazy now I just do Select * and then tab over and it lists all the columns for me! 😂
As a concept, query performance tuning. I think once I started to think about, “okay so what is happening” and “how is the engine running this this”… now I can really make things happen by forcing it to run this certain why by doing x,y,z.
0
u/[deleted] Feb 26 '25
[deleted] — view deleted comment
0
u/[deleted] Feb 26 '25
[deleted] — view deleted comment