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

This article mainly introduces "how to improve query skills". In daily operation, I believe many people have doubts about how to improve query skills. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "how to improve your query skills"! Next, please follow the editor to study!

Let's take a fictional business as an example. Suppose you are a member of Amazon's e-commerce analysis team and need to run a few simple queries. You have two tables on hand, which are "product" and "discount".

1. Calculate the number of NULL fields

In order to calculate the number of null fields, it is necessary to master how the COUNT function works. Suppose that when calculating the number of products, you are required to include the missing fields in the "product id" primary key column of the table "product".

SELECT COUNT (product_id) FROM product;Result: 3

Due to the null value required to be counted in the "product id" column, the query result should be 4, but the COUNT () function does not count the null value.

Solution: use the COUNT (*) function. This function counts the null value.

Select Count (*) From product;Result: 4

This operation is simple, but it is always ignored when writing complex queries.

two。 Use reserved words as column names

SELECT product_id, RANK () OVER (ORDER BY price desc) As Rank FROM product

The query result is incorrect because the column name "Rank" is a reserved word for the Rank function.

Solution:

SELECT product_id, RANK () OVER (ORDER BY price desc) As' Rank' FROM product

Add single quotation marks to get the desired result.

Comparison operation of 3.NULL

SELECT product_name FROM product WHERE product_id=NULL

Due to the use of the comparison operator "=", an exception occurs in the operation here, as well as in the operation using another comparison operator, "! =". The logical problem here is that the query you write shows whether the value of the "product id" column is unknown, but not whether the value of this column is an unknown product.

Solution:

SELECT product_name FROM product WHERE product_id ISNULL

The difference between 4.ON clause filtering and WHERE clause filtering

This is a very interesting concept that will improve your basic understanding of the difference between ON clause filtering and WHERE clause filtering. This is not entirely a mistake, it just demonstrates the use of both, and you can choose the best solution according to your business needs.

SELECT d.product_id, price, discount FROM product p RIGHT JOIN discount d ON p.product_id=d.product_id WHERE p.product_id > 1

Results:

In this case, the filter condition takes effect after the two tables are joined. Therefore, the result does not contain rows for d.product_id ≤ 1 (in the same way, it obviously does not contain rows for p.product ≤ 1).

Solution: use AND and pay attention to the differences in results.

SELECT d.product_id, price, discount FROM product p RIGHT JOIN discount d ON p.product_id=d.product_id AND p.product_id > 1

Results:

Here, the conditional statement AND is calculated before the join of the two tables occurs. Think of this query as a WHERE clause that applies to only one table (the "product" table). Now, due to the right join, there is a row of d.product_id ≤ 1 (and obviously a row of p.product_id > 1) in the result.

Note that ON clause filtering and WHERE clause filtering are different only in left / right / outer joins, but the same in inner joins.

5. Columns generated by using the Windows function in the WHERE clause of the same query & using the CASE WHEN clause

Note that column names generated by the Windows function and the CASE WHEN clause are not used in the WHERE clause of the same query.

SELECT product_id, RANK () OVER (ORDER BY price desc) AS rk FROM product WHERE rk=2

Because the column rk is generated by the Windows function and is used in the WHERE clause of the same query, the query result is abnormal.

Solution: this problem can be solved by using temporary tables or subqueries.

WITH CTE AS (SELECT product_id, RANK () OVER (ORDER BY price desc) AS rk FROM product) SELECT product_id FROM CTE WHERE rk=2

Or

SELECT product_id FROM (SELECT product_id, RANK () OVER (ORDER BY price desc) AS rk FROM product;) WHERE rk=2

The same method applies to columns created using the CASE WHEN clause. Keep in mind that Windows functions can only appear in SELECT or ORDER BY clauses.

Incorrect use of 6.BETWEEN

If you don't know the valid scope of BETWEEN, you may not get the query results you want. The valid range of BETWEEN x AND y statements includes x and y.

SELECT * FROM discount WHERE offer_valid_till BETWEEN '2019 AND' 2020 Universe 01 'ORDER BY offer_valid_till

Results:

In the query, maybe we only want to get all the dates in 2019, but the results also include January 1, 2020. This is because the valid scope of the BETWEEN statement includes 2019-01-01 and 2020-01-01.

Solution: adjusting the scope accordingly can solve this problem.

SELECT * FROM discount WHERE offer_valid_till BETWEEN '20119 AND' 2019 AND '2019 Universe 12 Universe 31' ORDER BY offer_valid_till

Results:

The results of all queries are now 2019.

7. Use the WHERE clause after the GROUP BY statement

When writing GROUP BY statements, pay attention to the location of the WHERE clause.

SELECT category, AVG (price) FROM product p INNER JOIN discount d ON p.product_id=d.product_id GROUP BY category WHERE discount_amount > 10

This query is incorrect because the WHERE clause is placed after the GROUP BY statement. Why is that?

The WHERE clause is used to filter query results, which is done before grouping query results, rather than grouping and then filtering. The right thing to do is to apply WHERE conditional filtering to reduce the data, and then use the GROUP BY clause to group the data through the aggregate function (here, the aggregate function AVG).

Solution:

SELECT category, AVG (price) FROM product p INNER JOIN discount d ON p.product_id=d.product_id WHERE discount_amount > 10 GROUP BY category

Note the order in which the main SQL statements are executed:

FROM clause

WHERE clause

GROUP BY clause

HAVING clause

SELECT clause

ORDER BY clause

At this point, the study on "how to improve query skills" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.