Good Habit of SQL: Write SQL statements that support searching

1 Overview

During the recent maintenance of the system, the customer often reports that a query condition should be added to the query list. This requirement is not too much at all, and it seems that the workload is not great, it is not to add a where condition, but when debugging to the Dal layer, petrochemical now -- an SQL statement used to write subqueries IN THE select clause is encountered,

 
SelectSo. order_no ,(Select Sum(SOD. good_num)From [Stockorderdetails] AsSODWhereSOD. order_id=So. ID)AsOrder_numFrom [Stockorder] AsSo
 

The disadvantages of this writing method are as follows:

1> order_num cannot be used in select.

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

3> performance problems

Here we mainly discuss the second point: order_num cannot be used in the WHERE clause.

2 Why can't order_num be used in the WHERE clause?

Because the SELECT statement execution process is:

1>. From
2>. Where
3>. Group
4>. Having
5>. Select
6>. Order

The where clause is executed before the select clause. Therefore, the column alias in the select clause is not recognized in the WHERE clause.

3. Example

Business: to query the list of purchase orders, you must display the master information of the order and the total number of purchases for each order;

Test data:

The following SQL statements cannot be searched:

SelectSo. order_no ,(Select Sum(SOD. good_num)From [Stockorderdetails] AsSODWhereSOD. order_id=So. ID)AsOrder_numFrom [Stockorder] AsSo
 

Supports searchable SQL:

 Select So. order_no, Isnull (SOD. order_num,0 ) As  Order_num  From   [  Stockorder  ]   As  So  Left   Join  (  Select Order_id, Sum (Good_num) As Order_numFrom   [  Stockorderdetails  ]           Group   By  Order_id)  As SOD On So. ID =  SOD. order_id  Where SOD. order_num > =  8 

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

;With Stockorderd As  (  Select Order_id, Sum (Good_num) As Order_num From   [  Stockorderdetails  ]      Group   By  Order_id)  Select So. order_no, Isnull (SOD. order_num, 0 ) As  Order_num  From   [  Stockorder  ]   As  So  Left   Join Stockorderd As SOD On So. ID =  SOD. order_id Where SOD. order_num > =  8 

4. Summary

In general, it is to convert procedural thinking into collective thinking.

This articleArticleI am mainly writing to my friends who have just started to write SQL. I should pay attention to some SQL writing methods during development. I can add more flexibility and reduce the workload in the maintenance phase.