Apparently, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for non-IT people. Simple SQL statements read like English, and even people without any programming experience can write them.

However, the language becomes clumsy as query needs become even slightly more complicated. It often needs hundreds of rows of multilevel nested statements to achieve a computing task. Even professional programmers often find it hard to write, let alone the non-IT people. As a result, such computing tasks become the popular main question in programmer recruitment tests of many software companies. In real-world business situations, the size of SQL code for report queries is usually measured by KBs. The several-line SQL statements only exist in programming textbooks and training courses.

SQL problem analysis

What is the reason behind the sheer bulk of the code? Let’s try to find the answer, that is, SQL’s weaknesses, through an example.

Suppose we have sales performance table sales_amount consisting of three fields (date information is omitted to make the analysis simpler):

We are trying to find salespeople whose sales amounts rank in top 10 in terms of both air conditioners and TV sets.

The task is not difficult. It is easy for us to think of the following natural computing process:

1． Sort the sales performance table by sales amount of air conditioners and get the top 10;

2． Sort the sales performance table by sales amount of TV sets and get the top 10;

3． Perform intersection operation on step 1 and step 2’s result sets to get the final result.

To implement the algorithm in SQL:

1． Find salespeople whose sales amounts rank in top 10 for air conditioners. The code is fairly simple:

select top 10 sales from sales_amount where product='AC' order by amount desc 

2． Find salespeople whose sales amounts rank in top 10 for TV sets. Same action is performed:

select top 10 sales from sales_amount where product='TV' order by amount desc 

3． Calculate the intersection between result sets of step 1 and step 2. As SQL does not support step-by-step coding, result sets of the previous two steps cannot be retained and the statements need to be copied. Here the code becomes a little complicated.

select * from 
    ( select top 10 sales from sales_amount where product='AC' order by amount desc ) 
intersect 
    ( select top 10 sales from sales_amount where product='TV' order by amount desc ) 

This is just a 3-step simple computation, but there are a large number of computations in the real-world analytic scenarios that involve about a dozen of steps. We can imagine the complexity degree of their code.

Do not support step-by-step coding – that is the first major SQL weakness. A stepwise procedure for approaching a complex computation can considerably reduce coding complexity. And trying to approach one wholesale will only make it much harder or more complicated to code.

We can just imagine how much a primary school student would feel frustrated when they were required to solve a word problem using only one formula (though there are always smart children who can work it out).

SQL does not support step-by-step coding, but stored procedures written in SQL can. So, can stored procedures handle the above task conveniently and simply?

Let’s just ignore the complex technological environment stored procedures require and the incompatibility caused by differences of databases, and look at how, in theory, the SQL stored procedure simplifies the computation through stepwise coding.

1． Find salespeople whose sales amounts rank in top 10 for air conditioners. The statement is the same as above, but its result set will be stored specifically for use in step 3. As SQL can only store a set of data with a table, we need to create a temporary table:

create temporary table x1 as 
    select top 10 sales from sales_amount where product='AC' order by amount desc 

2． Find salespeople whose sales amounts rank in top 10 for TV sets using the similar statement:

create temporary table x2 as 
    select top 10 sales from sales_amount where product='TV' order by amount desc 

3． Calculate intersection of result sets of step 1 and step 2. The complicated first two steps lead to a simple last step:

select * from x1 intersect x2 

Coding step by step displays clear thinking but the temporary tables make the computing process complicated still. The intermediate temporary result sets are common in batch structured data computations. If we create a temporary table for each one, efficiency will be low and code will be nonintuitive.

And SQL does not allow set-type field values, such as temporary tables. This makes some computations not achievable even if we are willing to endure the complex processing.

Suppose we need to find salespeople whose sales amounts for all products rank in top 10, it’s easy to think up the following algorithm based on that for the previous task:

1． Group the original table by product, sort each group, and find top 10 records meeting the specified condition in each group;

2． Calculate intersection of all top 10 records.

As we do not know how many products there are in advance, we need to store the grouping result in a temporary table, where one field will store members in each group. But as SQL does not support set-type values, the solution becomes infeasible.

If we have window functions at hand, we can switch to another route. It will group the original table by product, calculate the number of appearances of every salesperson in the top 10 sales amounts of each group, and find those whose total appearances are equal to the number of products – they are the ones whose sales amounts rank in top 10 for all products.

select sales 
from ( select sales, 
     from ( select sales, 
                   rank() over (partition by product order by amount desc ) ranking 
            from sales_amount) 
     where ranking <=10 ) 
group by sales 
having count(*)=(select count(distinct product) from sales_amount) 

But such a SQL query is beyond most users.

Even then, not all databases support window functions. In that case, we can only write loops using the stored procedure to circularly get the top 10 salespeople in terms of sales amounts in each product, and calculate intersection between the current result set and the previous one. The process is not simpler than the program written in a high-level language, and still involves the complexity of creating temporary tables.

The above shows the second SQL weakness – insufficient set-orientation. Though SQL has the concept of sets, it does not offer them as a basic data type, resulting in roundabout algorithms and code for the large number of set-oriented calculations.

The keyword top is used in the above SQL sample programs. Actually, there isn’t such an operator in relational algebra (but it can be constructed using a series of other operations), and the code is not standard SQL.

Let me show you how difficult it is when the top keyword is not available for finding top N.

Here’s the general way of thinking: For each member, get the number of members where the sales amounts are greater than the current amount, define ranking of the current salesperson according to the number, and get members whose rankings are not greater than 10. Below is the SQL query:

select sales 
from ( select A.sales sales, A.product product, 
             (select count(*)+1 from sales_amount 
              where A.product=product AND A.amount<=amount) ranking 
       from sales_amount A ) 
where product='AC' AND ranking<=10 

Or

select sales 
from ( select A.sales sales, A.product product, count(*)+1 ranking 
       from sales_amount A, sales_amount B 
       where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount 
       group by A.sales,A.product ) 
where product='AC' AND ranking<=10 

Even professional programmers find it hard to write. The code is too complicated for such a simple top 10 computation.

Even if SQL supports the keyword top, it can only solve top N problem conveniently. If the problem becomes a bit more complex, such as getting members/values from the 6th to the 10th and finding salespeople whose sales amounts are 10% higher than their directly next, difficulty still exists.

This is due to SQL’s another key weakness – lack of order-based syntax. SQL inherits mathematical unordered sets, which is the direct cause of difficulties in handling order-based calculations that are prevalent in real-world business situations (such as calculating link relative ratio, YOY, top 20%, and rankings).

SQL2003 standard adds window functions to try to improve the computing ability for dealing with order-based calculations. They have enabled simpler solutions to the above computing tasks and helped mitigate this SQL problem. However, the use of window functions is usually accompanied by nested queries, and the inability to let users access members of a set directly according to their positions leaves many order-based calculations hard to solve.

Suppose we are trying to find the gender ratio among the above top salespeople by calculating the number of females and that of males. Generally, the gender information of salespeople is recorded in employee table instead of the sales performance table, as shown below:

As the list of top salespeople is available, our first thought might be finding their genders from the employee table and then count the numbers. To achieve this cross-table query, SQL needs a table join. So, the SQL code following the above top 10 task is:

