3 ms·

I'm an SQL hater in remediation. In a given week I might work with all of the following: SQL, C#, Python, JS (Kendo, Vue, React), XSL, bash, and more. I'm a q

I'm an SQL hater in remediation. In a given week I might work with all of the following: SQL, C#, Python, JS (Kendo, Vue, React), XSL, bash, and more. I'm a quick learner and I pick things up fast, always have, I've got a deadline and I don't have the time or capacity to fully internalize the minutiae of all the technologies I have to work with. In other words I depend on the tools to show me the options at my disposal and construct syntactically correct expressions. What I really dislike about SQL is common to most systems I dislike, where the tools aren't discoverable to me. In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause. So I have to build up my statement in this weird spiral pattern where I change something deeper in before I know what I can SELECT in the first place. The ability to give tables shorthand names with MS-SQL, i.e. `left join [dbo].[sometable] st` ...is very helpful but I gather this isn't common to all dialects. Working with XML in SQL is a nightmare as the tool cannot tell you whether you can do obvious things like pass an sql:variable into nodes() until you actually query the server. (spoiler: you can't.) I vastly prefer the functional programming approach to working with data e.g. C#'s lambda style linq. The tool shows me all the pieces I have to work with, and all I have to do is piece them together the right way.

throwaway_pdp09 6y ago
> In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls. The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary. If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them). As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.
- hobs 6y ago
  I would not throw someone at CTEs who is using SQL Server - just use temp tables for each component you would be CTE-ing, CTE's dont get any benefit from re-use except from a code perspective, whereas composing your sets into temp tables will often get you exactly what you want, individual sets that you can re-use throughout your code.
  - throwaway_pdp09 6y ago
    Very good point indeed. I'll add to that, that you can trivially examine temporary tables wheres CTEs don't have that transparency. Thanks! Edit: I'm going to clarify this. It is a mistake to throw CTEs at a beginner but to be clear, CTEs have very important advantages over the using temp tables to hold intermediate results. It comes down to efficiency. MSSQL only optimises within a single statement; it does not optimise across statements, so if you have a several queries comprising a single CTE, the optimiser has plenty to get its teeth into and may produce a much more efficient query plan. Also a query plan of a complex CTE can (depending on what you're doing) end up being a straightforward pipeline where one result feeds into another into another and finally gets spat out at the end. That can be very efficient. If you use temp tables you spool intermediate results (which may be large), re-read, spool into another temp table etc. If you're working with a large data set that can use up a lot of memory, and if it's large enough that it has to spill to disk... oh dear. So for beginners, yes, as you get more expertise, CTEs are the way to go (depending, of course, on various factors) (Conversely, temp tables do have a definite cardinality, whereas queries in a CTE are estimates and can be badly out leading to very poor query plans. So temp tables can work to your advantage here).
Viliam1234 6y ago
Reading SQL is like reading German, where the last word in a long sentence determines the meaning of the entire sentence. An SQL statement starts with "select ABC.XYZ", but you have no idea what it means, because only one screen later it is written that "ABC" is actually an alias for "T_ACCOUNT_BUSINESS_CREDITS" or something. The logical order would be "from ... where ... select ...". Imagine a programming language designed like SQL. It would look approximately like this: function foo(a) { return s; b = join(a); s = concatenate("[", b, "]"); by the way, a is list of strings, b is string, s is string; also, function "join" is imported from "lists", and "concatenate" is imported from "string" module; actually, don't return the result, just tell me how many characters it would have; }
- karmakaze 6y ago
  I don't know why people think this. After much thought, it's because they read chronologically which isn't how SQL works. It doesn't 'execute' in the written sequence. The entire statement is a logical one like a mathematical equation. You wouldn't think that the meaning of (a + b) * (c + d) implies that (a + b) has to be done first. Maybe in some computer languages it may like early C compilers. Given that: "select ABC.XYZ" is perfectly well formed, it's like a forward reference declaration where ABC is the alias for a table-like thing and XYZ is the alias for the column-like thing. I mostly read my SQL inside-out, starting with uncorrelated subqueries. Another pet peeve: using the keyword INNER or OUTER is just noise.
- Izkata 6y ago
  > Imagine a programming language designed like SQL. Okay: const { ABC, XYZ } = source_table.get(id = 5); Oh, wait...
contingencies 6y ago
May I propose, for your long expression of frustration, a simple fix. Don't use left join. Instead select x.x,x.y,y.x,y.z from x,y where x.x=y.z and ...; This is the same operation but without the screwup syntax. The whole notion of left join style syntax is an abomination and - excepting for those who have internalized it to the detriment of normal discourse - represents needless cognitive load.

throwaway_pdp09 6y ago

> In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls. The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary. If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them). As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.

