Advanced SQL

— — Stored Function, Stored Procedure, Trigger, CTE, and Recursive CTE.

Except for the basic SELECT-FROM-WHERE and common INSERT, UPDATE and DELETE, there are some other useful SQL queries including Stored Functions, Stored Procedures, Triggers, Common Table Expressions(CTE), and Recursive CTE.

I will use MySQL for the classicmodels, which you can download here. I will focus on three tables(customers, employees, and orders) just to make it easy to understand. Getting hands dirty is always the best way to learn.

ER Diagram for classicmodels database

Note: Just in case if you want to know how this ER diagram is generated: on the menu bar, click on Database -> Reverse Engineer.

Stored Functions

A stored function is very similar to the functions we create in other languages. The function consists of the function name, parameters, and return value. Writing common queries can always do the work functions do. Functions simply make the procedure more clear and reproducible.

Deterministic means the function always return the same result for the same inputs, and “not deterministic” otherwise.

Stored Procedure

Stored procedures save you time by putting the queries into “scripts”. It works similarly to the “Automator” on your mac.

Stored procedures are different from stored functions in:

1. They are pre-compiled and can be executed when called. Instead, stored functions will re-compile every time we call them.
2. Stored procedures do not require a return value.
3. Procedures can have inputs and outputs. Functions only have inputs.
4. We can call functions in a procedure. The other way around does not work.
5. Functions only work with SELECT. Procedures work with SELECT, INSERT, UPDATE, and DELETE.

Trigger

When we modify a table, the trigger will fire and keep records/create reminders for this modification. We can create a trigger before or after the modification, and the modifications can be INSERT, UPDATE, or DELETE. We use OLD and NEW to refer to the tables before /after the modification. The availability of OLD and NEW can be described as below:

The trigger syntax is:

Now let’s put this into practical use. Before we update the customers table, we want to keep track of the information from the customer(number, name, phone, sales representative) and the date of the update. We can achieve this with the following code.

The records we saved are in the new table customer_update.

Common Table Expressions (CTE)

A CTE (Common Table Expression) is a temporary result set that you can reference within other statements (SELECT, INSERT, UPDATE or DELETE). CTE helps promote readability, allows recursion, and it can be substituted for a View. CTE starts with a WITH operator.

For example, if we want to find all the customers from New York, we might want to narrow the scope down to the USA first. We can do this by:

Here is another more complex one: we want to get customers’ names and phones who rank top5 in the purchase amount.

Recursive CTE

Recursive CTE’s are CTE’s that reference themselves. If you have experience with recursion in other programming languages, you can get it pretty easily. Let’s recall how recursion works in Python with the Fibonacci example.

We can get 0, 1, 1, 2, 3, 5, 8, 13…

To achieve this in SQL:

1. We pass 1(n), 0(fib_n), 1(next_fib_n) as a base (we call it anchor) to the recursive CTE.
2. We then use UNION ALL to achieve “n+1”. 2(n), 1(fib_n), 1 (next_fib_n) are passed to the recursive CTE again.
3. The recursion continues until it hits the condition(where n < 10). The result looks like:

Now let’s put this into practical use. In the employees table, reportsTo indicates the relationships between employees(Leader-Member). If we want to get the tree graph of these relationships, we can do the following:

Hope you have a great understanding of functions, procedures, triggers, and CTE already. Thank you for reading my article. If you find it useful or helpful, please leave me a thumb!