Relational databases are widely used. However, it is not very clear how queries written by SQL are executed by the DBMS. Don't you think we can understand the execution mechanism better by simulating the query execution using a procedural language?
I tried to translate some SQL queries into Perl programs. Although there are many methods to implement a join operation, I tried only one. However, I could implement an optimized join within 20 lines of Perl code, and I recognized the power of Perl again.
The detail was describe in the article titled “Simulation of RDB queries using Perl” in “Small and Large Stones of Programming”. In this article, I began with translation of a very simple SELECT statement, and wrote examples using COUNT function, GROUP-BY clause, or JOIN operator. You can run every code in this article using Perl 5.
The longest example in this article is the one using JOIN operation, but it has only 20 lines of code. There are many method to implement JOIN operation, and the best selection depends on the data to be handled. However, in this article, I only used a simple hash join. Tables are stored in arrays (i.e., main memory, instead of disk), and the function of hashing in Perl took the key role. This enabled such a short description.
I hope this will be a good tool for Perl users to understand relational databases.