#Psequel query writer code

Keep your subquery blocks small, with a strong focus on a single functionality in mind. WITH t_avg_salary AS ( -averagy salary for every employee SELECT emp_no, AVG(salary) avg_salary FROM emp.salaries GROUP BY emp_no ) SELECT e.last_name, avg_salary FROM emp.employees e JOIN t_avg_salary ON e.emp_no = t_avg_salary.emp_no WHERE avg_salary JOIN t_avg_salary In the WITH clause, you can give your subqueries a symbolic name, just like in a FROM clause, while moving it to the beginning of your statement. Some of us write SQL for years and never used the WITH clause. This “inside-out” direction is in contrast to our normal reading direction from top to bottom, which most of us are comfortable with.

#Psequel query writer code

This is a mayor issue on readability in more complex statements.Īnd even more important, every subquery splits the surrounding query in code “before” and “after”. Every subquery needs to get indented and the lines get longer and longer. And when you start with subqueries, the probability is high that more subqueries will follow over time. Subqueries can have more subqueries in any depth. SELECT last_name, (SELECT MAX(salary) FROM emp.salaries WHERE emp_no=e.emp_no) FROM emp.employees e So where is the problem?

Scalar subqueries (also a correlated subquery).

Please note that this is also a correlated subquery, which should get used carefully (see ). SELECT * FROM emp.employees e WHERE 0 3000) SELECT COUNT(*) FROM (SELECT gender FROM emp.employees) e

In the FROM clause, as a derived table with an alias name.

Basically there are three types of subqueries in SELECT statements: You know subqueries? Subqueries are great to encapsulate problems within a block of your query. As your SQL gets larger and more complex you will reach certain limits, where you need more structure than the style guide provides. With these basic rules, you’re SQL will easily reach at least a moderate readability level. * find highest actual salary for each employee */ SELECT first_name, last_name, MAX(salary) FROM emp.employees e JOIN emp.salaries s ON e.emp_no = s.emp_no WHERE current_date BETWEEN from_date AND to_date - filter date GROUP BY first_name, last_name

Use comments, whenever a query gets more complicated than a simple SELECT/FROM/WHERE statement.

SELECT first_name, last_name, birth_date FROM emp.employees WHERE gender = ‘F’

Create a gap, by aligning the keywords to the right-hand side and the values to the left-hand.

Use uppercase for all reserved keywords, like ´SELECT, FROM or WHERE and lowercase for columns, tables and symbolic names.

But if you’re in a hurry, you get very far with these three advices: There is an excellent SQL style guide from Simon Holywell, take a look at it.

When we say readability, we have to talk on style. But what can we do, to keep even large queries with hundreds of lines and 20 or more tables readable? The very basics Combining these elements, you can quickly generate a big mess.

I assume you already know the basic and more advanced elements of SQL: joining, grouping, subqueries or analytic functions. So, in this article we focus on readability and maintainability. And depending on your database engine optimizer, you often get both.

Keep in mind the costs for hours to understand a complex query, the cost that potential errors produce and the hours to fix them. It might be a better solution, to produce readable and maintainable SQL code, even if it burns cloud dollars every month. In business intelligence and data warehouse environments performance is still important, but often maintainability is even more valuable. In certain real time surroundings only performance counts as “good” and you measure your execution time in milliseconds. Photo by National Cancer Institute on Unsplashĭo you want to write good SQL? Sure, but what does “good” mean actually?