Скачать или смотреть Understanding SQL Subqueries and Row Numbering with MySQL

Explore the nuances of SQL queries, subqueries, and row numbering in MySQL to avoid common pitfalls and improve your database management skills.
---
This video is based on the question https://stackoverflow.com/q/64746483/ asked by the user 'user2894829' ( https://stackoverflow.com/u/2894829/ ) and on the answer https://stackoverflow.com/a/64746661/ provided by the user 'Akhilesh Mishra' ( https://stackoverflow.com/u/7772483/ ) 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: A column in a subquery does not appear

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 Subqueries and Row Numbering with MySQL

When we dig into the world of SQL, particularly in MySQL, we sometimes encounter situations that are difficult to navigate. A common problem arises when a column in a subquery doesn't appear as expected. This leads to confusion and can impede our progress. In this guide, we’ll explore a specific query example that highlights this issue, answer some critical questions, and help clarify the way MySQL handles subqueries and row numbering.

Problem Statement: Missing Column in a Subquery

Consider the following SQL query executed in MySQL 5.7:

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

While executing this query, you notice that there is no error, but column B doesn’t show up in the output. Moreover, when trying to select b in the outer query, you encounter an "unknown column" exception.

Your Questions:

Why does (SELECT (@ rowNum :=0)) not appear?

Is (@ rowNum:=@ rowNum+ 1) equivalent to row_number() over () in Oracle?

Why does placing (SELECT (@ rowNum :=0)) in the SELECT list impact the row number column?

Let’s break these down one by one!

Question 1: Why Does (SELECT (@ rowNum :=0)) Not Appear?

Answer:

In the subquery, you've joined (SELECT (@ rowNum :=0)) AS B but you've not included it in your column list after SELECT. As a result, it's not shown in the output.

Key Points:

The subquery runs but does not explicitly call B in the output selection.

The increment operation (@ rowNum:=@ rowNum+ 1) is what generates the rowNo, starting from 1.

Question 2: Is (@ rowNum:=@ rowNum+ 1) Equivalent to row_number() over () in Oracle?

Answer:

Yes, in many respects, (@ rowNum:=@ rowNum+ 1) does perform a similar function as row_number() over () in Oracle.

Understanding the Row Numbering:

Initialization: The variable @ rowNum is initialized to 0 at the beginning of the query with (SELECT (@ rowNum :=0)).

Incrementing: Each time a new row is processed in the select statement, (@ rowNum:=@ rowNum+ 1) adds 1 to @ rowNum which then effectively acts like an auto-incremented row number.

Question 3: Impact of Moving (SELECT (@ rowNum :=0)) to the Left

Answer:

When you include (SELECT (@ rowNum :=0)) AS B in the field list of your outer SELECT statement, the value of @ rowNum gets reset to 0 for each returned row. Hence, each row results in rowNo being generated as 1, and you won’t see an incremented row number.

Clarification:

If Initialized in SELECT: When included in the SELECT clause, it initializes @ rowNum to 0 every time, thus nullifying the cumulative effect of incrementing it.

Conclusion

Navigating SQL subqueries can be tricky, especially when it comes to variable initialization and output columns. By understanding how MySQL handles these nuances, we can write more efficient queries and avoid common pitfalls.

In summary:

Always call the output column in your SELECT statement to see it in the results.

Understand how MySQL's user variables operate, particularly in relation to row numbering.

Be mindful of where and how you initialize variables in your SQL queries, as it can significantly affect your results.

By clarifying these areas, you'll be better equipped to manage your database effectively and leverage the power of SQL fully. If you have more questions or need further assistance, feel free to reach out!