Four SQL anti-patterns: Avoid them!

Photo by Sunder Muthukumaran on Unsplash

The following best practices provide guidance on avoiding query anti-patterns that impact performance in BigQuery.

Self-joins

Best practice: Avoid self-joins. Use a window (analytic) function instead.

Typically, self-joins are used to compute row-dependent relationships. The result of using a self-join is that it potentially squares the number of output rows. This increase in output data can cause poor performance.

Instead of using a self-join, use a window (analytic) function to reduce the number of additional bytes that are generated by the query.

Using a Self-Join in Oracle SQL Assuming we have a table named employees with columns employee_id, employee_name, and manager_id. We want to find employees and their managers using a self-join:

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

This query uses a self-join to find employees and their managers by joining the employees table to itself.

Query 2: Using a Window (Analytic) Function in Oracle SQL Here’s an alternative query using a window (analytic) function to achieve the same result:

SELECT employee_name AS employee…