The Declarative Nature of SQL

Click to share! ⬇️

We’ve been covering a lot of information about database technology, terms, and ideas so far. Soon we will be diving into using data manipulation language to insert, update, and delete data in various databases we’ll create. Before we get to that point, we need to take a look at a key aspect of the Structured Query Language in general. This would be the fact that SQL is a Declarative Language, which is very much a different animal altogether from a procedural language. You are most likely much more familiar with procedural approaches in computing. This is why in this episode, we’ll take a closer look at what it means to be a Declarative Language, and how we will need to adjust our thinking to best make use of it.

What is a Declarative Language?

If you’re like most web developers, you probably found things like PHP and JavaScript more exciting than SQL when you first began. After all, when we think of programming, we think of actually doing something or taking an action. SQL isn’t really the first thing that comes to mind when following this type of thinking. The deal here is that those languages that you probably first cut your teeth on are procedural. This means they follow a step by step process of how to do something.

SQL on the other hand is a Declarative Language. In a declarative language, the programmer declares what she wants, and not how to do it. It is a subtle yet profound paradigm shift of how you might be used to doing things. All in all, SQL is fairly straightforward, but if you’ve ever found yourself scratching your head at how to approach solving a problem in SQL, it may be due to this very difference in programming approaches. It definitely takes a different mindset and process than you might be used to.

Basic SQL Structure

All SQL follows a convention of selecting what you want, where you want it from, and include any filters to apply. It looks a bit like this:

SELECT … FROM … WHERE

Following this convention, we could write the following query:

SELECT post_name
FROM wp_posts
WHERE post_title LIKE '%laravel%';

This is pretty cool. Basically it breaks down like so. The SELECT is what to get. The FROM dictates the location to look in, and the WHERE is the condition that will apply. The LIKE portion uses the MySQL wildcard characters which is just a percent sign, to search for any data within the posts_table that contains the term ‘laravel’. This outlines the declarative approach. Notice that the query above does not say something like, get all the post names, loop through each name to see if it contains laravel, then return the matching results. That would be a procedural approach which is not what we do in MySQL.

Advantages of a Declarative Language

In a declarative language, one benefit is that it is the database itself that figures out the procedure. All you have to do is tell the database what you want, it will figure out how to get that result for you. It is the Optimizer that determines which algorithm will most efficiently get the data that you ask for. This is a very different approach from what you might be used to in your usual procedural approach to programming. It helps to know upfront that this is how MySQL works, lest you bang your head against the wall trying to figure out why your procedural approaches are not working in the future when we get into more detailed raw queries. You are essentially relinquishing your control to the database, rather than using your own logic. You need to think of what you want, not how to get it. Get It? Got It. Good!

Declarative Language Outline

Efficient to use
The database chooses the algorithm
May be harder to debug than procedural
Paradigm shift for procedural thinkers

Shifts Focus To Database Design

When you’re programming an application, if things are slow, you can take steps to optimize the procedure to solve the problem. Maybe you can test a recursive solution against a simple looping solution. Maybe in other cases, you can use a string function as opposed to a regular expression. There are many ways to fine-tune your application if need be when using a procedural language. Due to the declarative nature of SQL, this is not possible when dealing with databases. If you find that your database operations are running slow, more often than not you’ll be looking at modifying the database schema or table structure rather than changing the query. Again, this is because MySQL is already choosing the best procedure or algorithm to provide what you asked for so trying to change what you are asking for will be of no help. Designing databases is not something you are apt to be doing on a daily basis unless you are a DBA of some sort. If you’re a web developer that deals with setting up WordPress Installations for clients, you don’t even think about the design of the database. Perhaps you’re a plugin developer and maybe you need an extra table or two in the database. Again, not a real design heavy task. The point is, is that if you want to get better at database design, it is something you’ll have to practice and test more frequently than you likely are doing now.

Conclusion

In this episode, we covered the fact that SQL is a declarative language. It’s a pretty simple concept, with the main take away being to think in terms of what you want, not how to get it. This may be new if you’re used to the procedural approach like most of us probably are. It may seem like a simple thing, but it is worth having an understanding of to provide a better foundation for learning MySQL as we progress through this series.

Click to share! ⬇️