SQL Operation Order

Introduction In this article, we are going to see what is the standard SQL operation order. Once you understand the order in which SQL operations are executed, then it will be clear why the Oracle legacy pagination query required a Derived Table in order to make sure that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause.

PostgreSQL Auto Explain

Introduction In this article, we are going to see how the PostgreSQL Auto Explain feature works and why you should use it to gather the actual execution plan for SQL statements that execute on a production system.

The best way to use SQL NOWAIT

Introduction In this article, we are going to see what is the best way to use the amazing SQL NOWAIT feature that allows us to avoid blocking when acquiring a row-level lock. Since all the top major database support this functionality, Hibernate offers a NOWAIT option that allows you to render the proper database-specific syntax associated with this feature without risking database portability.

YugabyteDB Connection Pooling

Introduction In this article, we are going to see the overhead of acquiring a new connection when using YugabyteDB and why connection pooling is mandatory for performance.

Building a Distributed Audit Log with YugabyteDB

Introduction In this article, we are going to see what is the best way you can build an Audit Log using YugabyteDB. And what’s even more interesting is that this solution is almost the same on PostgreSQL. The only difference is how we are building the audit log table PRIMARY KEY, but everything else is exactly the same, demonstrating how easily you can migrate from PostgreSQL to YugabyteDB.

How to format SQL using the command line

Introduction In this article, I’m going to show you how to format SQL using the command line and transform a single-line SQL statement into a multi-line SQL string that’s way more readable.

SQL Recursive WITH CTE queries

Introduction In this article, we are going to see how SQL Recursive WITH CTE (Common Table Expression) queries work and how we can apply them for processing hierarchical data models.

SQL CROSS APPLY – A Beginner’s Guide

Introduction In this article, we are going to see how the SQL CROSS APPLY works and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets. CROSS APPLY is basically equivalent to the LATERAL JOIN, and it’s been supported by SQL Server since version 2005 and Oracle since version 12c.

SQL Derived Table or Inline View

Introduction In this article, we are going to see what an SQL Derived Table or Inline View is and how you can use it to bypass the standard SQL operation order. While the SQL Standard uses the term Derived Table for this feature, some relational database systems call it Inline View.

SQL CTE – Common Table Expression

Introduction In this article, we are going to see what an SQL CTE (Common Table Expression) is and how you can use it to reuse subquery result sets or inline views.