Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Style with Table Alias (Oracle, 12.2.0.1.0, Linux)
| Oracle Style with Table Alias [message #687573] |
Fri, 07 April 2023 16:22  |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Just a friendly discussion about Table Alias.
I've been told this is bad programming and difficult to read and determine what the query is doing.
The discussion is about using an Alias of A and B repeatedly. This is usually how I would write a query but others claim they have difficulty following the query or verifying the query because A and B are being used multiple times.
Is this really a No-No? Everything should be written with a WITH clause or a query should use A, B, C, D, E, F..... to be unique?
I'm trying to understand where they are coming from but I don't see anything wrong with the query I've written. To them, everything should be written using WITH.
select a.something
from (select a.something
from (select 'X' something
from dual) a,
(select 'X' something
from dual) b
where a.something = b.something) a,
(select a.something
from (select 'X' something
from dual) a,
(select 'X' something
from dual) b
where a.something = b.something) b
where a.something = b.something
|
|
|
|
| Re: Oracle Style with Table Alias [message #687574 is a reply to message #687573] |
Fri, 07 April 2023 17:20  |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I am kind of wishy-washy and I guess it mostly depends on the circumstances.
If I am correcting or modifying somebody else's code, I will usually make minimal changes and keep whatever they have that runs. If it is confusing then I may add comment lines.
When the WITH clause was first introduced, I found there were a lot of things that would work with inline views (sub-queries in the FROM clause) that would not work with WITH clauses. So, I was slow to start to use WITH. Nowadays, I find that, when answering questions on the forums, rather than clutter my database with other people's tables or do a lot of creating and dropping of tables, it is convenient to list the test data using WITH clauses, then list the query. In cases where you need to re-use or join the same select statement multiple times, it is definitely convenient to use WITH clauses.
I usually try to create aliases that are short but meaningful. Alternatively, I may use t1, t2, t3, etc. If using XMLTABLE's, then I may use xt1, xt2, xt3, etc. For external tables, I may use ext1, ext2, ext3, etc.
If you have something and you realize that you need another one just like it, then it is definitely easier to just copy and paste and not bother with changing aliases, as long as it is clear to Oracle and not ambiguous.
There are a number of things that I got in the habit of doing early on and later decided that there was a better way, but still find myself doing things the old way. For example, I tend to put my commas where you would writing in English, so I do it kind of like the old SEQUEL (structured English query language), as shown below.
I have found that when creating code dynamically, and adding columns, the comma really belongs with the additional column, not the one before so it is better to do:
SELECT col1
, col2
, col3
I try to do things in the same manner, allowing for more. Some people have asked me why I do:
instead of:
It is because I am allowing for additional tabs and want to have everything lined up:
WITH
tab1 AS
(...),
tab2 AS
(...),
tab3 AS
(...)
WITH
tab1 AS
(...)
, tab2 AS
(...)
, tab3 AS
(...)
|
|
|
|
| Re: Oracle Style with Table Alias [message #687576 is a reply to message #687574] |
Fri, 07 April 2023 18:36 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
Thank you. That was insightful. I write queries as I have shown but, here recently, I've gotten a lot of push back on this style with other developers claiming they can't follow the query. They claim they get confused by the multiple A, B, C alias and don't know which A goes where since there can be 2 or 3 or 4. That shocked me. I'm guessing that you could follow my test query and know which A is which A.
They use the style and formatting in your last example. That's ok, I can follow it but they don't format any statements or line anything up. They use a WITH but the statements are all over the place.
This was good feedback. I suppose I should start using the WITH clause.
|
|
|
|
| Re: Oracle Style with Table Alias [message #687579 is a reply to message #687576] |
Fri, 07 April 2023 20:32 |
 |
mathguy
Messages: 108
Registered: January 2023
|
Senior Member |
|
|
First, as to "anything wrong with the query" - you did very well to frame the question. You didn't ask about the technical correctness of the construct, but rather about "style". Technically there is nothing wrong; the syntax allows it.
I would argue that the style is flawed. It may lead to difficulty even for yourself in the future, and definitely for others who will need to maintain your code. Consider this example:
Tables: T(ID, VAL, DESCR) and S(ID, DESCR, VAL, QTY). VAL in table T has no relation to VAL in table S, even for the same ID. Same with DESCR.
Query:
select t.id, t.val, s.descr
from (select t.id, s.val from t join s on t.id = s.id where s.qty > 100) t
join
t s
on t.id = s.id
;
Now try to read the SELECT clause. ID comes from the inline view T, and if we read along, we see it comes from table T (or S - we did an inner join). T.VAL is the one that comes from table S, not the one from T, and S.DESCR is actually that from table T, not from S! Perhaps this is not confusing to you, but it is to me.
Now suppose you want to order the result of the above query by the values in the VAL column of table T. They are visible, since you joined to table T in the FROM clause; you are not selecting them, but they can be used for ordering. What do you put in the ORDER BY clause? Note - even though you are selecting T.VAL, you can't simply order "BY VAL" - the VAL in the output of the query is that from table S, even though in SELECT it must be written as T.VAL. Instead, you must ORDER BY S.VAL - to indicate column VAL in table T! This becomes confusing enough for me to object to that style - even though technically everything is 100% correct.
|
|
|
|
| Re: Oracle Style with Table Alias [message #687585 is a reply to message #687579] |
Sat, 08 April 2023 17:54 |
Duane
Messages: 585
Registered: December 2002
|
Senior Member |
|
|
That was good feedback. My query wasn't as complex as yours.
Here's what I was up against. Another person was reviewing my code so it could be moved into production.
Taking my query as an example, this person couldn't determine where "select a.something" was coming from.
I've put numbers by an alias.
select a.something
from (select a.something
from (select 'X' something
from dual) a (1),
(select 'X' something
from dual) b (2)
where a.something = b.something) a (3),
(select a.something
from (select 'X' something
from dual) a (4),
(select 'X' something
from dual) b (5)
where a.something = b.something) b (6)
where a.something = b.something
A (1) directly bypassing A (3)
A (3) directly
A (4) directly bypassing B (6)
It was the craziest question.
To get the code reviewed and put into production I had to do the following so this person knew exactly where "select a.something" was coming from. Any alias had to be unique. Once I did this, the code was approved and moved to production.
select c.something
from (select a.something
from (select 'X' something
from dual) a,
(select 'X' something
from dual) b
where a.something = b.something) c,
(select d.something
from (select 'X' something
from dual) d,
(select 'X' something
from dual) e
where d.something = e.something) f
where c.something = f.something
[Updated on: Sat, 08 April 2023 17:55] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat Apr 26 04:50:24 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
