difference between inner join and intersection

Hi Can anybody explain me what is the difference between inner join and intersection?

I prepare a query but it shows the same results then why we need two functions like this to perform same operation

Thanx-Nagu

Union

Include all rows from two similarly defined tables into one table (append operation)

Antiques1 U Antiques2

Intersection

List rows where the exact same row appears in two simlarly defined tables (actual symbol is upside-down "U")

Antiques1 intersection Antiques2

nagu

check out

http://www.dbbm.fiocruz.br/class/Lecture/d17/sql/jhoffman/sqltut.html

|||

hi,

we are here dealing in both cases with semi joins, where the rows from one (or both sides of the join operation, depending on the left/right/inner join proprosition) table are returned base on the evidence of existing related rows in the correlated table.. semi joins can be performed both via standard INNER JOIN, and via EXISTS clauses..

the inner join proposition requires for this the distinct clause as well like

SELECT DISTINCT s.Id, s.Name

FROM dbo.Students s

[INNER] JOIN dbo.Courses c ON c.StudentId = s.Id -- INNER is the default join condition

WHERE Level = @.someLevel;

which can be even expressed in

SELECT s.Id, s.Name

FROM dbo.Students s

WHERE Level = @.someLevel

AND EXISTS(

SELECT *

FROM dbo.Courses c

WHERE c.StudentId = s.Id

);

but, in this case, objviously, NULL are treated as different form each others, while set operations are supposed to consider them as equal, and, of course, you get the the result of only one of the involved object and apply DISTINCT to force non repeatable rereferences to the very same row to be returned ..

in join conditions, the correlated objects (tables/views) do not need to have the same number of columns in the results as only the evidence of existance is needed and actually no other reference to the related object is returned as output..

in INTERSECT the 2 input objects must have the same number of columns returned and base data type.. more, set operations are performed against complete rows from the 2 input objects, and here NULLs are considered to be equals..

INTERSECT returns rows present in both objects so that,

SELECT s.Id, s.Name, s.Birthday

FROM dbo.Students s

INTERCEPT

SELECT t.Id, t.Name, t.Birthday

FROM dbo.Teachers t;

returns all students that are teachers too (or the contrary if you like it better ), and this solution implyes the DISTINCT definition as well as distinct is the default (and only) implementation for INTERSECT and EXCEPT set operations in SQL Server 2005..

regards

|||

Oh thank you

Nagu