hobs 6y ago
I would not throw someone at CTEs who is using SQL Server - just use temp tables for each component you would be CTE-ing, CTE's dont get any benefit from re-use except from a code perspective, whereas composing your sets into temp tables will often get you exactly what you want, individual sets that you can re-use throughout your code.
- throwaway_pdp09 6y ago
  Very good point indeed. I'll add to that, that you can trivially examine temporary tables wheres CTEs don't have that transparency. Thanks! Edit: I'm going to clarify this. It is a mistake to throw CTEs at a beginner but to be clear, CTEs have very important advantages over the using temp tables to hold intermediate results. It comes down to efficiency. MSSQL only optimises within a single statement; it does not optimise across statements, so if you have a several queries comprising a single CTE, the optimiser has plenty to get its teeth into and may produce a much more efficient query plan. Also a query plan of a complex CTE can (depending on what you're doing) end up being a straightforward pipeline where one result feeds into another into another and finally gets spat out at the end. That can be very efficient. If you use temp tables you spool intermediate results (which may be large), re-read, spool into another temp table etc. If you're working with a large data set that can use up a lot of memory, and if it's large enough that it has to spill to disk... oh dear. So for beginners, yes, as you get more expertise, CTEs are the way to go (depending, of course, on various factors) (Conversely, temp tables do have a definite cardinality, whereas queries in a CTE are estimates and can be badly out leading to very poor query plans. So temp tables can work to your advantage here).

Viliam1234 6y ago

Reading SQL is like reading German, where the last word in a long sentence determines the meaning of the entire sentence. An SQL statement starts with "select ABC.XYZ", but you have no idea what it means, because only one screen later it is written that "ABC" is actually an alias for "T_ACCOUNT_BUSINESS_CREDITS" or something. The logical order would be "from ... where ... select ...". Imagine a programming language designed like SQL. It would look approximately like this: function foo(a) { return s; b = join(a); s = concatenate("[", b, "]"); by the way, a is list of strings, b is string, s is string; also, function "join" is imported from "lists", and "concatenate" is imported from "string" module; actually, don't return the result, just tell me how many characters it would have; }

karmakaze 6y ago
I don't know why people think this. After much thought, it's because they read chronologically which isn't how SQL works. It doesn't 'execute' in the written sequence. The entire statement is a logical one like a mathematical equation. You wouldn't think that the meaning of (a + b) * (c + d) implies that (a + b) has to be done first. Maybe in some computer languages it may like early C compilers. Given that: "select ABC.XYZ" is perfectly well formed, it's like a forward reference declaration where ABC is the alias for a table-like thing and XYZ is the alias for the column-like thing. I mostly read my SQL inside-out, starting with uncorrelated subqueries. Another pet peeve: using the keyword INNER or OUTER is just noise.
Izkata 6y ago
> Imagine a programming language designed like SQL. Okay: const { ABC, XYZ } = source_table.get(id = 5); Oh, wait...

contingencies 6y ago

May I propose, for your long expression of frustration, a simple fix. Don't use left join. Instead select x.x,x.y,y.x,y.z from x,y where x.x=y.z and ...; This is the same operation but without the screwup syntax. The whole notion of left join style syntax is an abomination and - excepting for those who have internalized it to the detriment of normal discourse - represents needless cognitive load.