This article was published as a part of the Data Science Blogathon.
Introduction
We all make mistakes and learn from them. It is a good practice to make mistakes but not repeat them in the future. While learning, we often encounter mistakes and try to resolve them, but at the beginning, we need guidance on which process to follow and which not to follow.
This article will describe all the common mistakes we can avoid by practicing SQL often and how to resolve them.
Top SQL Mistakes to Avoid
For an essay explanation, let’s considered demo data where the data is of sales and we have columns representing SalesId, SalesAmount, and SalesDate.
Explanation of Data
- SalesId: This column represents the sales ID of a particular product.
- SalesAmount: This column represents the sales amount of that particular product.
- SalesDate: This column represent when that sale was made with respect to date.
Let’s discuss some of the most common mistakes we do and see how to improve them for the proper execution.
Order of Execution of the Query
One of the crucial points that should not be ignored at any cost is the order of execution of a particular query. The order needs to be in the below format, or the output won’t be the desired one, and sometimes it even creates an error in the query.
- Getting the Data (From, Join)
- Filtering the Row (Where)
- Grouping (Group by)
- Group Filter (Having)
- Return Expressions (Select)
- Order & Paging (Order by & Limit / Offset)
Know the best plan for your query
Save yourself the trouble by planning and executing the command for the big queries. The last thing you want to do is execute a big query with too many nested loops.
Always make a habit of planning and structuring the query format and even testing out the query with the sample data.
Choose the right data type
In general, selecting the right data type for each column in the table is crucial. However, it is possible that an overflow can happen and the situation might not look pretty. Also, data type conversion will increase the query execution time, so it is a good habit to try to avoid data type conversions in your query if possible.
Never use Select *
Using the select * query for all the columns in the data elevates the execution time period of the query, and it is an expensive operation for humongous data. Not to use select * is mainly for performance and security reasons. For the following example.
# Wrong way SELECT * FROM Sales
The proper way for output is to select the relevant columns from the output table and select the relevant columns from table. For the following example.
# Optimal way SELECT SalesID, SalesAmount FROM Sales
For example, if we want to get the sales id from the sales table, then we should select only the SalesID column instead of selecting all the columns using select *, which will help to execute the query fast. The above statement query defines each column, and it also limits the size of every record.
Don’t Use the Distinct
The Distinct statement finds the unique rows corresponding to the selected columns by dropping duplicated rows from the table. The distinct clause is a cumbersome operation with the time in SQL, but we have an easy option for the problem.
# Wrong Way SELECT count(distinct SalesID) FROM Sales
We can use group by instead of distinct in SQL query to make the process faster and smoother. For example, the below queries find the count unique SalesID from the Sales details table.
# Optimal Way SELECT count(*) FROM (SELECT SalesID FROM Sales group by SalesID)
Preview your Result
Image Courtesy: xkcd.com
Imagine running a massive query, a computationally heavy query that returns Millions (M) of rows, only to realize at the end that we are calculating the wrong way and it is not the desired output.
# Wrong Way SELECT SalesID, SalesAmount FROM Sales
To resolve this problem, we use TOP / LIMIT to preview the result first to ensure we have desired results and are not wasting our time on the problem.
# Optimial Way
SELECT TOP 100
SalesID, SalesAmount
FROM Sales
Don’t use Having
We mostly use having clause to apply a filter on the aggregated columns having operations such as (sum, min, max, etc.) created using the group by operation. But sometimes, we use the ‘having’ clause instead of the ‘where’ clause to filter out the data from the table. For example, using having query.
# Wrong way SELECT count(SalesId), SalesAmount, SalesDate FROM Sales group by SalesDate having EmployeeID = 5
For example, to find the total sales by the total employees having employee id 5, let’s do while using group by for the result.
# Optimial Way SELECT count(SalesId), SalesAmount, SalesDate FROM Sales where EmployeeID = 5 group by SalesDate
Be careful of Joins
For assumptions, there can be multiple departments in sales with no employees. Let’s write a query to find the count of the number of employees in a particular department.
SELECT s.SalesName, COUNT(*) as EmployeeCnt
FROM SalesID s
LEFT JOIN dept_emp de ON d.dept_no = de.dept_no
GROUP BY d.dept_name
Notice that if we hadn’t done a left outer join on Dept_emp, it would’ve excluded the departments with no employees because there are no records with that dept_no in Dept_emp. On the other hand, if you don’t want to include those ghost departments in your result, then doing a left join would be redundant and wasteful.
Use EXISTS() Instead of COUNT()
Though you can use both EXIST() and COUNT() to discover whether the table has a specific record, using EXIST() is more effective. While COUNT() will search the entire table to provide the total number of matching records, EXIST() will only run until it locates the record’s first entry in the table, saving you time and computing power and enabling you to optimize SQL queries.
Know the correct operator Precedence
The order of precedence matters a lot while executing the query when we have more than one table which is joined with the join operation or any other operation. If we do not follow the order of precedence from the operator, we will not get the desired output as the query reading will be different. For example, we want to get the details of every employee having their first name “Ana” or “Joey”, with each having a salary of at least $10,000.
The correct query would look like this.
SELECT e.emp_no, e.first_name, e.last_name, s.salary FROM employees e JOIN salaries s ON e.emp_no = s.emp_no WHERE (e.first_name = "Ana" OR e.first_name = "Joey") AND s.salary >= 10000
Because without the parentheses in the query, AND operators would be executed first. Then the result will be all the Ana, regardless of salaries, and all the Joey who make at least $10,000.
Conclusion
We saw how we can make silly mistakes while writing the query through the article.
- We saw how to avoid this problem using the proper order of SQL query.
- Moreover, we need to use the correct parentheses in the query as it determines the format.
- Well, sometimes, we might forget how we need to write the SQL query for the required question. For example, while writing a query for joins misplace the query format or even not use the proper join format query.
- We even saw some simple query alternatives for a faster and better approach.
The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.
