join problem

Question

Post reply

join problem

Alberto.Omini

SSC Eights!

Points: 957
More actions
April 3, 2008 at 4:18 pm

#76944

Hello people......
My problem is:
I have two tables joined with one code.......
my problem is how can i search end view only record in the table A
the have no reference in the Table B
thank you in advance.
Alberto.

Viewing 13 posts - 1 through 12 (of 12 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

So you want to select rows that only exist in TableA? This is an OUTER JOIN.

SELECT a.*

FROM TableA a

LEFT JOIN TableB b

ON a.code = b.code

WHERE b.code IS NULL

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]

or

Select * From TableA Where TableA.ID Not Exists (Select TableB.Id From TableB)

or

Select * From TableA Where TableA.ID Not In (Select TableB.Id From TableB)

I would suggest use EXISTS over IN as it gives better porformance.

Thanks,

Mahesh

[/font]

MH-09-AM-8694

peter.anderson SSC Rookie Points: 40 More actions · Answer 3

EXISTS basically returns True or False if it finds a record, it doesn't return a unique record so

EXISTS (Select TableB.Id From TableB)

will always return true if there is at least 1 entry in TableB, and will never return an ID to check against TableA

To use EXISTS in this case, you would have to use:

Select * From TableA WHERE NOT EXISTS

(Select TableB.Id From TableB WHERE TableB.ID = TableA.ID)

Which SQL SERVER interprets in the same way as using IN, therefore I would use IN, as it's easier to follow in your code:

Select * From TableA Where TableA.ID Not In (Select TableB.Id From TableB)

(This is also more effiecient than using the OUTER JOIN)

Sergiy SSC Guru Points: 110192 More actions · Answer 4

Which SQL SERVER interprets in the same way as using IN, therefore I would use IN, as it's easier to follow in your code:
Select * From TableA Where TableA.ID Not In (Select TableB.Id From TableB)
(This is also more effiecient than using the OUTER JOIN)

It's not right.

If you populate TableA and TableB with fair amount of data (million rows would be appropriate for not a big database) you'll see the difference.

If you look at execution plans you'll notice the difference as well.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Not enough coffee, Sergiy... which method are you recommending to use?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Sergiy SSC Guru Points: 110192 More actions · Answer 6

I once improved responce time of the query from 10 s to under 1 s just by replacing NOT IN with NOT EXISTS.

Don't remember exactly how many rows were in that subquery, several thousands or so.

Apparently, NOT EXISTS allows to use statistics more effectively, without pumping all "matching " data to memory.

_____________
Code for TallyGenerator

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Sergiy (4/6/2008)
I once improved responce time of the query from 10 s to under 1 s just by replacing NOT IN with NOT EXISTS.
Don't remember exactly how many rows were in that subquery, several thousands or so.
Apparently, NOT EXISTS allows to use statistics more effectively, without pumping all "matching " data to memory.

Well, NOT IN has had some bugs in SQL Server 2000, so that is certainly possible. They are mostly cleared up in Sql2005.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

rbarryyoung (4/7/2008)
Well, NOT IN has had some bugs in SQL Server 2000, so that is certainly possible. They are mostly cleared up in Sql2005.

What kind of bugs, Barry?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Sergiy (4/6/2008)
I once improved responce time of the query from 10 s to under 1 s just by replacing NOT IN with NOT EXISTS.
Don't remember exactly how many rows were in that subquery, several thousands or so.
Apparently, NOT EXISTS allows to use statistics more effectively, without pumping all "matching " data to memory.

Thanks for explanation, Sergiy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Alberto.Omini SSC Eights! Points: 957 More actions · Answer 10

thank you very much to all of you.

I solve my problem with NO EXIST phrase.

You help me very good !!!!!

thank you again.

Alberto.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeff Moden (4/7/2008)
rbarryyoung (4/7/2008)
Well, NOT IN has had some bugs in SQL Server 2000, so that is certainly possible. They are mostly cleared up in Sql2005.
What kind of bugs, Barry?

optimizer bugs: It would produce bad plans for WHERE clauses that had IN(..) and really bad plans for NOT IN(..).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Thanks... I've not experienced that in 2k... mostly because I won't let my guys use WHERE IN... Haven't seen a bad plan for WHERE NOT IN yet, either, but that's mostly because of the fear of adding indexes to some of the 3rd party junk we have to work with.

Seriously, thanks for the tip... I'll be a bit more wary about those two things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions