SQL vs. Visual Query Tools

Since the early 90’s there have been many attempts to create graphical database query tools.  I myself have designed and built both desktop and web-based query tools.  I’ve also seen attempts by others including large software companies to develop query tools, data manipulation applications (e.g. ETL and BI products), forms, dashboards, and so on.  Interestingly, there still seems to be no substitute for an old command line query tool: Structured Query Language (SQL).

Of course I understand the value of visual interfaces, but they are not a panacea.  For example, take the following query:

SELECT column1, column2, column3, SUM(column4)
FROM table1
WHERE column1 BETWEEN ’01-JAN-10’ AND ’30-JUN-10’
    AND column2 IS NOT NULL
    AND column4 > 10000 
GROUP BY column1, column2, column3 ;

The above SQL command takes a few seconds to create.  It is understandable by someone without technical skills (perhaps with some minimal explanation).  It is straightforward.  It is simple.

Now compose the same query with a typical visual query tool:

1. Find the correct table in a dropdown list of tables.
2. Find column1, column2, column3, and column4 and drag them to some box on the screen.
3. Double-click on column1 to open up a dialog.  Select something that says “range” or “selection criteria” or “column values” or whatever which opens up another dialog.
4. In the new dialog select “range” and select start and end values (’01-JAN-10’ and  ’30-JUN-10’) from a list or manually type in the values.
5. Save and close the open dialogs.
6. Open another dialog on column2.
7. Use the dialog to figure out how to exclude NULLS.  
8. Save and close the dialog.
9. Open another dialog for column4.
10. Figure out how to only select values greater than 10,000. 
11. Save and close the dialog.
12. Find a button or link in the GUI that says something like “groupings” or “aggregations” and open the appropriate dialog.
13. Create groupings on column1, column2, and column3.  
14. Save and close the dialog.
15. Find something in the GUI that says something like “functions” or “aggregate functions”.
16. Add a SUM( ) function to column 4.  
17. Save and close the dialog.
18. Press Submit.

Was it really easier?  Keep in mind that each vendor’s product has its own GUI—there is no standard.  Also keep in mind that many vendors of these products also sell training which suggests that perhaps their GUI’s are not instantly intuitive.

SQL is the standard language of relational databases.  With small variations, it is ubiquitous.  It is easy to hire people with SQL skills.  It is easy for non-programmers to learn.  SQL can get somewhat complex for complicated queries, but that is also true for any query tool graphical or otherwise.

Again, I understand the value of visual interfaces.  There are many useful applications that allow a user to access information with a few clicks.  There are wonderful dashboards, graphing tools, and so on.  But whenever serious data analysis or complex ETL is required, an enterprise shouldn’t disallow the most flexible and in many cases the simplest tool for querying data.

Change isn’t always progress.  What works well tends to return.