Difference between Full Join and Inner Join in SQl Server 2005

What is the main difference between Full Join and Inner Join in SQl Server 2005 ?
As inner join retrive all records that match certain condition, similiary in full join will rreturn all records only where they match.
Also sugest me which one is optimal in Inner Join and Where clause.This question is being moved to the SQL Server forum.

ADMIN|||Inner Join returns matching records only

A full outer join returns all rows in both the left and right tables

Hope it is clear enough.

Difference between 2 tables

Hi,

We have 2 tables as follows,

TableA
----
doc_id ver_id ref_date

TableB
----
doc_id ver_id ref_min_date

We need to find out the records that exists in TableA but not in TableB
and also to get the minimum of TableA.ref_date from the result.

For example,

TableA has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
100 2 12-AUG-2006
100 2 13-AUG-2006
100 2 14-AUG-2006
100 2 15-AUG-2006
200 1 17-AUG-2006
300 1 18-AUG-2006

TableB has the following rows
doc_id ver_id ref_date
100 1 10-AUG-2006
200 1 10-AUG-2006

Result should be as follows
doc_id ver_id ref_date
100 2 12-AUG-2006
300 1 18-AUG-2006

Please help us in writing the query

Thanks
AshokHi Ashok,

You may like to try this.

Select A.doc_id,A.ver_id ,Min(A.ref_date) From TableA A
Where Not Exists ( Select 1 From TableB B where A.doc_id=B.doc_id
And A.ver_id=B.ver_id
And A.ref_date=B.ref_date
) Group By A.doc_id,A.ver_id ;

I hope this helps.

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.comsql