JOIN issue

Question

Post reply

JOIN issue

panwar.jt

Default port

Points: 1418
More actions
March 12, 2010 at 2:21 am

#219859

How I can retrieve those rows from Tabble A that are not exists in table B using join.
OR
Join option of following subquery
Select A.* from A where A.id not in(Select B.id from B)
Thanks
KD

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Noel Ox Old Hand Points: 367 More actions · Answer 1

You can use a left join checking for Null

select a.*

from TableA a left join TableB b

on a.id = b.id

where b.id is null

-------------------------------------------------------
"With great power comes great responsibility"

Paul White SSC Guru Points: 150467 More actions · Answer 2

panwar.jt (3/12/2010)
Select A.* from A where A.id not in(Select B.id from B)

If you mean 'not exists', it can pay to use NOT EXISTS 😉

The LEFT JOIN version is not quite the same.

NOT IN displays some decidedly counter-intuitive behaviour if the sub-query returns a NULL.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 3

select a.* from A

where not exists (select 1 from B where A.ID=B.ID)

Susantha

panwar.jt Default port Points: 1418 More actions · Answer 4

Thanks 2 all

I could not understand this query because of less knowledge of relational algebra.

I got the ans after run below query

select A.*,B.* from TestTAble A right join TestTAble3 B on A.Line_No=B.Line_No

Then i eleminate all null value with below query

select A.*,B.* from TestTAble A right join TestTAble3 B on A.Line_No=B.Line_No

where A.Line_No is null

Regards

KD

Abhijit More SSCertifiable Points: 6897 More actions · Answer 5

[font="Courier New"]You can do it by two ways ..[/font]

[font="Courier New"]1. using "LEFT JOIN"[/font]

[font="Courier New"]SELECTT1.*, T2.*

FROMT1

LEFTJOIN T2 ON T1.keycol = T2.keycol

WHERET2.keycol IS NULL[/font]

[font="Courier New"]2. using "EXCEPT"[/font]

[font="Courier New"]SELECT keycol, col1, col2, col3, col4 FROM T1

EXCEPT

SELECT keycol, col1, col2, col3, col4 FROM T2[/font]

Abhijit - http://abhijitmore.wordpress.com

Paul White SSC Guru Points: 150467 More actions · Answer 6

Susantha Bathige (3/12/2010)
select a.* from A
where not exists (select * from B where A.ID=B.ID)

This method could be the highest performing of those submitted so far.

EXCEPT is close, but contains an implicit DISTINCT.

The LEFT and RIGHT JOINs are exactly equivalent of course.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

panwar.jt Default port Points: 1418 More actions · Answer 7

panwar.jt

Default port

Points: 1418

March 12, 2010 at 4:41 am

#1132575

Thanks u all

problem is solved