Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the common mistakes of SQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the common mistakes in SQL.

1. Execution order of Queries statements

The entry bar for SQL is very low. Many people think that they can claim to be experts by using Join and Group By statements. But do "experts" really know the order in which SQL statements are executed?

SQL queries do not start with SELECT, and although when we write the code, they are written from Select in the editor, but the actual execution does not start with SELECT.

The database first uses FROM and JOIN to execute the query, which is why we can use the values in the merge table in WHERE.

Why can't we filter the results of grouping (GROUP BY) in WHERE statements? Because GROUP BY is not executed until after WHERE. Therefore, we need to do further conditional filtering through HAVING.

Normally, SELECT is the last statement to be executed. It determines which columns need to be output from the query results and what kind of aggregation operations are performed. In addition, the WINDOW FUNCTION is executed at this step.

This is why an error occurs when we try to filter the results of WINDOW FUNCTION in WHERE.

Note: the database uses the query optimizer to optimize query execution. The optimizer may change the order of some operations so that the query runs faster. The above introduction is a general overview of what happens behind the scenes of SQL execution in common situations.

2. What does WINDOW FUNCTION do?

Many people seem mysterious when they meet WINDOW FUNCTION for the first time. Why use Window function as a grouping to aggregate data?

Window Function (WF) simplifies many steps with specific statements to make it easier for statements to run:

WF allows direct access to data before and after the current record. See the LEAD and LAG functions.

WF can use GROUP BY to perform additional aggregations on data that has been aggregated. See the example in the figure above: using WF to calculate mobile cumulative sales.

ROW_NUMBER can traverse each line. It can also be used to delete duplicate records. Or take a random sample.

As the name implies, WF can calculate statistics for a given window:

The WF above calculates the cumulative sum from the first record to the current record.

Window Function is worth your time to learn the basics, otherwise you are likely to write the query so complex that the program reports an error.

3. Using CASE WHEN to calculate the average

CASE WHEN is similar to IF conditional statements in programming languages. It works when we need to calculate statistics on a subset of the data.

The above code calculates the average price of products sold in the United States, but there is a slight problem with this code, that is, there is a problem with the writing of Else.

In the first statement, this code sets the price of all non-American products to 0, which reduces the overall average price. If there are many non-American products, the average price may be close to zero.

In the second example, it only calculates the average price of products sold in the United States, which is what I need. Note that you don't need to include ELSE when using CASE here, because it defaults to NULL.

Note that you must be careful with "else 0" when using the case statement. It has no impact on SUM, but has a significant impact on AVG average calculations.

4. Execute a JOIN statement on a column with missing values

There are four different kinds of JOIN in SQL: Inner, Outer, Left and Right. When we use JOIN in a query, it defaults to INNER JOIN.

Although I have some research and study on JOIN, I still make some small mistakes.

Execute the JOIN statement as shown in the figure above, and the result shows that many records are lost. Why is this? It is obviously a very simple JOIN statement.

The real reason is that there are many null values in the string_field columns in tables 1 and 2. People usually think that JOIN will keep the record of NULL because NULL equals NULL, don't they?

Then I run the following statement:

The result returns NULL.

In this case, in order not to lose the record with NULL, the solution should be to use COALESCE to convert the NULL in string_field into an empty string.

It is important to note, however, that doing so matches every record in Table 1 that contains NULL with each record in Table 2 that contains NULL.

By using the WINDOW FUNCTION of ROW_NUMBER (), we can remove these duplicate matches:

Suppose the data table has a unique identifier "so-and-so ID" and a timestamp field for each row.

Just keep the first line of each identifier so that duplicates can be deleted.

5. Temporary tables are not used for complex query statements

Another advantage of SQL is its ability to debug and correct errors.

We can split complex queries and create multiple temporary tables. You can then run an Integrity check on these tables to ensure that they contain the correct records. This method is highly recommended when designing a new and important query or report.

The only disadvantage of temporary tables is that the query optimizer in the database cannot optimize the query.

When you need to ensure the efficiency of execution, you can redefine and query the statements that create temporary tables with with statements.

At this point, I believe you have a deeper understanding of "what are the common mistakes of SQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.