Wednesday, March 21, 2007

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);


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);


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;


Is there a way around this silliness that I have yet to discover?

No comments: