How to Draw Pictures with Recursive SQL Queries

Did you know that SQL can be used for fun? You can even ‘draw’ in SQL using recursive queries! Find out how in this article. 

As an SQL user, you already know this language is great for data analysis and data manipulation in general. This is something people get very serious about. But you can also have fun with it by drawing ASCII-art pictures using the SQL code. To do that, you have to know how recursive SQL queries work.

What Is a Recursive Query in SQL?

If you don’t know already, a recursive query is an SQL query that references itself while it executes commands. Each recursion returns a subset of results and then the query repeats until it gets the final result.

To use recursive queries in SQL, you have to know Common Table Expressions (CTEs). CTEs allow you to define a temporary result set; they can be non-recursive or recursive. Later on, you can reference the CTE in a SELECT statement and use it as you would any other table.

CTEs are great for querying hierarchical data. If you’re writing complex SQL code, they also help you improve code readability by breaking computations into parts and nesting them.

But did you know you can use CTEs to draw pictures with SQL? Sometimes you need to relax a bit and have fun. Maybe you want to amuse yourself and your colleagues by showing off your SQL knowledge and your nerdy-artsy side. Who knows – perhaps you’ve unearthed an artistic talent that’ll get your art shown in MoMa or Tate Modern under the section “SQL art”! Let’s have fun by drawing something!

Drawing a Man with a Hat – Using SQL CTEs!

Even though summer is approaching, it’s still surprisingly cold where I live. It’s cold enough that some people are still wearing hats to keep warm. Why not try to draw them? Let’s try to draw a man wearing a winter hat.

Here is some code which does precisely that:

WITH RECURSIVE hat
AS (
   SELECT
     CAST(REPEAT(' ', 11) || '/V\' AS VARCHAR(100)) 
       AS hat_pattern,
     1 AS level
  
   UNION ALL
     
   SELECT
     CAST(
       REPEAT(' ', 10-level) || '/' 
         || REPEAT('V', 2 * level + 1) || 'V\'
         AS VARCHAR(100)) 
       AS repeated_pattern,
     hat.level + 1
  FROM hat
  WHERE level < 6
)
 
 
SELECT hat_pattern
FROM hat
 
UNION ALL
 
SELECT 
  CAST(
    REPEAT(' ', 5) || '|' || '             ' || '|' 
    AS VARCHAR(100)) 
  AS forehead
 
UNION ALL
 
SELECT 
  CAST(
    REPEAT(' ', 5) || '|' || '  O   /   O  ' || '|' 
    AS VARCHAR(100)) 
  AS eyes
 
UNION ALL
 
SELECT 
  CAST(
    REPEAT(' ', 5) || '|' || '     /_      ' || '|' 
    AS VARCHAR(100)) 
  AS nose
 
UNION ALL
SELECT 
  CAST(
    REPEAT(' ', 5) || '|' || '     ~~~~~   ' || '|' 
    AS VARCHAR(100)) 
  AS mouth
 
UNION ALL
SELECT 
  CAST(
    REPEAT(' ', 5) || '|' || '   {  |  }   ' || '|' 
    AS VARCHAR(100)) 
  AS chin;

This is quite a long query, but I’ll explain how it works. When writing a recursive CTE, you start using WITH, followed by the keyword RECURSIVE and then the name of the CTE. My CTE’s name is hat. After that, you write a SELECT statement. The first column I’ve selected is hat_pattern. This code row uses the function REPEAT to repeat a blank space (' ') eleven times. This is then concatenated with '/V\', which will be the pattern that will make the hat I’m drawing. All this is cast as a VARCHAR data type. The second column is level with the value 1. In a few moments you’ll realize its purpose.

UNION ALL then combines the result of this SELECT statement with the next one. To achieve that, there must be the same number of columns in both statements. The first column in the second SELECT statement is repeated_pattern. Again, there’s the REPEAT() function that will repeat blank spaces. Now, though, it’s 10-level times. This refers to the level column from the previous SELECT. The column level will start at the value 1; following recursion, it will have value 2, and so on. In the case of 10-level, the blank space will be repeated nine times (10-1=9), then eight times (10-2=8), and so on. I’ve concatenated '/' to those blank spaces. I’ve also concatenated the value 'V'. It will be repeated 2 * level + 1 times. In other words, it will be repeated three times with the first iteration (2*1+1=3), five times with the second one (2*2+1=5), and so on. I’ve also concatenated the value 'V\' with it.

All this will be a pattern that will comprise the man’s hat after the first level. The second column in this SELECT statement is hat.level + 1. This means that with every recursion, the value of this column will go up by one. The data will be used from this same CTE (hat), with the condition that the column level value is less than six.

At this point, I’ve finished writing the CTE. Now I have the SELECT statement that will use the data the recursive query produces. I’ll use this part of the code to draw the man’s face, since this doesn’t require as much repetition as the hat does. I’ve again used the REPEAT() function and concatenation on some string values I think are suitable for drawing facial features. Every facial feature has its column, which is connected with all others using the UNION ALL.

Now that I’ve explained how the code works, it’s time to see the result. Here’s my artistic masterpiece, a man I’ve named Boris. He just looks like a Boris, doesn’t he?

OK, I admit it’s not a Van Gogh, but it’s not that bad either. Surely it can pass as a Picasso, with its cubist approach to facial perspective. Even if you’re not a promising artist hiding behind SQL, you can at least laugh at yourself and your drawing skills. To practice your drawing, you can, for example, draw a Christmas tree. Here are also some examples I was considering before I settled on Boris: a house, a windboard on a sea, or the Renault insignia. Or you can just keep it simple by drawing triangles and make different kinds of arrows out of them.

If you want a more serious explanation of how CTEs work, here’s an article that reveals the power of CTEs.

Learn Other Uses of the SQL Recursive CTEs

You just saw that SQL’s recursive queries can be good for having fun. However, they can be used to even better purpose when analyzing data and writing complex queries. This article demonstrates how useful CTEs really are. Everything covered in that article you can learn in LearnSQL.com’s Recursive Queries course. And when you need a break from acquiring new knowledge, you can always rest a little and draw something.

If you like to make ASCII art using SQL, share your work with us!