April 3, 2008 at 4:18 pm
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.
April 3, 2008 at 4:35 pm
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
April 4, 2008 at 1:07 am
[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
April 4, 2008 at 5:55 am
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)
April 6, 2008 at 4:51 pm
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
April 6, 2008 at 10:45 pm
Not enough coffee, Sergiy... which method are you recommending to use?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2008 at 11:41 pm
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
April 7, 2008 at 12:04 am
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]
April 7, 2008 at 6:51 am
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
Change is inevitable... Change for the better is not.
April 7, 2008 at 6:52 am
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
Change is inevitable... Change for the better is not.
April 8, 2008 at 3:55 am
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.
April 8, 2008 at 11:55 pm
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]
April 9, 2008 at 1:19 am
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
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply