![]() And to do that, we need a GROUP BY, which can break the one-to-one relation needed for a JOIN. In other words, we need to first calculate the historical average. We can't replace this subquery with a JOIN because we don’t have a table with the average previously calculated. SELECT AVG(PH.production_in_kg / CYP.area) Production_in_kg / area AS "production_per_meter" This sounds complex, but it's easier than it seems. Let’s now suppose the owner of the company, after reading the results you delivered in the previous query, asks you to obtain the names of the farms that are producing more apples per square meter this year than the historical average. The result of both previous equivalent queries is: farm_nameĮxample: when subqueries are the only way to go Moreover, in a test database with only a few farms, both queries execute with an acceptable response time however, when we move to a productive database, (where the data volume is usually much higher), the response time of the subquery approach will increase significantly, while response time of JOIN approach will remain stable. ![]() Clearly, a subquery is inefficient for our purposes here. But what if you worked for a larger company that has 10,000 global farms? The subquery would need to be executed 10,000 times. In this case, we only have three farms, so the difference is negligible. While the JOIN clause in the second example needs to be executed only once, the subquery in the first example will be executed once per farm. The difference between these two approaches is in performance. The owner of the company wants you to obtain the names of the farms where the company is producing more apples in the current year than in the previous year (2017).ĪND CYP.production_in_kg > PH.production_in_kg Suppose you're an SQL data analyst working at EverRed. current_year_productionĮxample: replacing a subquery with a JOIN Below are some sample data from these two tables. The second table, production_history, stores past production information for each farm. The first table is current_year_production, which contains information about the number of apples produced in the current year by each farm, as well as the area and number of trees on each farm. The database has two tables representing the production statistics of a fictional apple farm company named EverRed. ![]() The dataīefore getting to the examples, let’s briefly look at the sample database we will use. In this article, I'll show examples of both cases: when a subquery is a must and when a subquery should be avoided and replaced by a JOIN. ![]() But of course, in some cases, using a subquery is the only way to solve a data question. If you can avoid a subquery and replace it with a JOIN clause, you should do so without hesitation. ![]() The following subquery displays the same result as the above JOIN statement.However, in some cases a subquery can be replaced with a more efficient JOIN. Now let’s convert this to subquery as shown below. The following query shows the JOIN query to know the languages spoken in a particular continent, for example, North America. In this example, we will use the Country and the CountryLanguage tables from the world MySQL database. Steps to convert a Subquery to a JOIN statement: However, subqueries will normally run slower than join statements. Sometimes its easy to construct a subquery rather than using a join. We can replace a join statement with a subquery. In this tutorial, we will learn steps to Convert Joins to Subqueries with an example. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |