Relational Database Management Systems and SQL have gotten a bad rap over the past 10 years or so, and some of it deservedly so. But we tend to overlook that the SQL standard is being continuously improved and that it has become even more powerful than it was 20 years ago. In fact, SQL is now considered to be turing complete. In this blog post I'll try to shed some light on three of these features that were not available when I first started working with SQL in the late 90s and that are overlooked in lots of tutorials for beginners. All the example queries written here were tested against Postgresql 94.

Lateral

Lateral is the for each of sql. It is the appropriate tool whenever we want to iterate over each record and run a sub-query, giving access to that record. Although it might look like a sub-query, it differs in that lateral can return multiple columns and multiple rows.

For the sql examples in this post, we will create a table for holding users and another for holding payments:

CREATE TABLE users(
    id BIGINT PRIMARY KEY,
    firstName VARCHAR(75),
    lastName VARCHAR(75));

CREATE TABLE payments(
    id SERIAL PRIMARY KEY,
    user BIGINT REFERENCES users(id),
    amount DECIMAL,
    payment_date DATE)

As a contrived example: For each user, we want to print the month and amount where they paid more than $10. In psuedo code this would look like this:

rows = []
for r in table_reference():
    for r1 in sub_query_for_amount(r):
        rows += (r, r1)

This can be written in sql using lateral:

SELECT month, payer_name, amount
FROM (SELECT u.firstname || ' ' || u.lastname as payer_name, id
      FROM users as u) u1 INNER JOIN LATERAL
      (SELECT amount, EXTRACT(MONTH FROM payment_date) AS month
       FROM payments 
       WHERE payer = u1.id AND amount > 10) p ON TRUE;

Filter

Filter works on aggregates. The result of the aggregate is limited to only the rows that match the additional filter conditions.

In this example we will use it to filter out all the row where the amount paid by an individual was more than $20.

SELECT EXTRACT(MONTH FROM payment_date) as month, 
sum(amount)  FILTER (WHERE amount < 20)
FROM payments
GROUP BY month;

Lag

And finally, the window function lag. I have had to do multiple contortions in the past to write queries where I needed to reference the previous row. Without the lag function, I needed to either write stored procedures or process the query in the application code where I could do more flexible processing.

But, postgresql provides a window function, lag, to assist us with this. According to the postgresql docs, a window function:

performs a calculation across a set of table rows that are somehow related to the current row. 
This is comparable to the type of calculation that can be done with an aggregate function. 
But unlike regular aggregate functions, use of a window function does not cause rows to become 
grouped into a single output row — the rows retain their separate identities. Behind the scenes, 
the window function is able to access more than just the current row of the query result.

To illustrate its usage, lets try to write a query that will print the amount paid, and the month, and additonal column that indicates if the amount paid increased or decreased compared to the previous month:

SELECT EXTRACT(MONTH FROM p.payment_date) AS month,
  case
    when lag(p.amount) OVER (ORDER BY p.payment_date) > p.amount THEN 'up'
    else 'down'
  end prog
  FROM payments as p
   WHERE p.payer = 3
ORDER BY p.payment_date;

 month | prog
-------+------
    11 | down
    12 | down
     1 | down
     2 | up
     3 | up
     4 | down
     5 | up
     6 | up
     7 | up
     8 | down
(10 rows)

Conclusion

It is easy to overlook some of the modern features added to SQL in the past 17 years, especially with the emergence of NOSQL. But for complex reporting, RDBMS is many times the right tool for the job, and with new features like filter, lag and lateral, we are able to write these queries easier than before.