SQL Good habits: Writing support for searchable SQL

1 overview

When the system is recently maintained, customers often feed back a message that adds a query condition to the query list. This requirement is not too much, and it seems that the workload is not very much, not just add a where condition, but when debugging to the DAL layer, immediately petrified-encountered in the SELECT clause to write a subquery sql,

SELECT So.order_no, (SELECTSUMfrom[stockorderdetails] as WHERE sod.order_id= as order_numfrom[stockorder  ] as

The disadvantages of this writing are as follows:

1>order_num cannot continue to use in select

2>order_num cannot be used in a WHERE clause (subqueries in select cannot be used in where)

3> Performance Issues

Here we discuss the 2nd: Order_num cannot be used in the WHERE clause

2 Why Order_num cannot be used in the WHERE clause?

Because the SELECT statement performs the following procedures:

1>. From
2>. WHERE
3>. GROUP by
4>. Having
5>. SELECT
6>. ORDER by

The WHERE clause is executed before the SELECT clause, so the column alias in Select, where is of course not recognized.

3. Example

Business: Purchase order query list, need to display the main information of the order, and also to show the total purchase quantity of each order;

Test data:

Searchable SQL is not supported:

SELECT So.order_no, (SELECTSUMfrom[stockorderdetails] as WHERE sod.order_id= as order_numfrom[stockorder]   as

Support for searchable SQL:

SELECTSo.order_no,ISNULL(Sod.order_num,0) asOrder_num from [Stockorder]  as So Left JOIN     (         SELECTORDER_ID,SUM(Good_num) asOrder_num from [stockorderdetails]         GROUP  byorder_id) asSod onSo.id=sod.order_idWHERESod.order_num>=8

When a subquery is quite complex, you can replace the left join part with the WITH AS (subquery part):

; withStockorderd as(    SELECTORDER_ID,SUM(Good_num) asOrder_num from [stockorderdetails]    GROUP  byorder_id)SELECTSo.order_no,ISNULL(Sod.order_num,0) asOrder_num from [Stockorder]  as So Left JOINStockorderd asSod onSo.id=sod.order_idWHERESod.order_num>=8

4 Summary

It's all about transforming the process of thinking into a set of thinking.

This article is mainly for the beginning of the formal writing SQL friends, in the development of some SQL to note the wording, you can add more flexibility, reduce the maintenance phase of the workload.

SQL Good habits: Writing support for searchable SQL