Posted by: Derek Kuhr
Oracle database administration, Oracle development
The other day I stumbled across a blog post titled “In defense of SQL” — in this post, blogger Kenneth Downs asks, “So am I the only one who likes SQL?”
Is he? Apparently SQL has a bad rap among development languages — people tolerate it because they figure they have to, but they think it, well, sucks. Downs, however, lists a number of things to enjoy about SQL. For instance, it can be easily generated with other languages, and it’s easy to read and write.
I asked a couple of our site experts how they feel about the structured query language. Like Downs, Brian Peasland, DBA and expert in database design as well as backup and recovery, also has a soft spot in his heart for SQL:
I have always been a fan of SQL. When I was studying database theory, I was taught that there are three main operations in any database query: 1) selection, 2) projection and 3) joins. Think of a table as a grid with each record as a row in the grid and each attribute as a column in the grid. A “selection” is a vertical slicing of the grid. You simply indicate which columns you want. Not surprisingly, the selection is the entire purpose behind the SELECT clause of a typical SQL statement. A “projection” is a horizontal slicing of that grid. The projection defines which records you returned out of all possible records in the table. The projection is defined in the WHERE clause of the SQL statement. Many who know SQL already know that a “join” operation is taking more than one table and stitching them together. The FROM clause defines the join operation … or the table(s) to find the data. So at its basic level, a simple SQL statement looks like the following:
Once you know the basic tenet above, reading and writing SQL statements becomes much easier. The FROM clause tells you where to pull the data *from* and the WHERE clause tells you which rows to pull out of your data source. Once you know the rows of data to be returned, we only show what the user SELECTed. Once I learned the basics of SQL and started thinking of SQL similar to the above, it really made things easy for me. And it just makes sense. I agree that one can get some convoluted SQL statements which do require some analysis before truly understanding what is going on, but most SQL statements are not that complicated.
One other thing that I think is great with SQL is its standardization. I have worked in Oracle for a very long time. When I first started working with SQL Server, I did not have to learn an entirely new query language. This was huge! Granted, not all SQL statements work in both systems, but many do. Some things are different, but I just had to learn a few things to easily rewrite my query. For instance, the substring function in Oracle is SUBSTR, while in SQL Server, it is SUBSTRING. In Oracle, you use INSTR, but the similar function in SQL Server is CHARINDEX. So a few non-standardized functions are different. But for the most part, my SQL queries work well in both.
I am well aware of the ANSI SQL standards and that each RDBMS vendor does not implement all of the standards, so not all SQL statements work the same in every database. But the point is that even if the SQL statement works in SQL Server but not in Oracle, I do not have to redo everything to get that SQL statement to work in Oracle. I just have to tweak it a little to get the statement to execute.
Finally, most of the people that I have worked with that do not like SQL are those who did not really get a chance to learn it. They were taught the simplest of SQL statements and then let loose to code their application. As their application became more complex, coding a SQL statement became tougher, and more frustrating. But those I have seen take a 1-week course on SQL for their RDBMS of choice do not have this same attitude. I think this speaks volumes about why some hate SQL and others like it.
All you other DBAs and developers out there: what do you think? Is SQL not so bad or does it deserve its nasty reputation?
(Stay tuned for remarks from SQL guru Rudy Limeback on this topic.)