Subquery alias and same tables?

Toad for Oracle & DevOps Toolkit
1

Greetings!

I am not able to figure out why the code below gives me 5543 rows of data whereas (please go to the sentence after the SQL statement)

…

SELECT SPRIDEN_ID StudentID, (SPRIDEN_FIRST_NAME || SPRIDEN_LAST_NAME) FullName, SPRHOLD_HLDD_CODE Code, STVHLDD_DESC Description

FROM SPRHOLD, STVHLDD, SPRIDEN, SGBSTDN A

WHERE SPRHOLD_TO_DATE >= SYSDATE
AND SPRHOLD_HLDD_CODE = STVHLDD_CODE
AND SPRHOLD_PIDM = SPRIDEN_PIDM
AND SPRHOLD_PIDM = SGBSTDN_PIDM
AND SPRIDEN_CHANGE_IND IS NULL
AND SGBSTDN_PRIM_ROLL_IND = ‘Y’
AND SGBSTDN_STST_CODE in (‘AS’,‘OW’,‘AW’,‘RA’, ‘DW’,‘PA’,‘SP’,‘CW’,‘WN’,‘DA’,‘DP’)
AND SGBSTDN_TERM_CODE_EFF = (SELECT MAX(SGBSTDN_TERM_CODE_EFF)
FROM SGBSTDN B
WHERE B.SGBSTDN_PIDM = A.SGBSTDN_PIDM
AND SGBSTDN_TERM_CODE_EFF <= 201520
)
;

…

Removing the alias from the parent select statement reduces rows of data to 2809. Specifically, the modified SQL looks like this: (Note: Adding an alias to both same table (SGBSTDN) gives me the same result as query result from query above i.e. 5543 rows.)

…

SELECT SPRIDEN_ID StudentID, (SPRIDEN_FIRST_NAME || SPRIDEN_LAST_NAME) FullName, SPRHOLD_HLDD_CODE Code, STVHLDD_DESC Description

FROM SPRHOLD, STVHLDD, SPRIDEN, SGBSTDN

WHERE SPRHOLD_TO_DATE >= SYSDATE
AND SPRHOLD_HLDD_CODE = STVHLDD_CODE
AND SPRHOLD_PIDM = SPRIDEN_PIDM
AND SPRHOLD_PIDM = SGBSTDN_PIDM
AND SPRIDEN_CHANGE_IND IS NULL
AND SGBSTDN_PRIM_ROLL_IND = ‘Y’
AND SGBSTDN_STST_CODE in (‘AS’,‘OW’,‘AW’,‘RA’, ‘DW’,‘PA’,‘SP’,‘CW’,‘WN’,‘DA’,‘DP’)
AND SGBSTDN_TERM_CODE_EFF = (SELECT MAX(SGBSTDN_TERM_CODE_EFF)
FROM SGBSTDN B
WHERE B.SGBSTDN_PIDM = SGBSTDN_PIDM
AND SGBSTDN_TERM_CODE_EFF <= 201520
)
;

…

I am guessing it has something to do with parent child table relationship but I can’t put my head around it. Any explanation would be appreciated.

2

It actually has to do with the fact that you have column names that are the same in both tables. So in your sub-query, where you have:

 WHERE B.SGBSTDN_PIDM = SGBSTDN_PIDM

It actually is looking in table B for both values instead of matching table A with table B.

On Thu, Apr 7, 2016 at 1:39 PM, jollydoe bounce-jollydoe@toadworld.com wrote:

Subquery alias and same tables?

Thread created by jollydoe
Greetings!

I am not able to figure out why the code below gives me 5543 rows of data whereas (please go to the sentence after the SQL statement)

...........................................................................................................................................................................................................................................................................................................

SELECT SPRIDEN_ID StudentID, (SPRIDEN_FIRST_NAME || SPRIDEN_LAST_NAME) FullName, SPRHOLD_HLDD_CODE Code, STVHLDD_DESC Description

FROM SPRHOLD, STVHLDD, SPRIDEN, SGBSTDN A

WHERE SPRHOLD_TO_DATE >= SYSDATE
AND SPRHOLD_HLDD_CODE = STVHLDD_CODE
AND SPRHOLD_PIDM = SPRIDEN_PIDM
AND SPRHOLD_PIDM = SGBSTDN_PIDM
AND SPRIDEN_CHANGE_IND IS NULL
AND SGBSTDN_PRIM_ROLL_IND = 'Y'
AND SGBSTDN_STST_CODE in ('AS','OW','AW','RA', 'DW','PA','SP','CW','WN','DA','DP')
AND SGBSTDN_TERM_CODE_EFF = (SELECT MAX(SGBSTDN_TERM_CODE_EFF)
FROM SGBSTDN B
WHERE B.SGBSTDN_PIDM = A.SGBSTDN_PIDM
AND SGBSTDN_TERM_CODE_EFF <= 201520
)
;

...........................................................................................................................................................................................................................................................................................................

Removing the alias from the parent select statement reduces rows of data to 2809. Specifically, the modified SQL looks like this: (Note: Adding an alias to both same table (SGBSTDN) gives me the same result as query result from query above i.e. 5543 rows.)

...........................................................................................................................................................................................................................................................................................................

SELECT SPRIDEN_ID StudentID, (SPRIDEN_FIRST_NAME || SPRIDEN_LAST_NAME) FullName, SPRHOLD_HLDD_CODE Code, STVHLDD_DESC Description

FROM SPRHOLD, STVHLDD, SPRIDEN, SGBSTDN

WHERE SPRHOLD_TO_DATE >= SYSDATE
AND SPRHOLD_HLDD_CODE = STVHLDD_CODE
AND SPRHOLD_PIDM = SPRIDEN_PIDM
AND SPRHOLD_PIDM = SGBSTDN_PIDM
AND SPRIDEN_CHANGE_IND IS NULL
AND SGBSTDN_PRIM_ROLL_IND = 'Y'
AND SGBSTDN_STST_CODE in ('AS','OW','AW','RA', 'DW','PA','SP','CW','WN','DA','DP')
AND SGBSTDN_TERM_CODE_EFF = (SELECT MAX(SGBSTDN_TERM_CODE_EFF)
FROM SGBSTDN B
WHERE B.SGBSTDN_PIDM = SGBSTDN_PIDM
AND SGBSTDN_TERM_CODE_EFF <= 201520
)
;

...........................................................................................................................................................................................................................................................................................................

I am guessing it has something to do with parent child table relationship but I can't put my head around it. Any explanation would be appreciated.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

3

Without the alias in the 2nd query, it’s unclear if the unaliased fields in the subquery refer to the SGBSTDN table that’s in the main query or in the subquery. It appears that without the alias, the unaliased fields in the subquery are referring to the SGBSTDN table in the subquery (and subject to the terms of that where clause) rather the one in the main query.

You really should use aliases when multiple tables are involved to prevent ambiguity (and make your queries easier to understand for people who don’t know which columns belong to which tables)

4

Phyllis beat me to it!

5

Thank you Phyllis & John for your prompt response.

I will try to add an alias to every table in a query. I still have a question about aliases.

Isn’t B.SGBSTDN_PIDM different than SGBSTDN_PIDM? One has alias and the other does not. Isn’t that different enough for Oracle to figure out?

6

To ask my above question differently, why does the order of the alias matter? Specifically, if I assign an alias to the parent select table and not assign an alias to the subquery select table, I get 5543 rows but if I switch the alias the other way round i.e no alias for the parent select table but alias for the subquery select table, then I get 2809 rows. Why does it matter that I assign an alias to the parent? To me, the tables are exactly the same. As such, as long as a differentiation is made between these two tables by assigning one table an alias and no alias for the other table, the query should work.

I may sound repetitive but that is not my intent. I want to understand the underlying principle so that I can answer this question when someone else asks me.

7

I may have found answers to my questions. According to Oracle Database SQL Language Reference docs.oracle.com/…/queries007.htm),

“If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.”

I read the above statement as something that is imposed by Oracle SQL architecture and we must adhere to it. From a logical perspective, it does not make sense to me but then there are many things considered logical that do not make sense to me.

Verification by an Expert would be appreciated.

8

Hi JollyDoe.

Aliases. If you have a query that selects data in columns having the same name in two (or more) different tables, the rule is pretty simple, give each table an alias, and when you select the columns, prefix each duplicate column name with the alias for the table that you wish to see that particular column’s data.

For example, joining a hypothetical employee table with a department table, both have dept_id columns and both have name columns:

select emp.dept_id, emp.name, emp.stuff, dept.name

from employee emp

join department dept

on (emp.dept_id = dept.dept_id)

where emp.name like ‘Norm%’

and …

order by dept.dept_id, emp.name;

Or something like that. You must tell oracle exactly which table’s dept_id or name that you want in the select list or the where clauses or the order by clauses etc, or the results are, “undefined”.

In the above each mention on name or dept_id must be prefixed with an alias or carnage reigns. The other columns don’t need an alias, but I think it’s neater to use one, and shows exactly where the data are being read from. You don’t have to go DESCing or F4ing the two tables to find the un-aliased columns, for example.

You don’t need aliases on your tables if ALL columns are unique.

You don’t need aliases either if the selected columns, where clauses, order by, group by etc never mention any of the duplicated column names.

HTH

Cheers,

Norm. [TeamT]

9

Isn't B.SGBSTDN_PIDM different than SGBSTDN_PIDM? One has alias and the other does not. Isn't that different enough for Oracle to figure out?

Not always. Using an alias tells Oracle which table the column is in, but the lack of alias does NOT mean "use the table that I didn't provide an alias for".

So in your subquery -

SELECT MAX(SGBSTDN_TERM_CODE_EFF)

FROM SGBSTDN B

WHERE B.SGBSTDN_PIDM = SGBSTDN_PIDM

AND SGBSTDN_TERM_CODE_EFF <= 201520

when Oracle sees SGBSTDN_PIDM in the first line of your where clause, it looks back to the table in the nearest FROM, and sees there is a column by that name in the SGBSTDN table, so that's the one it uses. If you want to tell Oracle to use a table in the main query, you need to use an alias to be specific about that.

10

Thank you Norm & John for responding.

Norm, my question was not whether an alias is needed or not. My questions was why does Oracle insist on assigning an alias to the outer query. If you look at my example, I have posted results of my testing. I performed 3 tests for alias ranking using above query.

First test: I assigned aliases to table used in both outer query and subquery. I got 5543 rows.

Second test: I kept the table alias of the outer query and removed the table alias from the subquery. I got 5543 rows.

Third test: I removed the table alias from the outer query and kept the table alias of the subquery. I got 2908 rows.

My question was why not produce same results if my subquery has the alias as opposed to the outer query.

To me, John’s explanation makes sense. Additionally, according to Oracle 10g Database SQL reference Using Subqueries section, docs.oracle.com/…/queries007.htm,

“If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias.”

The logic behind this requirement does not make sense to me but at least I know that Oracle requires me to do it.

11

Morning All,

apologies, first, for misreading your question. Sorry. English isn’t my first language. I’m a Scot! :wink:

I would be willing to bet that there are 5543 rows in the correctly formed query results.

As John said, when there is ambiguity in column names, the parser/optimiser may choose any table as the “correct” one for the ambiguous columns. At least Oracle documents which one it will use inn these cases. Personally, I’d prefer an error about ambiguous columns myself.

Your first test is the obvious correct one. You use an alias on the outer and inner queries. All is well, there’s no ambiguity and Oracle returns the correct results.

Your second test is also correct, but not necessarily easier to read - someone reading it who knows where the columns are might get confused as to which table the un-aliased columns come from - if they havent read the docs. In this case, the alias should be used “merely” as a comment, if you like.

Your third test simply breaks Oracle’s rules and gives incorrect results. There’s really nothing more to it than that. :slight_smile:

HTH

Cheers,

Norm. [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

12

I'm not sure why this doesn't seem logical to you. You don't want to let Oracle guess what you are talking about.

To put this another way, if I tell you that you need to invite people from the Smith and the Jones families, specifically John and Pat and James and there is a John Smith and a John Jones, how could you know which John I mean without me giving a last name? It is the same thing for Oracle. If a field is in two tables with the same name and you don't tell Oracle which table to select it from, how could it know?

The previous team I was on made sure that each table used a unique prefix to all the columns so there was never the need to alias a table for this. My current database does not use prefixes. I have found that using the alias before every column from that table is actually very nice because then I know which table the column comes from without having to look, even if it only exists in one of the tables. When you have hundreds of tables, you don't memorize things like that. :slight_smile:

On Wed, Apr 13, 2016 at 2:43 AM, jollydoe bounce-jollydoe@toadworld.com wrote:

RE: Subquery alias and same tables?

Reply by jollydoe
I may have found answers to my questions. According to Oracle Database SQL Language Reference docs.oracle.com/.../queries007.htm),

"If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view."

I read the above statement as something that is imposed by Oracle SQL architecture and we must adhere to it. From a logical perspective, it does not make sense to me but then there are many things considered logical that do not make sense to me.

Verification by an Expert would be appreciated.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Phyllis Helton

Data Magician
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office :phone: 407-515-4452

phyllis.helton@cru.org

13

In addition to what Phyllis said - if the same table is in two different places in a query (in this case, once in a main query and once in a subquery), then each instance of the table will have a different ‘where’ clause acting on it - so there is more to it than just finding which table the column belongs to.

14

Greetings Norm, phyllis, & John,

Thank you for responding to my comments.

Norm, I thought English originated in the great land of Scots. Additionally you would win money if you had bet money on 5543 being the correct number of rows.

phyllis, let me see if I can use your example to present my logic. If I know that I am only inviting two Johns (John Smith & john Jones) and I am addressing one of them as John Jones, then I know that the other one is John Smith.

Similarly, if I am using the same table only twice & I assign an alias to one of them, whether outer query or subquery, Oracle should know that the unassigned table is the remaining table and proceed from there. As I said before, Oracle does make it clear that in cases like mine, I must assign an alias to the outer query, not the subquery.

In my current job, almost all (I would say 98%) columns I deal with are prefixed with table name which I think is a great idea. All table names are 7 letters. It certainly makes it easier to understand the source table of almost any column.

15

Hi Jollydoe,

You know that the other John is Jones, because you know who both of them are from prior knoledge, just as you know which table you mean when you select from John. However, oracle doesn’t have such prior knowledge, when you say John, oracle has to look up the data dictionary to find John.

Run a 10046 level 4 trace, to get binds, or dbms_monitor etc, and you’ll see how much work oracle has to do just to see if a table exists, as the columns you mention, check your privs etc etc.

It has no prior knowledge at all, it’s pretty much like you have just introduced the two John’s to Oracle, which if it’s anything like me, will immediately or get which one is which!

HTH

Cheers,

Norm. [ TeamT ]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

16

Thank you Norm for additional explanation. I assumed that like me, Oracle would have prior knowledge about Johns because the table resides in Oracle. Now I see why it is not easy for Oracle to distinguish between two Johns. Additionally, I have only dealt with writing queries. I should look into traces, explain plan, etc. to understand the complexity of identifying objects in Oracle.