Common SQL substitution points

1. If you can use DISTINCT, you do not need group.
SELECT OrderID FROM Details WHERE UnitPrice> 10 group by OrderID
Can be changed to: select distinct OrderID FROM Details WHERE UnitPrice> 10
2. Do not use UNION if union all is used.
Union all does not execute the select distinct function, which reduces unnecessary resources.
3. Try not to use the select into statement.
The select inot statement will lock the table and prevent other users from accessing the table.
4. If you can use BETWEEN, do not use IN.
5. Replace exists with in
Select num from a where num in (select num from B)
Can be changed to: select num from a where exists (select 1 from B where num = a. num)
6. Avoid performing expression operations (calculation, Format, and so on) on fields in the where clause. This will cause the engine to stop using the index for full table scanning.
For example, SELECT * FROM T1 WHERE F1/2 = 100
Should be changed to: SELECT * FROM T1 WHERE F1 = 100*2
SELECT * from record where substring (CARD_NO, 5378) = Limit bytes ‟
Should be changed to: SELECT * from record where CARD_NO LIKE „ 5378% rows ‟
SELECT member_number, first_name, last_name FROM members where datediff (yy, datofbirth, GETDATE ()> 21
Should be changed to: SELECT member_number, first_name, last_name FROM members WHERE dateofbirth <DATEADD (yy,-21, GETDATE ())
That is, any operation on the column will cause the table to scan, including database functions, calculation expressions, etc. During the query, try to move the operation to the right of the equal sign.
7. check whether a record exists in the table. Do not use count (*) as inefficient and a waste of server resources. It can be replaced by EXISTS.
For example, IF (select count (*) FROM table_name WHERE column_name = „ xxx rows ‟)
Can be written as: if exists (SELECT * FROM table_name WHERE column_name = „ xxx rows ‟)