select employee.gender,count(*) 
from employee, 
    ( ( select top 10 sales from sales_amount where product='AC' order by amount desc ) 
    intersect 
    ( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A 
where A.sales=employee.name 
group by employee.gender 

Only one table join has already made the code complicated enough. In fact, related information is, on many occasions, stored in multiple tables and often of multilevel structure. For instance, salespeople have their departments and the latter has managers, and we might want to know the managers under whom those top salespeople work. A three-table join is needed to accomplish this, and it is not easy to write smooth and clear WHERE and GROUP for this join.

Now we find out the fourth SQL weakness – lack of object reference mechanism. In relational algebra, the relationship between objects is maintained purely by foreign keys match. This results in slow data searching and the inability to treat the member record in the related table pointed by the foreign key directly as an attribute of the current record. Try rewriting the above SQL as follows:

select sales.gender,count(*) 
from (…) // … is the SQL statement for getting the top 10 records of salespeople 
group by sales.gender 

Apparently, this query is clearer and will be executed more efficiently (as there are no joins).

The four SQL key weaknesses shown through a simple example are causes of hard to write and lengthy SQL statements. The process of solving business problems based on a certain computational system is one that expresses an algorithm with the syntax of a formalized language (like solving word problems in primary school by transforming them into formalized four arithmetic operations). The SQL defects are great obstacles to translation of solutions computing problems. In extreme cases, the strangest thing happens – the process of converting algorithms to syntax of a formalized language turns out to be much harder and more complicated than finding a solution.

In other words, using SQL to compute data is like using an assembly language to accomplish four arithmetic operations – which might be easier to understand for programmers. A simple formula like 3+5*7 will become as follows if it is written in an assembly language, say X86:

mov ax,3 
mov bx,5 
mul bx,7 
add ax,bx 

Compared with the simple formula 3+5*7, the above code is complicated to write and hard to read (it is even more difficult when fractions are involved). Though it may be not a big deal for veteran programmers, it is almost unintelligible for most business people. In this regard, FORTRAN is a great invention.

Our examples are simple because I want you to understand my point easily. But real-world computing tasks are far more complicated, and users will face various SQL difficulties. Several more lines here and a few more lines there, it is therefore no wonder that SQL generates multilevel nested statements of hundreds of lines for a slightly complicated task. What’s worse, often the hundreds of lines of code are a single statement, making it hard to debug in terms of engineering aspect and increasing difficulty in handling complex queries.

More examples

Let’s look at SQL problems through more examples.

In order to simplify the SQL statement as much as possible, the above sample programs use many window functions and thus the Oracle syntax that supports window functions well. Syntax of the other databases will only make the SQL statement more complicated.

Even for these simple tasks that are common in daily analytic work, SQL is already sufficiently hard to use.

Non-stepwise coding

Coding a complex computation step by step can considerably reduce the complexity, while trying to accomplish it wholesale will only make it even more difficult.

Task 1: Find the number of female employees who come from Beijing in the sales department.

Count employees in the sales department:

select count(*) from employee where department='sales' 

Count employees who are Beijingers:

select count(*) from employee where department='sales' and native_place='Beijing' 

Count female employees who are Beijingers:

select count (*) from employee 
where department='sales' and native_place='Beijing' and gender='female' 

The natural solution is to select records of employees in sales department and count them, get employees who are Beijingers and count them, and then find female employees and count the number. Each query is based on result of the previous query, generating simple and efficient code. Yet, SQL’s non-stepwise coding cannot reference the previous result set for use in the current query, and writes the query condition for each query.

Task 2: Select a female employee and a male one from each department to form a team to take par in the game.

with A as 
       (select name, department, 
              row_number() over (partition by department order by 1) seq 
        from employee where gender=‘male’) 
     B as 
        (select name, department, 
              row_number() over(partition by department order by 1) seq 
        from employee where gender=‘female’) 
select name, department from A 
where department in ( select distinct department from B ) and seq=1 
union all 
select name, department from B 
where department in (select distinct department from A ) and seq=1 

The non-stepwise coding leads to bloated code and low computing efficiency, as well as extremely roundabout algorithms sometimes.

Here is the intuitive solution. Loop through each department, from which a female employee and a male employee are selected to add to the result set if the department has employees of both genders. SQL does not support getting the final result set step by step (unless it turns to the stored procedure). It will, in this case, switch to a hard route. It selects male employees and female employees respectively from each department, get members from each result set whose departments exist in the other, and finally, union them.

If it were not for WITH subclause and window functions, the SQL statement would be unreadable.

Unordered sets

Order-based calculations are prevalent in batch data processing (such as getting top 3 or record/value in 3rd position, and calculating link relative ratio). SQL switches to an unusual way of thinking and take a circuitous route because it cannot perform such a calculation directly thanks to its inheritance of the concept of mathematical unordered sets.

Task 3: Find employees whose ages are equal to the median.

select name, birthday 
from (select name, birthday, row_number() over (order by birthday) ranking 
      from employee ) 
where ranking=(select floor((count(*)+1)/2) from employee) 

Median calculation is common, and the process is simple. We just need to sort the original set and get the member at the middle position. SQL’s unordered-sets-based computational mechanism does not offer position-based member access method. It will invent a field of sequence number and select the eligible members through a conditional query, where subqueries are unavoidable.

Task 4: Find the largest number days when a stock rises consecutively.

select max (consecutive_day) 
from (select count(*) (consecutive_day 
      from (select sum(rise_mark) over(order by trade_date) days_no_gain 
            from (select trade_date, 
                         case when 
                              closing_price>lag(closing_price) over(order by trade_date) 
                         then 0 else 1 END rise_mark 
                from stock_price) ) 
     group by days_no_gain) 

Unordered sets also lead to tortuous ways of solving problems.

Here is the general way of doing the task. Set a temporary variable to record the number of consecutive rising days with the initial value as 0, compare the current closing price with the previous one, reset the variable’s the current value as 0 if the price does not rise and add 1 to it if the price rises, and get the largest number when the loop is over.

SQL cannot express the algorithm and it gives an alternative, which first counts the non-rising frequency for each date from the initial one to the current one. The dates that have the same frequency contain prices rising consecutively. Then it groups these dates to get continuously rising intervals, counts members in each, and finds the largest number. It is extremely difficult to understand and even more hard to express.

Insufficient set-orientation

There is no doubt that sets are the basis of batch data processing. SQL is a set-oriented language, but it can only express simple result sets and does not make it a basic data type to extend its application.

Task 5: Find employees whose birthdays are on the same date.

select * from employee 
where to_char (birthday, ‘MMDD’) in 
    ( select to_char(birthday, 'MMDD') from employee 
      group by to_char(birthday, 'MMDD') 
      having count(*)>1 ) 

The original purpose of grouping a set is to divide it into multiple subsets, so a grouping operation should have returned a set of subsets. However, SQL cannot express such a “set of sets” and thus cannot help forcing an aggregate operation on the subsets to return a regular result set.

At times, what we need isn’t aggregate values but the subsets themselves. To do this, SQL will query the original set again according to the grouping condition, which unavoidably results in a nested query.

Task 6: Find students whose scores of all subjects rank in top 10.

select name 
from (select name 
      from (select name, 
                   rank() over(partition by subject order by score DESC) ranking 
            from score_table) 
      where ranking<=10) 
group by name 
having count(*)=(select count(distinct subject) from score_table) 

The set-oriented solution is to group data by subject, sort each subset by score, select top 10 from each subset, and calculate intersection between the subsets. As SQL’s inability to phrase “a set of sets” and support intersection operations on an indefinite number of sets, the language takes an unusual route to achieve the task. It finds top 10 scores in terms of subjects using a window function, group the result set by student, and find the group where the number of students is equal to the number of subjects. The process is hard to understand.

Lack of object reference method

A SQL reference relationship between data tables is maintained through matching foreign key values. Records pointed by these values cannot be used directly as an attribute of the corresponding records in the other table. Data query needs a multi-table join or a subquery, which is complicated to code and inefficient to run.

Task 7: Find male employees whose managers are female.

Through multi-table join:

select A.* 
from employee A, department B, employee C 
where A.department=B.department and B.manager=C.name and 
      A.gender='male' and C.gender='female' 

Through subquery:

select * from employee 
where gender='male' and department in 
    (select department from department 
     where manager in 
          (select name from employee where gender='female')) 

If the department field of the employee table is the foreign key pointing to records of the department table and the manager field of the department table is the foreign key that points to records of the employee table, the query condition can be written in the following simple, intuitive and efficient way:

where gender='male' and department.manager.gender='female' 

SQL can only use a multi-table join or a subquery to generate difficult-to-understand statements.

Task 8: Find the companies where employees obtained their first jobs.

Through multi-table join:

select name, company, first_company 
from (select employee.name name, resume.company company, 
             row_number() over(partition by resume. name 
                               order by resume.start_date) work_seq 
      from employee, resume where employee.name = resume.name) 
where work_seq=1 

Through subquery:

select name, 
    (select company from resume 
     where name=A.name and 
           start date=(select min(start_date) from resume 
                       where name=A.name)) first_company 
from employee A 

SQL is also unable to treat the sub table as an attribute (field) of the primary table because it lacks object reference method and has inadequate set-orientation. A query on the sub table uses either a multi-table join, which makes the statement particularly complex and needs to align the result set to records of the primary table in a one-to-one relationship through a filtering or grouping operation (since records of the joining result set has such a relationship with the sub table), or a subquery that calculates ad hoc the subset of records of the sub table related to each record in the primary table one by one – which increases amount of computations (the subquery cannot use the WITH subclause) and coding difficulty.

SPL as the solution

SQL problems need to have a solution.

Actually, the above analysis implies a way out. That is, designing a new language that gets rid of those SQL weaknesses.

And this is the original intention of creating SPL.

SPL is the abbreviation for Structured Process Language while SQL’s full name is Structured Query Language. It is an open-source programming language intended to facilitate structured data computations. SPL emphasizes step-by-step coding, and supports ordered sets and object reference method to achieve complete set-orientation, reducing the difficulty of “algorithm translation” sharply.

Here we just present SPL code of the 8 tasks in the previous section, giving you a glance of the language’s elegance and conciseness.

Task 1

SPL keeps a set of records as an intermediate variable, enabling a step-by-step query process.

Task 2

SPL obtains the final result step by step using its own efficient programming logic.

Task 3

Task 4

It is easy for SPL to code an intuitive and direct algorithm.

Task 5

SPL keeps result set of the grouping operation to further process it as it handles a regular set.

Task 6

SPL writes the code smoothly as the intuitive algorithm unfolds.

Task 7

With the support of object reference, it is convenient for SPL to access a field of the record pointed by the foreign key as it gets one of its original fields.

Task 8

SPL allows treating a set of records of the sub table as a field of the primary table and accesses it in the same way of getting its other fields, avoiding repeated computations on the sub table.

SPL has an intuitive IDE that offers convenient debug functionalities to track each step for processing a query, making coding even easier.

For a computation within an application, SPL offers the standard JDBC driver to be integrated with the application, such as JAVA, as SQL does:

… 
Class.forName("com.esproc.jdbc.InternalDriver"); 
Connection conn =DriverManager.getConnection("jdbc:esproc:local://"); 
Statement st = connection.(); 
CallableStatement st = conn.prepareCall("{call xxxx(?,?)}"); 
st.setObject(1, 3000); 
st.setObject(2, 5000); 
ResultSet result=st.execute(); 
… 

Only details.

• The list of expressions in SELECT is in a wrong place. We can say “the SELECT clause of the SELECT statement”, which is frankly ridiculous.
• 2 types of NULL should exist. Somewhere, NULL semantics clearly mean “no value”, but other times they clearly mean “unknown value”. If the meaning is not clear, it’s better to have no NULL at all.
• Columns should not be NULLable by default.
• But having only the primary key column non-NULLable by default is even worse.
• No simple syntax to change a single characteristic of a column (ie: NULLable, comment…).
• FROM t1, t2 syntax should not be supported, because it is a less powerful alternative to JOIN.
• CTE syntax is too complicated.
• Too many way to do the same things.

On the other side, it is:

• Almost plain English.
• Generally simple to write and understand.
• The only standard-ish language for databases.
• Inspired but free from the influence of Date and friends.

From a Software Engineering perspective, SQL is sorely lacking in some areas that more general programming languages possess…

1. No provision for symbolic constants (yes, you can declare variables, but that’s hardly the same thing).
2. No real notion of modularization or logic hiding/abstraction.
3. SQL Functions have too much overhead to be readily used as a means to simplify and genericize queries.
4. Relatedly, some other means of in-line parameterization of similar code would allow queries to be greatly simplified. I envision something like the C/C++ preprocessor, though I realize that comes with its own perils.

Queries getting into production with really long lines, like a couple hundred characters. Yes, the DB engine can parse it just fine, but what about the poor SOB who has to maintain it?

Crazed capitalization. Camel case is fine for Java and other languages, but snake case works best in SQL (IMHO). I don’t mind capitalizing key words, or capitalizing everything, or nothing, but PLEASE don’t DO THIS kind of CRAP in the SQL code BASE BECAUSE it LOOKS f’ing WEIRD!!!!! And, it’s terribly hard to read.

Lack of aliases. When SQL gets really complicated, it helps knowing which tables contain the columns referenced, so you can use a table name or alias. For crying out loud, use the alias instead of the name! With modern databases allowing lengthy object names, I really don’t need to see a column name like “MY_REALLY_REALLY_REALLY_REALLY_REALLY_LONG_TABLE_NAME.FIELD1” in the SQL. Alias the table to something meaningful, like ‘LTN’, or even ‘LNTABLE’.

Oh, there are few things:

1. Its not tab-completion friendly. To fix that, from section should go first
   Select <tab> …. well, there is nothing to work with here, while:
   From some_table select <tab> could provide useful completion.
   (“from” should also be renamed to “using”.
2. Its error prone. Again, section order is the culprit. Using some_table where conditions delete; would be much safer than what is there now.
3. Its not very well standardized. Even most essential things like auto_increment have different syntax or semantics between databases.
4. Nulls are allowed by default. It should be otherwise.
5. Schema migration lack db support. For example if you define an index on a field, typically you are required to name it and people name it after the field name(s). Renaming fields later will not suggest, require or perform renaming of the index leaving you with not-so-helpful index names. Database does not store history of migrations, so you may accidentally add or miss one when you try to manage them.
6. Standard tooling sucks terribly.

I’m going to say TRIGGERs.

Lots of people love triggers. And then they wonder why their database performance is awful, or why they get a particular answer from a query that ends up using a trigger that they didn’t expect.

Triggers often embed what should be application logic in the database, and can cause all manner of headaches when trying to test and deploy application upgrades if you’re not Very, Very Careful in how the triggers are managed and maintained.

VIEWs can be similarly abused, and can cause related problems, particularly if developers fall in love with “abstraction” and start creating “view pyramids” or views on top of views.

Fundamentally, SQL was designed by people who weren’t relational database experts and did not understand the principles of Codd.

For example SQL SELECT does two things from relational algebra: select and project. Should they be separate notions and map to relational algebra better? It is debatable.

C.J. Date has written about the many problems in SQL, including non-orthogonality. Many of his points might seem subtle and difficult to understand. However, these result in less convenience for programmers and language constructs that are difficult to understand and remember.

SQL designed by people who didn’t understand Codd’s work:

Unfortunately, this is typical in the computing industry. While some think it is important to get things right and not burden others with poor designs, there are others who want to rust to market, benefit from market power and lock in, but leave burden to thousands and millions of others.

Chris Date and Hugh Darwen have designed an alternative in Tutorial D. Dave Voorhis — one of the good Quora writers — actually has an implementation.

One of the worst from a developer perspective is that it is declarative. It is a fantastic feature the the language is parsed and optimised better than most developers could figure out, but it requires an optimiser, and often that optimiser needs heuristics to work. This means if you are dealing with databases with large databases, you cannot predict how queries will perform given another data distribution. The problem is that the query planner will sometimes change plans, to a “better” execution plan. Sometimes the query planner is right, and no one notices, but sometimes it is really poor - and executions go from sub second query to minutes or half hour queries. A consultant is flown in with a heavy price tag, to improve performance. Queries are re-written and forced to a specific behaviour with e.g. with hints and or pinning some query plans. And performance is almost restored.
If SQL was not declarative, it would have been easier to measure performance. Just measure a couple of points when data fits in RAM, and a couple of points when it no longer fits in RAM. And you would have a rough idea of the gradual performance degradation due to large amounts of data.

With SQL databases being as smart as they are, they provide excellent performance until they suddenly don’t. And the initial performance is not due to a developer having investigated the smartest way to perform a query. all that thought is often done in retrospect, when the database can no longer find smarter query plans than you can. But even at this point, it might just be the databases statistics that are out of date.

As someone who has done some DB kernel programming, my biggest frustration is when DB engines are Doing Something Stupid. It makes me want to download the source code and fix it - although my employers typically won’t let me :)

Some stupidities:

• MySQL still can’t do “constant subquery IN” without re-running the inner subquery over and over. While there’s a simple fix - convert the IN to an EXISTS - this query form is common enough and the performance hit is bad enough that it really should be fixed. Converting IN to EXISTS often results in a query going from O(many minutes) to O(milliseconds).
• Lots of ORs with constant values should be converted into a constant IN by the query optimizer by MySQL. But it isn’t.
• And random other ones :)

Oracle should do the world a big fat favor and implement hashjoins in MySQL. The machinery is pretty much already there, as MySQL has temp tables and memory tables - someone just has to do it…

Some other ones:

• “Clever” queries that people think are Awesomely Kewl because they use a bunch of interview-question features but that Suck when they’re actually deployed using a real dataset. People get annoyed when I unpack these into more icky-looking query packages that use temp tables and such things, but actually complete in a reasonable time.
• When I forget to use a left or right-join somewhere in the midst of a big ugly query and don’t realize it until someone else realizes that the results are wrong. As these are typically in internal report queries, they aren’t tested as carefully as customer-facing code, so this happens more often than I’d like.

A thing I like is that SQL has a pretty simple quality metric: fast is better than slow.

You might like my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming, which I wrote about exactly this subject.

I suppose in general, programmers make mistakes in SQL of the same root cause that they do in other code.

• Neglecting to use powerful features built into the language, like understanding correct use of JOIN, GROUP BY, HAVING, UNION.
• Not applying best practices of architecture and logical organization. With respect to SQL, this means normalization and choosing data types.
• Assuming that there’s no cost to accessing data. Neglecting to test how their code works when processing large-scale collections of data.
• Trying to do too much work on a modestly-sized database server. Not enough memory, storage capacity, CPU cores.
• Trying to do too much work in the database all at once, when you should be diversifying with caching and message queues.
• Neglecting a backup & restore strategy.
• Running dynamic SQL queries that include untrusted content (SQL injection vulnerabilities).

First I would stop writing SQL commands on their own to run scripts. You can easily set up code in your favorite language, and at worst just run a SQL command against the database as a string. Sometimes it’s less headache to play around with everything in a programming language instead of just SQL. You can put data into objects and iterate through them, you can talk to other API’s, you have way more flexibility over your variables, etc. It shouldn’t take long to make a standalone python script which connects to your database and you can run any SQL command you want directly from your python script, giving you access to all the python libraries and features for your scripts.

Second, use TRANSACTIONS. With any decent sized database you don’t want to complete something halfway and then have it fail. It can make things a headache.

Third, always backup the database before you write something. Always write the WHERE clause first before you even type the words DELETE or UPDATE.

Fourth, read about it sometimes. It’s easy to think you know it all just because you know the basics of INSERT, UPDATE, SELECT, and DELETE, but there’s a lot more. JOINS, GROUP BY’s, Subqueries, they can save you a ton of time. Again, these can all be covered by writing solid backend code in something like python, where you just iterate through your data or write multiple queries in the same script. Never know when you’ll need a solid RegularExpression too.

Fifth, just to add to my point of always just putting the data handling in a real programming language and then passing SQL queries in, you to use a real debugger. Export your data to excel and manipulate it with python or java. Your code can just grab all the data at once and put it into predefined objects in the programming language, and now you have way more tools at your disposal. No need to figure out how to do it with JUST queries.

The longest SQL script I have seen to date was 10,548 lines long. This particular script was being used to evaluate a messy string input and someone had developed several huge CASE statements to attempt to evaluate every possible incoming scenario. It was later rewritten to use regular expressions to do the same thing, but use considerably less lines.

Extremely long SQL scripts are sometimes the result of inexperience. To some extent, this was the case with the huge CASE statements vs using regular expressions.

I remember when I was just starting out, I wrote a huge SQL query (which I was ignorantly very proud of at the time) that had 8 LEFT OUTER JOINs to the same huge subquery with 1 single predicate that was different between each of the 8 subqueries. I didn’t know about Views, Temporary Tables, Common Table Expressions (CTEs), Table-Valued Functions (TVFs), or other alternatives to more gracefully do what I needed to do. I could have just used a Table-Valued Function or a Temporary Table and turned that 1,000 line SELECT command into something like a 50-line SELECT command.

Extremely long SQL scripts can also be the result of neglecting to refine and tune the code. Some developers think their code is ready to go to Production as soon as it correctly implements the business requirements. But after the first pass, the code is rarely as optimal as it can be. Code that is truly fully-baked and hardened has had several passes and refinements.

Another consideration is the type of environment. OLTP environments will generally have short SQL scripts, and Data Warehouses (OLAP environments) will tend to have longer, more involved SQL scripts. This is just the nature of the different environments.

Can't say that i or anyone “work in databases”. At most we work WITH them. They are after all just tools…although of the multiuser and persistant kind.

My biggest problems are…

• Sometimes complex queries runs for very long times. Hours or forever. They need to be implicitly broken up to work, but that doesn't always help.
• Non-compatible SQL extensions. Not that it's needed often, but sometimes one have some reason to change vendor.
• Historic bagge in SQL. For instance, why doesn't it let us substring(xxxx, ‘,’, ‘.’) directly, or let us store a substatement for use later…to for example extract second part of a csv value….

I have only two frustrations when running SQL queries, likely shared by just about everyone:

1. Why is it taking so long?!?!
2. Why am I getting these results?!?!

But my biggest frustration when composing SQL queries (which is what I think you’re really asking) is losing perspective.

It’s too easy to spend hours diving deep into crafting the “perfect” SQL query, that retrieves exactly what I want, and only what I want.

It’s too easy to forget that the query results will usually be processed in a different language (PHP, C++, etc.) that can shoulder part of the burden, and in a more expressive way. I’ll likely be iterating over the result set in my programs anyway, so why not let SQL do what it does best (JOINs and coarse filtering) and do the finessing where it’s more easily done?

Once I step back from the SQL abyss and consider my full range of options, using each tool to its strengths, I feel like a weight has been lifted from me.

Q: What is your biggest frustration when running SQL queries?

If you are facing difficulties in SQL subqueries, don't worry, you are not alone. Subqueries can be complex and tricky, but with some practice and patience, you can master them.

Understand the Basics of Subqueries

The first step in mastering subqueries is to understand the basics. A subquery is a SELECT statement within another SELECT statement. The inner query is executed first, and its results are used by the outer query to produce the final result set. There are two types of subqueries: correlated and non-correlated. In a correlated subquery, the inner query depends on the outer query, while in a non-correlated subquery, the inner query is independent of the outer query.

Practice with Simple Examples

Once you understand the basics of subqueries, it's important to practice with simple examples. Start with basic queries that only involve one subquery and build up to more complex queries. You can use online resources or a database software like MySQL or PostgreSQL to practice. Don't be afraid to experiment and try different variations of subqueries.

Pay Attention to Syntax and Formatting

SQL is a syntax-sensitive language, so it's important to pay attention to the syntax and formatting of your subqueries. Make sure you use the correct keywords and operators, and that your queries are properly formatted. Incorrect syntax can lead to errors and unexpected results.

Use Visualization Tools

If you're struggling to understand how a subquery works, visualization tools can help. Many database software programs have built-in query visualization tools that can help you see how your subqueries are working. You can also use third-party tools like SQL Fiddle or SQL Formatter to visualize and format your queries.

Get Help from Online Resources

There are many online resources available to help you learn SQL and subqueries. Websites like Stack Overflow and SQL Tutorial provide examples and explanations of subqueries. You can also join online communities like Reddit's SQL community or the SQL Server Central forum to ask questions and get help from experts.

This is not the harder one, but it is one of the most useful.

One of the problems with relational model is the hierarchical structures.

Imagine you want to represent hierarchically a domain security system where

• Each object is associated to 1 and only 1 domain.
• Each domain is contained into another (with an unpredictable number of levels) except the root ones (domains without parent)

create table domains( 
  id bigserial not null, 
  parent_id bigint, 
  name character varying not null, 
  PRIMARY KEY (id), 
  UNIQUE (parent_id, name), 
  FOREIGN KEY (parent_id) references domains(id)  
    ON UPDATE RESTRICT 
    ON DELETE SET NULL 
); 
create table things( 
  id bigserial not null, 
  domain_id bigint not nul, 
  ... 
  PRIMARY KEY (id) 
  FOREIGN KEY (domain_id) references domains(id) 
    ON UPDATE RESTRICT 
    ON DELETE CASCADE 
) 

Then, you want to know which “things” are into a domain and you realize that the answer is:

• Things defined directly into the domain
• plus Things defined directly into the children domains
• plus Things defined directly into the children of children domains
• plus …

It is impossible to write an SQL query that gives you the required result: SQL does nothing with unpredictable recursive level relationships.

Solving this problem requires you to maintain an additional data structure (a table) to lean on.

When I was to solve this problem 25 years ago, I first used a very complex data structure (not valid for “volatile” object hierarchies):

• If you draw the “tree” of nodes and visit them in post-order (children first), you obtain a sequence: assign the sequence position to each node
• Store in each node the sequence value of the smaller contained node
• The “bigger” contained node is itself

The resulting data structure could be:

• Node 11 contains all nodes from 1 to 11 (for simplification, node contains itself in this kind of structures, and it’s sequence value is the greatest)
• Node 4 contains nodes 2 to 4
• Node 6 contains itself

If we call the sequence value “bigger” and the sequence value of the smaller contained node “smaller”, then we can create a table “domain_containment” as

create table domain_containment ( 
  domain_id bigint not null, 
  bigger bigint not null, 
  smaller bigint not null, 
  FOREIGN KEY (domain_id) references domain(id) 
    ON UPDATE RESTRICT 
    ON DELETE CASCADE, 
  PRIMARY KEY (bigger) 
) 

Now you can perform a select to obtain children of domain X

select contained.domain_id  
from  
  domain_containment container, 
  domain_containment contained 
where 
  container.domain_id=$1 AND 
  container.smaller <= contained.bigger AND 
  container.bigger > contained.bigger 

The main problem with this structure is you have to maintain a “sequential” value that will change each time you insert or delete a domain (a parent or a children one).

The second problem is that “bigger” and “smaller” fields are not references to something and the own Relational System can’t ensure the integrity between your structure and the “domains” table: it is your responsibility (and, probably, you will run batch tasks to cleanup and rebuild time to time).

Experience and restrictions gave me an efficient and simplest “relational friend” solution:

A table with all the pairs (domain_id, ancestor_id)

create table containers ( 
  domain_id bigint not null, 
  container_id bigint not null, 
  primary key (domain_id, container_id), 
  foreign key domain_id references domains(id) on update restrict on delete cascade, 
  foreign key container_id references domains(id) on update restrict on delete cascade 
) 

To obtain all domains contained into a domain the select could be

select domain_id from  
  containers 
where 
  containers.container_id = $1 

This is a fast and simple select!!!

And what about the number of pairs we have to maintain? well, the depth of the domains tree is O(log N) where N is the number of nodes… the total number of pairs will be O(N log N) that is very far of the N² that probably you have imagined it would be

And what about maintaining this structure?

Just define a trigger for domains insert. When you add a new domain:

• Take the new domain parent identifier and copy it’s containers pairs assigning “domain_id” to be the new node
• Insert the missing (domain_id, parent_id) record

When you remove a node (a domain in our example)

• Foreign keys “delete cascade” will remove all affected pairs

I think I have wrote this solution so many times in all kind of projects last 20 years and it always surprise my how easy is it

And this is all falks

There are many kinds of programming languages. The kind of programming language that a lot of programmers learn are imperative programming languages. Every imperative language has a syntax, and a library of commands. You use these to you tell the computer what to do. Let’s say you want an ice cream, and you had a butler who behaved like an imperative programming language, then you will have to tell the butler what kind of ice cream you want, which store to go to, and how to get there

SQL is a declarative programming language. In SQL, you tell the computer what you want. If SQL were a butler, you would just tell him “I want an ice cream”, and he will go figure out the rest. In our case, the database is the butler. You tell the database what data you want, and it will figure out the best way to get that data.

Most developers who started learning programming in imperative languages get a bit shocked with declarative languages. If you are used to programming in imperative languages, you are used to thinking about how to solve the problem step by step. Whereas, when you use declarative languages, you only have to think about what you want. You have to shift your mind set

Now, the thing is every imperative language provides some sort of abstraction to the programmer. Languages that provide a higher abstraction are called high level languages. The more high level the language is, the closer it gets to being declarative. In fact declarative languages are implemented in imperative languages.
For example, if you are using assembly language(which is the lowest language possible), you are telling the computer which bytes to fetch from the memory bank into which registers, and which circuits to activate within the CPU.
If you use C or Java, you are telling it to add 2 numbers, and the compiler figures out how to generate the byte code that does that actual operations. Here, C and Java are closer to being declarative than assembly language is.
Another example is Scala, which is even more closer to being declarative that C/Java. If you want to convert a list of Shmoops to a list of Broops, you just need to tell it how to convert one Shmoop to one Broop. It goes ahead and converts all the Shmoops to Broops. You hook Scala up to an Engine like Apache Spark, and Spark figures out how run that conversion on a grid of computers. All the details of how the tasks get distributed on the grid is hidden by Spark.

One thing about SQL that one is not taught in school or database training programs which is probably the most important thing one needs to learn is:

Every SELECT statement can be written many different ways to return the same result. If you have not tested them all, then the form of the query that you have selected may not be the most efficient or the fastest.

Some of the “versions” of a given SELECT include:

• Simple JOIN
• Correlated sub-query (note: every correlated sub-query that does not include an aggregation can be rewritten as a simple JOIN)
• Non-correlated sub-query through IN (), NOT IN (), EXISTS (), or NOT EXISTS ()
• Derived table expression
• Common table expression
• ANSI-89 JOIN syntax:
  

SELECT *  
FROM frsttable f, scndtable s, thirdtable t 
WHERE s.keycol = f.keycol AND f.keycol = t.keycol 
  AND s.fltrcol = 4; 

• ANSI-92+ JOIN syntax with filters in the WHERE clause:
  

SELECT * 
FROM frsttable AS f 
JOIN scndtable AS s 
  ON s.keycol = f.keycol 
JOIN thrntable AS t 
  ON t.keycol = f.keycol 
WHERE s.fltrcol = 4; 

• ANSI-92+ JOIN syntax with filters in the ON clauses:
  

SELECT * 
FROM frsttable AS f 
JOIN scndtable AS s 
 ON s.keycol = f.keycol 
JOIN thrntable AS t 
 ON t.keycol = f.keycol 
   AND s.fltrcol = 4; 

• ON clause filters are processed pre-join while WHERE clause filters are processed post-join requiring the join results to be saved to a temp table and then fetched using the WHERE filter(s). (No one tells you this tidbit either!)
• Break a single query into multiple queries linked by partial results in temp tables
• Break a single query into multiple queries linking data in application or middleware space

In addition, you should test embedded queries with different filter values because your RDBMS’s optimizer may choose different query paths for different filter values due to the relative concentration of one value versus another in the data. While the “different” query plan may be faster overall to return all matching data, the “original” query plan may return initial data faster which may impact your application’s perceived responsiveness. Similarly testing on a development system with a limited data set may not only perform differently (faster or slower) just due to scale, but my choose a different query plan. Perhaps one that exhibits behavior that is not acceptable (as in the filter value case). Again, test test test until you find the right version of your query to embed in your application for the rest of time!

I remember doing an application upgrade about 10–12 years ago. A co-worker was assigned to assist me because the process was involved and he’d been through something similar with a different client. I was not happy that he was to be the lead, until…

So he takes a backup and even runs verify on the backup (just in case, right?). He runs through the process and hits a snag, so we need to revert. But the backup would not restore. It turns out that there was a bug in our specific version of SQL Server (I don’t remember the build) where in some weird set of extremely specific set of events, a valid backup would just not restore and we managed to unknowingly run head-first into that edge case. There was already a hot-fix for the problem, but the advice in those days was to only apply full Service Packs and to skip hot-fixes unless you actually needed them. The previous backup was from the previous evening, so nearly 24 hours-worth of data was gone.

It was a big client, so we ended up several other big guns joining us to find some way to fix the system. I finally remembered something I’d been told at one point how our application logs, while full of all sorts of random noise and the occasional error, had enough information to rebuild each and every transaction. I pulled the application logs and took a look-see and yes, in fact, it was possible (with a bit of effort and knowledge of the system) to duplicate every single INSERT/UPDATE/DELETE. Once I had the script built, I spoke up and told the group what I’d done. They discussed it and determined that it would work (there weren’t any things NOT being logged in that system, so my script was complete). I ended up saving the day, although I still would have preferred that everything had just worked. Why? Because this is an uncomfortable warning that even if you do everything right, you still might be completely and utterly screwed and there might not be anything you can do to recover. This is the only time I’ve seen a backup fail and (in a twisted sort of way) we were lucky because our verbose logging gave us a path out of failure. Most applications today barely log anything because the risk of a system subject to HIPAA or PCI DSS being compromised is so high. If this scenario transpired today, *I* would be the lead tech who had done everything right and still failed, and no amount of logging could save me.

It would be hard to narrow the irks down to one, so I’ll give you my top four.

Mandatory use of tools that cause more problems than they solve. Example: When we submit a change for code review, a static analysis tool is run on the change which is supposed to find bugs like use of uninitialized variables. The problem is that this tool has a very high false positive rate — in my experience, up to 90%. So we have to spend time analyzing and explaining away these false positives. A manager once told me we just have to put up with that.

Rigid adherence to fad methodologies. Example: After pressure from our management, our group finally took up the Scrum development method. In theory this should be a good thing — it should make our planning and execution more adaptable. In reality though, a large part of our activities do not conveniently fall into stories that can be scheduled and completed for our 3 week sprint. We sometimes have bug fixes for customers that have to be done immediately — we can’t tell them they have to wait 3 weeks for the next sprint. We also have a constant stream of code reviews from other groups that we can’t put off. Other activities have dependencies on other groups or people that we can’t control. So we consistently miss our sprint goals. It feels like a cruel sham to me.

Uncommented, poorly written, or tricky legacy code. Most of my work involves code written by other developers, frequently long gone, and a lot of the code is simply CRAP. Some of it is clever CRAP — too clever by half. Example: Our product makes excessive use of macros as a kind of poor man’s code generation. I’ve listed a particularly egregious example below (the names have been changed to protect the guilty). Of course there is no documentation on what any of these defines mean, and the people who wrote this left the company years ago,

static void some_function(void) 
{ 
40 #defines for different symbols 
LIST_OF_LISTS (defines thousands of items using the symbols) 
40 #undefs 
} 

Customers and their advocates who try to extort service by threats. Nothing pisses me off like a customer, or a sales rep on behalf of a customer, who tries to raise the priority of a bug by threatening a big deal or name-dropping an executive. What makes this even worse is that it will typically be the first time we’ve heard about the bug — it’s been languishing in the support organization long enough to have become critical. So now because of someone else’s incompetence we have to drop everything (there goes the sprint) to fix a bug, usually with very little information. Then the customer will demand updates, sometimes hourly. Situations like this tempt me to just phone in my resignation.

1. Legacy. Someone some time (in a ‘70 galaxy far away) set the length of an error code to 5 chars (2 for category, 3 for code). A ton of code relies on ODBC codes, native errors, SQLSTATE (2, 3) etc. Rewriting those is very tedious for both vendor and clients, which are usually behind some versions - a complete hell.
2. Programmers are cryptic. If SQL would have been written by poets instead of engineers, for sure the ‘General warning’ would have been split in 100 subcases. Coders do not like log and error messages verbosity when they don’t think is needed. Almost always it is needed, but we have to deal with it.