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

difference between inner join and intersection

Data which I have

1001 2003 F

1001 2001 P

1002 2004 F

1003 2004 F

1004 2004 S

1004 2003 F

Which is considering all the cases

1004 -- 2003 1001

Result must be

1001 2003 2001 P

1002 2004 NULL F

1003 2004 NULL F

1004 2003 2004 S

But I am getting with this query

1001 2001 2003 P

1001 2003 2001 F

1002 2004 NULL F

1003 2004 NULL F

1004 2003 2004 F

1004 2004 2003 S

select a.acct_num, a.hh_id,b.hh_id, a.hh_acct_typ_cd from household_account a, household_account b where

(a.acct_num = b.acct_num and a.hh_id <> b.hh_id and a.hh_acct_typ_cd = 'P' )

union

select a.acct_num, a.hh_id

,(select b.hh_id from household_account b where b.acct_num = a.acct_num and a.hh_id<>b.hh_id ) AS STMT_HH_ID

, a.hh_acct_typ_cd from household_account a, household_account b

where (a.hh_id = b.hh_id and a.acct_num <> b.acct_num )

If the last case is not included then query is giving desired results

I split this from the bottom of another thread that was already answered. This seems like a straight T-SQL question, so I'm moving to the T-SQL forum.

Mike

|||

I am having a hard time following your queries. Can you build a table, insert the data that we can run your queries against?

Show us what you are getting, and what you really want

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.

Differ bet WHERE clause & INNER JOIN?

In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set.

In layman's terms, what is the difference? Any examples? Thanks in advance.

ddaveSo, I saw the question about Inner Join vs Where in Queries. I'm using all the "Joins" in clause where, is there problem to do it or I should be using Inner Join ?|||Linking tables should be done using a JOIN clause. Filtering on individual data elements should be done with WHERE clauses.

For simple queries, the SQL Server Optimizer will convert a statement like

select * from a, b where a.key = b.key

into

select * from a inner join b on a.key = b.key

...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.|||Originally posted by blindman
...and will make optimium use of the indexes on the two tables to create an efficient execution plan. For more complicated queries the optimizer may not be able to make this conversion.


Ya think? Not so sure...but youcan test it and check out the plan the optimizer chooses by looking at the query plan

The JOIN syntax leads to better organized and more easily readable and debuggable code, because it clearly establishes the relationships between tables.

100%|||Not so sure it will reevalutate the simple query, or not so sure it won't be able to evaluate a complex query?|||I'm not sure that it will give you 2 different plans...have you seen this?|||It won't for simple queries. I've heard that it might not be able to for complex queries. At some point of complexity there must be a statement that it is beyond its capacity to reduce to simple join clauses. I mean, complexity is theoretically infinite, right? I suspect that it may have difficulty reevaluating WHERE clauses containing conditional syntax, but I haven't formally tested it.sql

diferent way to a query

is theres a way to return the same result of this query without making the inner joins?

Code Snippet

create view missEeCuts
as
select distinct e.*
from events e
inner join (
select eventid, sum(px) as sum_px
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid
inner join (
select eventid, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l2
on e.idevent=l2.eventid
where
dbo.module(e.PxMiss,e.PyMiss)>=40 AND
dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

GO

Maybe this, Luis:

Code Snippet

selectdistinct e.*

from events e

where

dbo.module(e.PxMiss,e.PyMiss)>=40 AND

dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

ANDEXISTS(

select eventid,sum(px)as sum_px

from isolatedLeptons l

where e.idevent=l.eventid

groupby eventid

)

ANDEXISTS(

select eventid,sum(py)as sum_py

from isolatedLeptons l2

where e.idevent=l2.eventid

groupby eventid

)

Why do you not want the joins?

|||

Luis:

Why do you take "sum_px" and "sum_py" from different joins in:

Code Snippet

inner join (
select eventid, sum(px) as sum_px
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid
inner join (
select eventid, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l2
on e.idevent=l2.eventid

Can these two sums potentially be computed in a single join such as:

Code Snippet

inner join (
select eventid, sum(px) as sum_px, sum(py) as sum_py
from isolatedLeptons
group by eventid
) l
on e.idevent=l.eventid

If so, you might be able to eliminate the last INNER JOIN

|||

Dale:

Are "l.sum_px" "l2.sum_py" in scope in this line?

dbo.effectiveMass(e.PxMiss,e.PyMiss,l.sum_px,l2.sum_py)<= 150.0

I thought these would be out of scope

|||

Ah man. I completely missed that.

Still early...where'd that coffee pot go?

Thanks Kent