Everyone knows that new developers write long functions. As you progress, you get better at breaking your code into smaller pieces and experience teaches you the value of doing so.
Enter SQL. Yes, the SQL way of thinking about code is different from the procedural way of thinking about code, but this principle seems just as applicable.
Let’s say I have a query that takes the form:
select * from subQuery1 inner join subQuerry2 left join subquerry3 left join join subQuery4
Using some IDs or dates etc.
Those subqueries are complex themselves and may contain subqueries of their own. In no other programming context would I think that the logic for complex subqueries 1-4 belongs in line with my parent query that joins them all. It seems so straightforward that those subqueries should be defined as views, just like they would be functions if I were writing procedural code.
So why isn’t that common practice? Why do people so often write these long monolithic SQL queries? Why doesn’t SQL encourage extensive view usage just like procedural programming encourages extensive function usage. (In many enterprise environments, creating views isn’t even something that’s easily done. There are requests and approvals required. Imagine if other types of programmers had to submit a request each time they created a function!)
I’ve thought of three possible answers:
This is already common and I’m working with inexperienced people
Experienced programmers don’t write complex SQL because they prefer to solve hard data processing problems with procedural code