1

I currently have 2 tables:

Table Animal:
animal_id, animal_name owner

Table Owners
owner_id owner_name

One way to list all the animals and their respective owners it to use sql joins:

select animal_id, owner_name
from Animals, Owners
where (owner = owner_id);

I'd now like to know how to do the same with subqueries. I thought something like

select animal_id, owner_name
from Animals
where owner_name = (select owner_name from Owners where owner = owner_id)

but that doesn't seem to make the trick. Why?

Improve this question
1
  • 3
    Any reason why you want to do that? Commented Nov 24, 2010 at 4:33

4 Answers 4

Reset to default
2

If you want the owner_name field included in the results, and that field is in the table Owners, then you must JOIN Owners into the query somehow. The only thing you can return as part of the result set are columns from the included tables, constants, or derived values (using Oracle language functions to operate on columns and/or constant values).

Edit:

Actually, now that I think about it, you could use (or, rather, try — I'm not an Oracle user) this extremely-poorly-performing syntax:

 SELECT animal_id, (SELECT owner_name FROM Owners WHERE owner = Animals.owner_id)
   FROM Animals

That is, technically, a sub-query solution.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks! So, from your comments I can see that it is preferable to do joins instead of subqueries?
For example, if I have 10 animals and 10 owners wouldn't it be best to have to do the 10 subqueries than having to create a table of 100 elements? Thanks
The subselect (in this example, correlated) is logically equivalent to a LEFT JOIN to the OWNERS table, because if there's no owner for the animal -- the owner value would be NULL. Correlation is generally what erodes performance, while the optimizer has been known to see non-correlated subselects as having equivalent plans to LEFT JOINs.
Yes, for this type of problem a JOIN is the preferable and correct way of attaining your result. If you have ten animals each with one owner, the result set will have only ten rows. Do not try and imagine what the database engine is doing to produce the result; it is much, much smarter than you give it credit for. Later, when you're more familiar with the various types of JOINs and relational calculus, it's interesting to delve into the mechanics of execution planning, but for the time being, trust your database.
+1 most of the time, doing an ordinary join is the best way. However, in some cases using a subquery in the SELECT clause can be useful.
1

It doesn't work because you have a column 'owner_name' that doesn't exist in a table in your from clause. To include a sub query result in the select, include the sub query in your select clause:

select animal_id, (select owner_name from Owners where owner = owner_id)
from Animals
Improve this answer

2 Comments

Thanks! Isn't there any other way of not having to put the subquery in that place? I mean, I'd prefer (if possible!) to have something of the form "SELECT a, b, d, d FROM ...." instead of having to put the subqueries directly on the "header".
I think you do something like: select animal_id, owner_name from Animals, (select owner_name from Owners where owner = owner_id). The general term is a derived table. But in real use you'd almost always use an inner or outer join.
0

What you use is like an implicit Cartesian join.

select animal_id, owner_name
from Animals, Owners
where (owner = owner_id);

An alternative is inner joins

select animal_id, owner_name
from Animals A inner join Owners O
     on A.owner = O.owner_id;

It seems like you don't like a subquery in the SELECT clause. I don't either. There similar things that can be done wityhout using the actual word 'JOIN' but this is usually considered best.

Improve this answer

2 Comments

Those are both equivalent INNER JOINs, with a modest difference in syntax.
multiple tables comma seperated in FROM makes an inner join? i was under the impression it was cartesian, and the WHERE just filters stuff after the fact.
0

I think you may have a design flaw: owner would appear not to be an attribute of an animal.

Rather, ownership may be a relationship between animals and people (or whatever entity an owner is). Therefore, you should consider having three tables e.g. Animals (entity table), People (or whatever entity table) and Ownership (a relationship table).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.