Скачать или смотреть How to Properly Use SQL Aliases in Subqueries: A Guide for Your SQL Queries

Learn how to refer to aliases in SQL subqueries effectively, with practical examples and solutions for your SQL frustrations.
---
This video is based on the question https://stackoverflow.com/q/64391954/ asked by the user 'Fizban' ( https://stackoverflow.com/u/10006908/ ) and on the answer https://stackoverflow.com/a/64392003/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL Refer to alias in subquery

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding SQL Aliases in Subqueries

When working with SQL, especially in complex queries, many developers encounter challenges with aliases and subqueries. One common issue arises when trying to refer to an alias created in the main query within a subquery. In this post, we'll explore a specific example of this problem and provide a clear solution.

The Problem

Imagine you have a table structured like this:

customer_id (INT)

transaction_date (DATE)

income (INT)

Your goal is to create a summary that displays the total income for each distinct customer_id, but you want to exclude customers who only made transactions in the year 2014. You initially attempted the following SQL query:

[[See Video to Reveal this Text or Code Snippet]]

However, this query resulted in an error stating "Unknown column a_customer_id". The confusion here is a common misconception when trying to use aliases within subqueries.

The Solution

The solution is to restructure your SQL query. Instead of attempting to refer to the alias in a subquery, we can utilize GROUP BY along with a HAVING clause. This allows you to filter results after aggregation instead of trying to reference an alias that hasn't been fully established yet in the subquery context. Here’s a revised version of your query:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Solution

Selecting Columns:

We're selecting customer_id and summing the income, labeling it as sum_income.

Grouping Results:

Grouping the data by customer_id allows us to calculate the total income for each customer.

Using the HAVING Clause:

The HAVING clause is crucial; it ensures that only those customers whose transactions do not solely consist of the year 2014 are included.

MAX(YEAR(transaction_date) <> 2014) = 1 checks if there's at least one transaction date that's not in 2014. If this condition is met, it means that the customer has made transactions in years other than 2014, hence they are included in the final output.

Why This Works

The initial confusion stemmed from attempting to retrieve an alias in the subquery without it being established yet. The use of HAVING allows for the filtering of grouped data after aggregation, which neatly solves the problem.

Final Thoughts

Using SQL effectively means understanding how to structure your queries to avoid common pitfalls like referencing aliases incorrectly. By following the outlined solution, you can efficiently aggregate data and filter results according to your needs.

In summary, remember:

Aliases are not available in sub-queries unless they are fully defined.

Use HAVING for filtering after GROUP BY to get desired results.

With these adjustments, you should now be able to tackle your SQL queries more confidently!