1

EDIT: Managed to test it all and I guess my friend was wrong here, thanks for the help, people.

From what I understand the second example (with the alias) is useful when referring to an object type's field. For example, I went ahead and made an object table of managerType called manager with the following attributes:

Name NameType(name VARCHAR2(30), surname VARCHAR2(30),
Address AddressType(First_Line VARCHAR2(30), City VARCHAR2(30), Post_Code VARCHAR2(8),
Salary NUMBER)

Now for this question I needed to retrieve the city of a particular manager, so the following would not work:

SELECT City
FROM Manager
Where name like 'Jim';

However, this did work:

SELECT m.address.city from Manager m 
WHERE m.name.f_name LIKE 'Jim' AND m.name.l_name LIKE 'Smith';

There's probably a way I could avoid using the AND operator here, if anyone knows, I'd appreciate it. I hope this helps someone. Thanks.

Original Question

This has been confusing me for a while. I'm doing a past exam paper for revision and for one question I must retrieve the salaries of all managers whose names start with 'J' from a Manager Object table.

I'd normally do the following

SELECT salary
FROM Manager
WHERE name like 'J%';

Now a fellow student has gone and confused me by suggesting that I must use an alias as it's an object table, so it'd be something like:

SELECT m.Salary 
FROM Manager m 
WHERE name like 'J%';

I know this one is useful for field access in Oracle (say if I had a user defined type in Manager table and wanted to refer to a field in that type) but should I be using it in general?

Mo Moosa
  • 937
  • 2
  • 19
  • 39
  • You have a bunch of questions that have been answered and has obviously helped you, but you have not [accepted them](http://u.sbhat.me/t6SXUH). Please do else people may be not be inclined to help you – Sathyajith Bhat Jan 22 '12 at 15:18
  • 1
    You don't need to. Aliasing becomes important when you have the same filed listed multiple times in separate tables; or when you are joining to the same table multiple times. In this simple of an example it is not required. So someone suggesting that you MUST use an alias, in my opinion, is wrong. – xQbert Jan 22 '12 at 15:19
  • Ah sorry, still new to how this all works, thanks for letting me know. And yeah, they were wrong in this context. I actually managed to get back into my database I used for my project and test it all, the second example is only relevant when I need to refer to a UDT's own attribute, so for then it makes sense. Thanks for your help. – Mo Moosa Jan 22 '12 at 15:40
  • For adhoc queries, I only use aliases when necessary to differentiate between different tables or columns. In production code, however, I now use aliases for *every* table - a habit that has saved a lot of time that used to be wasted trying to debug SQL (especially SQL embedded in PL/SQL). – Jeffrey Kemp Jan 25 '12 at 14:41

3 Answers3

2

Aliases are only required if you're referencing the same table twice (or referencing an otherwise unnamed derived table)

Most people use them when dealing with multiple tables because it makes the query more readable that using long table names.

When dealing with a single table, I don't usually use them as its just extra text for no reason.

Gary - Stand with Ukraine
  • 7,081
  • 1
  • 30
  • 39
1

suggesting that I must use an alias as it's an object

Must is rather strong. You can use it, you probably will use it especially when performing a multi-table join so that you can refer to the tables by their short aliases instead of their long names.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
0

Alias is not compulsory. For our understanding purpose only we are using alias in sql query..

vijikumar
  • 1,805
  • 12
  • 16