Ad-Hoc Queries, Oracle, Using and Table-Qualified Wildcards
When I'm doing ad-hoc queries in Oracle as part of my development, debugging or research, I'll often make use of the USING clause. This lets me join two tables with a minimum of actual text.
SELECT *
FROM customer
INNER JOIN address USING (address_id);
FROM customer
INNER JOIN address USING (address_id);
This is more compact than specifying the id in both tables, given that our corporate nomenclature is <table>_id, so both tables tend to have columns with the same name.
Having done so, if I'm not exceptionally familiar with these tables, and haven't opened the table definition in another window, I may well want to find out the columns of one of the tables before deciding how to nail down the query. For instance, if I know I want some of the address, but I haven't decided which fields, I might do:
SELECT customer_name, address.*
FROM customer
INNER JOIN address USING (address_id);
FROM customer
INNER JOIN address USING (address_id);
At which point Oracle throws an ORA-25154. This is because address.* implies address.address_id, and Oracle doesn't like the idea that I might be qualifying a field that I've already indicated is the same as another field.
This frustrates me regularly. I'm not entirely sure why this is different from:
SELECT customer_name, address.*
FROM customer
INNER JOIN address ON customer.address_id = address.address_id;
FROM customer
INNER JOIN address ON customer.address_id = address.address_id;
Is there a way around this silliness that I have yet to discover?
No comments:
Post a Comment