<\/span><\/h2>\n\n\n\nThere will be times when you come across a complex query with multiple SELECT clauses. In this scenario, we need to understand the query’s ultimate result, or the final (first) SELECT statement. The trick is to understand what the query output is intended to be.<\/p>\n\n\n\n
Let\u2019s Try out one more code:<\/p>\n\n\n\n
WITH customerspending AS (\n SELECT \n customers.id, \n SUM(purchases.price) as total_spending \n FROM \n purchases \n JOIN customers ON purchases.cust_id = customers.id \n GROUP BY \n customers.id\n) \nSELECT \n c.name, \n pd.product, \n pd.total_product_price, \n cs.total_spending \nFROM \n (\n SELECT \n purchases.cust_id, \n purchases.product, \n SUM(purchases.price) as total_product_price \n FROM \n purchases \n WHERE \n purchases.category = 'kitchen' \n GROUP BY \n purchases.cust_id, \n purchases.product \n HAVING \n SUM(purchases.price) > 10000\n ) AS pd \n JOIN customers c ON pd.cust_id = c.id \n JOIN customerspending cs ON c.id = cs.id \nORDER BY \n pd.total_product_price DESC;<\/strong><\/code><\/pre>\n\n\n\nThe query is more complex and lengthy now, but the primary attention should be on the final SELECT<\/strong>, which appears to attempt to produce the customer’s entire spending and purchase history. Try to determine what the desired final result is and break it down from there.<\/p>\n\n\n\n<\/span>3. The Final Condition Clause<\/strong><\/span><\/h2>\n\n\n\nThe inquiries provide us with information into what the result should be. The next step is to determine the conditions for the final SELECT<\/strong> statement. The conditions clause, which included WHERE<\/strong>, GROUP BY<\/strong>, ORDER BY<\/strong>, and HAVING<\/strong>, determined the overall data result.<\/p>\n\n\n\nTry to read and grasp the conditions of our query, and we will have a better understanding of the final answer. For example, in our previous SQL query<\/strong>, the final condition is simply ORDER BY<\/strong>. This signifies that the final result will be sorted by total product price in descending order.<\/p>\n\n\n\nKnowing the final conditions will help you grasp a substantial portion of the query and the overall query intent.<\/p>\n\n\n\n
<\/span>4. The Final Join<\/strong><\/span><\/h2>\n\n\n\nFirst and foremost, we must understand the source of the data. After we’ve determined which data to select and under what conditions, we need to understand the source. The last JOIN<\/strong> clause would help us understand how the tables connect and how data flows.<\/p>\n\n\n\nFor example, the prior complex query indicates that we used Join twice. This signifies that we used at least three data sources to produce the final result. This information will be required in the subsequent steps to better understand how each data source is obtained, particularly when the data source is derived from the subquery.<\/p>\n\n\n\n