how to avoid sql subquery

I wrote a query, but I used a subquery. I am not satified. But I don't know how to avoid a subquery like that. I want to exclude the names in the third table blacklist. Appreciated!!!

SELECT v1.name ... from view1 as v1
inner join table2 as t2 on v1.name = t2.name
where v1.name not in (select v1.name from view1 inner join blacklist bl on v1.name = bl.name)
jssong2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
SELECT v1.name ...
from view1 as v1
inner join table2 as t2 on v1.name = t2.name
left join blacklist bl on v1.name = bl.name
where bl.name is null
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jssong2000Author Commented:
it looks like not helpful. Thanks anyway
0
cyberkiwiCommented:
> it looks like not helpful. Thanks anyway

Have you actually tried it?
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

AkenathonCommented:
Cyberkiwi is right... when you want to "substract", either you do the subquery thing ("not in" as in your example or "not exists", both require a subquery), or else you do the antijoin exactly as he wrote. The only other option I can think of is using set operations and expressing it with MINUS:

SELECT v1.name ...
from view1 as v1
inner join table2 as t2 on v1.name = t2.name
MINUS
SELECT v1.name ...
from view1 as v1
inner join table2 as t2 on v1.name = t2.name
join blacklist bl on v1.name = bl.name

Open in new window


But that would be much more expensive than the subquery. I would try the NOT EXISTS variant:

SELECT v1.name ... from view1 as v1
inner join table2 as t2 on v1.name = t2.name
where not exists (select 0 from blacklist bl where v1.name = bl.name)

Open in new window

0
jssong2000Author Commented:
Yes I tried and returned a lot more rows. without the filter I got 341 rows, with the filter I should get 340 rows. But now I got more than 8000.
0
AkenathonCommented:
This can happen when bl.name is a NULLABLE field. Change in the antijoin "bl.name" to "bl.not_null_field" and it will work:

SELECT v1.name ...
from view1 as v1
inner join table2 as t2 on v1.name = t2.name
left join blacklist bl on v1.name = bl.name
where bl.SOME_NOTNULL_FIELD is null
0
AkenathonCommented:
Oops... please ignore my previous comment, I just messed up. The problem is that you have more than one row in your blacklist with the same name, so the result set gets multiplied. As you don't have a standard SQL syntax for a semijoin (other than the subqueries you want to avoid), you will have to do the "distinct" yourself if your DBMS supports inline views (you don't mention your DBMS brand or version, so I'll use Oracle syntax):

SELECT v1.name ...
from view1 as v1
inner join table2 as t2 on v1.name = t2.name
left join (select distinct name from blacklist) bl on v1.name = bl.name
where bl.name is null

Open in new window

0
jssong2000Author Commented:
Sorry. cyberkiwi. Actually it worked. I made a mistake on another clause.
But I am not sure why where bl.name is null  could remove that row from the result??? Thanks a lot.
0
SharathData EngineerCommented:
>> But I am not sure why where bl.name is null  could remove that row from the result???
If there are no records in blacklist for the same name of view1, the name field of blacklist would be null. If you can filter for those records, you will get the names which are not in blacklist but present in view1.
0
cyberkiwiCommented:
A split between http:#a35336032 for the answer, and http:#a35336744 for putting a good explanation to it.
0
SharathData EngineerCommented:
I agree as my post is an explanation of what cyberkiwi's query will do.
0
jssong2000Author Commented:
http:#a35336032 is the solution and  http:#a35336744 is a great expanation.
Thank you Guys!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.