Home » SQL & PL/SQL » SQL & PL/SQL » using in and = conditions
using in and = conditions [message #192947] Thu, 14 September 2006 06:15 Go to next message
Messages: 63
Registered: March 2005

I have table1 and table2

table1 columns


table2 columns


and other fields in both the tables.

table1 data
id fname lname
1 jon abc
2 jon bcd
3 jon efg

table2 data

fname lname address val
jon xyz aaaa 10
jon xyz aaaa 20
jon pqr aaaa 30

Now when I match both the tables using equi joins on fname field then I am getting 9 records.

select a.id,b.fname,b.address,b.val from table1 a,table2 b
where a.fname=b.fname;

when I try the other option as below

select fname,address,val from table2 where fname in
(select fname from table1)

I am getting 3 records only.

I want the difference of both the queries and also
I need my output
with id from first table and address,val from 2nd table.
which query should I use.

output should be

id address val
1 aaaa 10
2 aaaa 20
3 aaaa 30

thanks in advance

Re: using in and = conditions [message #192960 is a reply to message #192947] Thu, 14 September 2006 06:57 Go to previous message
Messages: 74
Registered: September 2006
infact in joining, the fname value of the first row is matched with the all rows of the table and they all matched, and then that is the same for othertwo fnames in table 1 so thats why you are getting 9 records. i.e. (3x3=9).

But in case of the IN and subquery you are only selecting data from table2, the data of the table 1 is just being used by the query to search in the table2. As there are only three rows matched in the table 2 so thats why you get three rows only.

To get the desired output you will have to change your desin a bit. Put some column in both tables who has a proper unique identification for each row in both tables.

Previous Topic: Select data that is not in other table(Merged)
Next Topic: HOW TO: Create clone user in oracle
Goto Forum:

Current Time: Fri Oct 28 09:50:49 CDT 2016

Total time taken to generate the page: 0.06963 seconds