2015-11-22 - SQL

Structured Query Language (SQL) is a family of languages used for interacting with databases. Originally developed by IBM in the 1970 and standardized in 1986. SQL allows users to retrieve, create and modify data in a database. These queries are commonly made against a database engine that transforms the query into a series of actions that are required to complete the requested operation.

One of the best things about SQL is its query syntax. For simple operations these queries are very natural sounding which makes them easy to work with and understand. For example to get column1 and column2 from table1 where column1 is equal to 3 you would write this.

If you wanted the results to be ordered by coumn2 you would change it to.

This makes SQL a very approachable language. Simple operations are simple to perform and it’s only when you start wanting to do more complex things that the query start becoming more complicated.

The main reason SQL can have such simple syntax is because it’s a declarative language. This means that instead of telling the database engine what you want it to do you declare what you want to happen. It’s the database engine’s job to figure out how to do it. In a lot of languages doing something a bit weird feels like cheating, like you are doing something you shouldn’t be doing. In SQL it feels really good when you get a weird query to work exactly how you want it to be. I think this is largely because you aren’t saying what you want it to do so you don’t feel like you are forcing it to do something. Instead you are just finding the best way to describe what you want to happen.

SQL is a standard but is still somewhat un-standardized. All SQL languages are derived from the standard and have a lot in common but they aren’t all the same. The main differences come from the addition of procedural elements which are required for writing scripts that make decisions or perform operations multiple times. These procedural elements weren’t a part of the original standard so they were implemented differently by individual database systems. Microsoft uses Transact-SQL (T-SQL) for their SQL Server and Oracle uses Procedural Language/Structured Query Language (PL/SQL) for their database products. A standard for these procedural elements was created as SQL/Persistent Stored Modules (SQL/PSM) which some databases, such as MySQL, base their versions on. There are also slight syntax and data-type changes between implementations. This means that going from one database system to another does require some new knowledge. A lot of the basics are the same but you need to be aware of the actual implementation differences.

I personally like SQL. It’s a very quirkily language but in a lot of ways that makes it more enjoyable to work with. You can do basic things without a lot of knowledge but you need to really understand the particular database you are working with to do really advanced things well. The more time you put into understanding the quirks the more enduring the language is.