CODEWITHSUNDEEP
sqloracle-databasejoinoracle10g
BenoitParis
BenoitParis

Reputation: 3184

'Implicit' JOIN based on schema's foreign keys?

Hello all :) I'm wondering if there is way to tell the database to look at the schema and infer the JOIN predicate:

+--------------+     +---------------+
| prices       |     | products      |
+--------------+     +---------------+
| price_id (PK)|  |-1| product_id(PK)|
| prod_id      |*-|  | weight        |
| shop         |     +---------------+
| unit_price   |
| qty          |
+--------------+

Is there a way (preferably in Oracle 10g) to go from:

SELECT * FROM prices JOIN product ON prices.prod_id = products.product_id

to:

SELECT * FROM pricesIMPLICIT JOINproduct

Upvotes: 0

Views: 226

Answers (2)

haki
haki

Reputation: 9779

The closest you can get to not writing the actual join condition is a natural join. 

select * from t1 natural join t2

Oracle will look for columns with identical names and join by them (this is not true in your case). See the documentation on the SELECT statement:

A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. If two columns with the same name do not have compatible data types, then an error is raised

This is very poor practice and I strongly recommend not using it on any environment

Upvotes: 3

Ron Deijkers
Ron Deijkers

Reputation: 3101

You shouldnt do that. Some db systems allow you to but what if you modify the fk's (i.e. add foreign keys)? You should always state what to join on to avoid problems. Most db systems won't even allow you to do an implicit join though (good!).

Upvotes: 2

Related Questions