3 ms·

Because tables and SQL operators are really sets and acting on sets, so naturally operations take place on the set unless a subset is specified. Once you comple

Because tables and SQL operators are really sets and acting on sets, so naturally operations take place on the set unless a subset is specified. Once you completely and totally internalize this SQL is pretty intuitive, although I think that the clause order might be nicer in a different order. For example, I would like SELECT to be FROM, WHERE, SELECT. UPDATE could be UPDATE, WHERE, SET. But then DELETE would end up inconsistent... > If I just do 'SELECT *' and execute it, it does not rotate through all the tables. Because you did not specify what set you wanted to operate in, and the set of sets isn't meaningful because of schematic differences. > Same with 'SELECT from xyz' if I can not have it empty and return all. SELECT is asking what pieces of the subsets (rows) you want to display. If you don't ask for any, you don't get any. You are asking for the number of rows in xyz times zero. That's zero. You can write SELECT 1 FROM xyz and get 1 returned for each subset.

mhink 5y ago
Honestly, I think the problem is just that SQL isn't a particularly great language. This is an example of where the syntax and semantics are coupled so tightly that it's just gonna be confusing no matter what you do. My vote would be to not even bother with a "literate" kind of language.
sumtechguy 5y ago
Oh I get that, and that kind of was my point. I created those to show the inconsistency of the interface that SQL is. You have to internalize those inconsistent bits. Sometimes you specify, other times you get everything out of the different sets. The mental model of SQL is split. In some cases it is 'get these items from the set and if you dont you will get a syntax error' in other cases it is 'filter these items from the whole set and if you dont you get everything'. I see a lot of people new to SQL who that trips up. Some people are sayin 'well just for update/delete make the where condition not optional'. That at least seems fair and fixes the easy issue, but then that is one more inconsistency in the language that SQL is. I am saying just go one step further and make unconditional always. There are some who disagree. That is fine. My guess we get a mode in most SQLs with the update/delete one. I think that is at least a decent compromise.

mhink 5y ago

Honestly, I think the problem is just that SQL isn't a particularly great language. This is an example of where the syntax and semantics are coupled so tightly that it's just gonna be confusing no matter what you do. My vote would be to not even bother with a "literate" kind of language.

sumtechguy 5y ago

Oh I get that, and that kind of was my point. I created those to show the inconsistency of the interface that SQL is. You have to internalize those inconsistent bits. Sometimes you specify, other times you get everything out of the different sets. The mental model of SQL is split. In some cases it is 'get these items from the set and if you dont you will get a syntax error' in other cases it is 'filter these items from the whole set and if you dont you get everything'. I see a lot of people new to SQL who that trips up. Some people are sayin 'well just for update/delete make the where condition not optional'. That at least seems fair and fixes the easy issue, but then that is one more inconsistency in the language that SQL is. I am saying just go one step further and make unconditional always. There are some who disagree. That is fine. My guess we get a mode in most SQLs with the update/delete one. I think that is at least a decent compromise.