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?
 
cyberkiwiConnect With a Mentor Commented:
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
 
jssong2000Author Commented:
it looks like not helpful. Thanks anyway
0
 
cyberkiwiCommented:
> it looks like not helpful. Thanks anyway

Have you actually tried it?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
SharathConnect With a Mentor Data 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.