Procedural Versus Declarative Languages
We mentioned at the beginning of the chapter, that SQL is not a procedural language. Before we move
on to look at how we can actually implement SQL, we’ll first see exactly what we mean by a declarative
rather than procedural language. Many readers will have experience in languages like Visual Basic or
C++ in which they mastered how to write a series of statements in order to achieve a goal. You have
probably even used these commands to “walk through” a set of data checking to see if each item is the
item of interest. If you wanted to change data you wrote a series of steps that somehow opened up the
datastore, then put a pointer in the correct location, modified the data, moved to the next bit of data (all
the while checking if you were at the end of the data yet), modified the next record and finished by
closing the datastore. In a procedural language we tell the computer each step to perform. The
computer performs those steps and, if they are well written, we will end up with our desired result.
SQL is not a procedural language but a declarative language. You write a single SQL declaration and
hand it to the DBMS. The DBMS then executes internal code, which is hidden from us. The DBMS
returns a set, which is a group of data that is somehow defined. For example, we write a SQL statement
that would translate as something like Give me the last names of all the employees. The DBMS will
work away for a few milliseconds and then produce a set that contains Adams, Barrett, Cao, etc. In a
declarative language, we carefully phrase what we want and then let the DBMS get it for us. If we have
written a good SQL statement then the resulting set of data will be correct.
Procedural languages result in many lines of code.
Declarative languages result in one statement of the desired result.
The distinction between these classes of languages drives the entire way that we think about database
programming. Many of the mistakes that I see are a result of thinking in a procedural way when writing
SQL statements. In this book we will show you hundreds of examples of good SQL statements that will
help you on your way to thinking declaratively.
Languages in a Taxi Ride
A good analogy exists between these types of languages and different types of taxi rides. When I get in a
taxi I can give a direction to the driver in one of two ways. If the driver looks and sounds like he knows
what he is doing then I just give him the address. If the driver looks like he just moved to town
yesterday, then I talk him through the directions to get to my destination.
If I give the driver an address I am communicating like a SQL statement, it is a clear description of the
result and the driver carries it out as he or she sees fit. We may go south then east or we may go east
then south. The driver is free to adapt to local conditions like a closed road or a traffic jam. It doesn’t
really matter to me; a good taxi driver will get me to my stated destination in the best way the driver
knows. During the trip I just sit still and wait for the result. If the address is written on a piece of paper
then I can show that same card to any good driver.
If I give the driver exact directions then I am communicating in a procedural language. I tell the driver
to head south four blocks then turn left at the Luck Hunan Restaurant. Procedural languages work fine
when the taxi driver is not too experienced and when the situation is very uniform. But I must
constantly interact with the driver. If I get involved in a book we are likely to overshoot our destination.
Likewise, if I tell the driver to turn left at the Luck Hunan Restaurant and the name has changed to the
Lucky Duck Bar, then the communication will fail.
In this analogy we see a number of important points that apply to SQL. First, a declarative language
only works when you have a strong, SQL-enabled DBMS (taxi driver). Second, procedural languages
can fail when there are unexpected changes in the situation (changes in the city’s landmarks). Third,
procedural languages require interaction during the task (like me instructing the driver at every turn)
whereas declarative languages only require that the requester wait for the process to finish. And fourth,
a well-written SQL statement will work with any SQL-enabled DBMS (like an address on a paper will
work with any good taxi driver).
Comparing Procedural and Declarative Languages
| Procedural (Basic, C++, Cobol, etc.) | Declarative (SQL) |
| Most work done by interpreter of the languages | Most work done by Data Engine within the DBMS |
| Many lines of code to perform a task | One SQL statement to perform task |
| Programmer must be skilled in translating the objective into lines of procedural code | Programmer must be skilled in clearly stating the objective as a SQL statement |
| Requires minimum of management around the actual data | Relies on sophisticated, SQL-enabled DBMS to hold the data and execute the SQL statement against the data |
| Programmer understands and has access to each step of the code | Programmer has no interaction with the execution of the SQL statement |
| Data exposed to programmer during execution of the code | Programmer receives data at end as an entire set |
| More susceptible to failure due to changes in the data structure | More resistant to changes in the data structure |
| Traditionally faster, but that is changing | Originally slower, but now setting speed records |
| Code of procedure tightly linked to front end | Same SQL statements will work with most front ends Code loosely linked to front end. |
| Code tightly integrated with structure of the datastore | Code loosely linked to structure of data; DBMS handles structural issues |
| Programmer works with a pointer or cursor | Programmer not concerned with positioning |
| Knowledge of coding tricks applies only to one language | Knowledge of SQL tricks applies to any language using SQL |
To summarize this table, declarative languages are quite different from the procedural languages you
may be using now. Procedural languages like C++ place an emphasis on the programmer writing many
lines of code to describe the exact steps of obtaining a result. Declarative languages like SQL place an
emphasis on the programmer writing an exact description of the desired result. The DBMS then handles
the task of obtaining the result. A good SQL programmer becomes very skilled at carefully describing
the result but remains blissfully ignorant of the internal code of the DBMS that executes the result.
