Actually what is the main difference sub queries and joins.....we are using joins for more than 1 table we need to retrieve the data, where as sub queries also used same ... tel me the main difference between joins and sub queries?
They are very similar - what you can do with a join, you can with a subquery, and vice-versa. Here's an example:
SQL> create table t1 as select * from dba_Objects;
Table created.
SQL> create table t2 as select * from dba_Objects;
Table created.
SQL>
SQL> select owner, count(*)
2 from t1
3 where owner != 'PUBLIC'
4 and object_name
5 in ( select object_name
6 from t2
7 where t2.owner != t1.owner
8 and t2.owner != 'PUBLIC')
9 group by owner
10 order by 1;
OWNER COUNT(*)
------------------------------ ----------
APEX_040200 2880
APEX_050000 2878
APPQOSSYS 1
DVSYS 5
FLOWS_FILES 6
MCDONAC 4
OJVMSYS 8
ORDSYS 6
OUTLN 6
SCOTT 3
SI_INFORMTN_SCHEMA 5
SYS 53
SYSTEM 23
WMSYS 1
XDB 1
15 rows selected.
SQL>
SQL> select t1.owner, count(*)
2 from t1,t2
3 where t1.owner != 'PUBLIC'
4 and t1.object_name = t2.object_name
5 and t2.owner != t1.owner
6 and t2.owner != 'PUBLIC'
7 group by t1.owner
8 order by 1;
OWNER COUNT(*)
------------------------------ ----------
APEX_040200 3403
APEX_050000 3402
APPQOSSYS 2
DVSYS 5
FLOWS_FILES 20
MCDONAC 5
OJVMSYS 8
ORDSYS 6
OUTLN 6
SCOTT 3
SI_INFORMTN_SCHEMA 5
SYS 74
SYSTEM 25
WMSYS 1
XDB 1
15 rows selected.
SQL>
SQL> select t1.owner, count(distinct t1.rowid)
2 from t1,t2
3 where t1.owner != 'PUBLIC'
4 and t1.object_name = t2.object_name
5 and t2.owner != t1.owner
6 and t2.owner != 'PUBLIC'
7 group by t1.owner
8 order by 1;
OWNER COUNT(DISTINCTT1.ROWID)
------------------------------ -----------------------
APEX_040200 2880
APEX_050000 2878
APPQOSSYS 1
DVSYS 5
FLOWS_FILES 6
MCDONAC 4
OJVMSYS 8
ORDSYS 6
OUTLN 6
SCOTT 3
SI_INFORMTN_SCHEMA 5
SYS 53
SYSTEM 23
WMSYS 1
XDB 1
15 rows selected.
So you can see
query 1: for each row in t1, is there a row in t2 that matches the criteria - if so, bring back the row from t1
query 2: for the join of t1, t2, bring back the matches...hence because 1 row in t1 may match more than 1 row in t2, we get more results per owner
query 3: the same as (2), but restricting it to the distinct rows in t1 - as you can see, we end up back with the subquery result.
Hope this helps.