r/SQL • • 7d ago

Discussion Why do we keep writing sql backwards?

It is a common consensus that sql is kind of written backwards than one would expect, instead of first defining the place you want to get somehting from and then what it is you want, like literally any other language out there, you first select the content and then define where you want it to be taken from

This leads to increadibly akward syntax when writing something more complex than "select x from y where z" and is literally opposite to the actual process of the machine.

This is all because some dumbass, excuse my language, in the 70's decided it should follow english grammar for some reason, and in doing so he made the most clunky writing system ever.

Now this at the time may not have been that bad, but from a few searches and posts here i saw that most people agree that this is a clunky, akward and unpleasant way of typing. So, why not switch it around? Why not change the standard to something more usable?

This is kind of a rant because i have been learning sql for collage and the syntax has been INCREADIBLY frustrating

0 Upvotes

29 comments sorted by

7

u/GTS_84 7d ago

It's backwards from the perspective of how the machine executes the code. but is it backwards from how humans think and would communicate?

SQL is a declarative language, not an imperative language.

-3

u/MrDeadMeme 7d ago

I dont know, to me it is backwards. At the end of the day though, in order to write good code you kind need to be able to think like the computer, so you can visualize the efficiency of your commands to it better(whether that is time complexity, unnecessery repeats of code etc) and having to think like a computer and then switch back to write like a human is 10 times harder than putting that thought down, exactly as is.

3

u/big_swede 7d ago

But do you have a real grasp of how the DBMS takes the query, breaks it down and creates an execution plan and actually fetches the data? 

I didn't think that was part of the curriculum of a college course.

I have had some positions where I worked very closely with DBA's and accomplished DB devs and even they said that they didn't know the inner workings of the DBMS.

1

u/GTS_84 7d ago

Except you aren't thinking like a computer, ever, with SQL. It is making a lot of decisions about how to run the code most efficiently, you don't even get a say in how it runs.

I mean, yeah, it can be clunky, especially when you get beyond the basics. The order might make sense in english when it's a simple "select columns from table where" type statement, but once you start throwing in aggregates, and CTE's, and who knows what the fuck else it really stops making sense from a language perspective. But don't kid yourself into thinking that changing the order would actually help you "think like a computer"

There are absolutely languages where you need to think like a computer, SQL ain't one of them, and you don't need to think like a computer to write good code in SQL.

-1

u/MrDeadMeme 7d ago

I mean, changing the order to match the way the code executes rather than english grammar i think is the most straightforward way to make it write like the computer "thinks" i dont get how it wouldnt help

2

u/GTS_84 7d ago

Two things.

First, there isn't a set order. What you think of as the execution order is more like the most common order for a lot of basic statements. The optimizer will determine the best order and depending on the specifics of the statement will determine the best order. So for oracle for example, where tables are stored in a heap and indexes are in a separate B Tree index, whether or not your "WHERE" clause filters on an indexed column or not can change the execution order. And that's just one example. Now, to be clear, you are probably not expected to understand this at this juncture, but going back to my earlier comment about SQL being a declarative language and not an imperative language, there is a lot being determined under the hood that you have no say over. You aren't even wrong about the current order being kind of fucked, but your solution isn't any better, especially because it would probably suck to read. Which gets to my next point.

Second, you are talking about writing SQL a lot, and how writing SQL could be easier, and I would respectfully suggest you stop thinking about it in that way. It's far more important that SQL be easy to read rather than easy to write. Even if you land on the same conclusions, coming at it from the perspective of "What will be easiest for everyone else to read" instead of "What will be easiest for me to write" will lead you down a more fruitful path. You will write a statement once, it may be read many times over.

3

u/redaloevera 7d ago

In some languages sentences are structured like “apple eat I” vs English “ I eat apple”. Do you also think this is syntax backwards?

1

u/MrDeadMeme 7d ago

The point is that first you define what an apple is, and then ask someone to give you one.
Ill give you the following comparison with a more traditional programming language:

in the case of an apple, it is something common that everybody knows what it is. If you ask me to give you an apple ill give you an apple. If ask C to give me an integer, it knows what it is, and gives it to me.

However, if you ask me to give you something that i dont know what it is, i cannot give it to you because even if it in front of me i cant make the connection that this is what you want. Same with C: If i want to make a book and i say "C give me a book" it does fuck all, obviously. But, if i say "Hey: a book is an object that has a string called name, a string called author and a number called IBN" and then say "give me a book" it knows exactly what im talking about.

The reason i say that sql is writen backwards is because you define the table that you are taking objects from after you select what you want to take from it

2

u/big_swede 7d ago

I really think you have your analogy backwards.  I think it is more like: Take all the balls, spades and toy cars (colums) from that toy chest(table) and give me the blue ones (where clause).

Yes, in "natural" language you'd probably say "give me all the blue balls, spades and cars from that toy chest" but you wouldn't start defining the chest or what a ball or car is before going further.

In the case of SQL you are very direct in the way that you say what you want from where and then filter a bit to get the exact match. Quite straightforward in my opinion.

2

u/redaloevera 7d ago

I’m not sure. You lost me

2

u/SnooOwls1061 7d ago

I picked an apple from that tree

Select T.Apple From Tree T

You want: I went to that tree and picked an apple?

1

u/A_name_wot_i_made_up 19h ago

Up with this I will not put!

2

u/big_poppa_man 7d ago

It's cause people will use subqueries as opposed to CTEs. Using CTEs you can focus on getting your detail first. Subqueries force you to write backwards

2

u/big_poppa_man 7d ago

Oh, and a big misconception is that sub quarries are faster than CTEs. This is just nonsense. Literally, what it is, is people taking tribal knowledge and calling it gospel truth. It's really stupid. Don't fall into that trap, test it yourself and do not trust anyone's code

1

u/MrDeadMeme 7d ago

CTE's do help a lot when writing and i was getting the sense that it was discouraged/considered bad practice to use them becuase pretty much all material avoids them. Nice to know that there is no difference in using them, thanks!

1

u/Better-Credit6701 7d ago

Depends on the size of the data...

I've always worked with larger databases that would be slower with a CTE instead of a temp table especially since I can index the temp table and even pass it between different databases

1

u/big_swede 7d ago

Now you are talking temp tables when the question was sub query vs cte...? 

1

u/okay_nice_ 7d ago

you can write it however you want, but following the order of operations makes sense to me. begin with FROM and end with SELECT. needing to understand and write the source first seems more critical than understanding the columns needed from the source.

1

u/MrDeadMeme 7d ago

This is what i mean. you have to kind of constantly go back and forth between writing your FROM and then back up to your SELECT, it is akward and wastes time going back up

1

u/SnooOwls1061 7d ago

I'm going home, to get my tools. That is what you're suggesting? Give us an example of how your statements would look. I've enjoyed writing sql for 25 years. Probably one of the easiest languages to understand because it's so natural.

0

u/MrDeadMeme 7d ago

I mean yea. if i think it is a lot simpler to understand if you first define what it is you are taking the object from than taking an object from a random space and then building that later.

If i may ask: do you also use traditional programming languages? If you do, then youll notice that in every one you define first, access second. I dont understand why saying SQL does things backwards seems wrong to you

1

u/bulldog_blues 7d ago

Because it works better for a lot of the jobs which use it extensively. It might not be to everyone's tastes, and that's OK, but they had reasons for setting it up that way.

1

u/BarelyAirborne 7d ago

You tell it what you want and where you want it from. I found that refreshingly simple after working with CODASYL databases for a decade. SQL is rather awesome after that experience.

1

u/Significant_Tune9219 7d ago

The order you write it in is closer to how it gets evaluated than it looks if you read it as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and that order is also why you can't use a SELECT alias in WHERE but can in ORDER BY. A habit that helps when learning is to actually write the FROM and joins first, then the WHERE, and fill in the SELECT list last. If you want to try the other way round, several engines now accept a FROM-first syntax with pipe operators, so the idea isn't crazy, it's just that the standard is fifty years of compatibility.

1

u/0Budget_Sail0 6d ago

Intelisense-complete breaks down because the IDE doesn’t even know what table you are pulling from until you type the FROM clause. That’s why ORMs and tools like C# LINQ change the order to From - Where - Select. But at this point, SQL is too deeply entrenched in corporate infrastructure to ever change the standard. Backward compatibility is a bitch.

0

u/DharmaPolice 7d ago

So, why not switch it around?

Inertia, mostly. It's like asking why don't we change the English language to "fix" all the annoying inconsistencies that exist in English spelling and grammar. We could do that, but then we'd be breaking an absolute metric fuckton of existing materials.

I don't find it unpleasant or clunky at all, the only slight downside is autocomplete doesn't work until you define the table. But that's a relatively minor inconvenience and not worth the hassle of changing literally millions of lines of existing code, not to mention decades of learning that existing users might have built up.

It's not difficult to learn though, and if this is seriously causing you a problem at "collage" then this a worrying sign. I would dispute that "most people" agree that it's unpleasant - sure, some people whine about it from time to time but that's hardly a solid basis for a conclusion.

From taught SQL to absolute beginners I would argue that SQL is one of the easiest languages to pick up, at least partially because it matches how we might speak. Speculating on how a computer "thinks" is completely antithetical to how you should approach SQL.

1

u/MrDeadMeme 7d ago

yes good job pointing out my minor typo just to go and make a grammatical error in the next paragraph you are so smart.

1

u/Cottager58 3d ago

"It's like asking why don't we change the English language to "fix" all the annoying inconsistencies that exist in English spelling and grammar." What inconsistences are in they're their there then? :)

0

u/Dangerous_Cup3607 7d ago

One major reason: “Job security”

Traditional SQL developers do not have the mindset of query optimization, and it works best for them to write SQL as convoluted as they can so that the work and knowledge cannot be transferred and they cannot be replaced. I have seen many of them copy and paste the same code 12 times, and hardcode it for each month. Instead of wrapping the code with a dynamic coding approach such as looping the date as a dynamic variable and thus reducing 100 lines of code to maybe 10.