Скачать или смотреть How to SELECT from a Subquery Yielded Table in MySQL

Learn how to correctly use a `SELECT` statement on a subquery in MySQL, avoid common errors, and improve your SQL skills.
---
This video is based on the question https://stackoverflow.com/q/67608590/ asked by the user 'asleniovas' ( https://stackoverflow.com/u/7340130/ ) and on the answer https://stackoverflow.com/a/67608841/ provided by the user 'Akina' ( https://stackoverflow.com/u/10138734/ ) 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: How to correctly SELECT from subquery yielded table

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.
---
How to SELECT from a Subquery Yielded Table in MySQL

When working with SQL, especially if you’re new, you might encounter issues when trying to pull data from a subquery. A common error occurs when trying to select columns with duplicate names. In this post, we'll break down a commonly faced scenario: how to correctly SELECT data from a table generated by a subquery, specifically focusing on resolving the “Duplicate column name” error.

Understanding the Problem

Imagine you have a complex query where you join multiple tables to derive a new column, usd_equivalent. However, when executing a SELECT statement on the results of this subquery, you run into an error:

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

This error indicates that the subquery you’re trying to pull results from has multiple columns with the same name. This can happen when you select all columns (SELECT *) from multiple tables involved in the JOIN, leading to ambiguity in your result set.

The Original Query

Here’s the problematic query you might be working with:

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

In this case, it’s clear we’re attempting to filter results based on the usd_equivalent column, but we’re inadvertently fetching columns with duplicate names.

Solution: Using HAVING Instead of WHERE

A more effective approach is to use the HAVING clause instead of applying a WHERE clause to the subquery. This allows you to filter results after the aggregate calculations have been completed. Here’s how you can adjust your query:

Revised SQL Query

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

Key Changes

Avoid SELECT *: Instead of selecting all columns, be explicit about which columns you want to retrieve alongside usd_equivalent.

Using HAVING: Implement the condition on the usd_equivalent calculation using HAVING, which is appropriate after grouping and aggregations.

Benefits of This Approach

Performance: This method can improve performance by reducing the amount of data processed in the query.

Clarity: Making your intentions explicit by selecting only relevant columns increases the readability of your code.

Avoiding Errors: By handling duplicates explicitly, you reduce the risk of encountering errors.

Conclusion

Working with subqueries in SQL can be tricky, especially when it comes to selecting data without running into errors. By using the HAVING clause instead of an ambiguous WHERE, and by avoiding the use of SELECT *, you can effectively filter your query results while maintaining clarity and performance.

Now you can confidently execute your queries without worrying about common pitfalls. Happy querying!