select baseurl from tmp_page_tbl
where baseurl NOT IN ( select baseurl from page_lookup )
How do I write this query using joins instead of nesting it.
The idea is to get the baseurls from tmp tbl which do not exist in the page_lookup table
Add a comment
|
3 Answers
select baseurl
from tmp_page_tbl t
left outer join page_lookup p on t.baseurl = p.baseurl
where p.baseurl IS NULL
Comments
You could rewrite using joins like below:
SELECT baseurl from tmp_page_tbl as t
LEFT JOIN page_lookup as pl
ON t.baseurl=pl.baseurl
where pl.baseurl IS NULL
I'm not sure I would though unless you have a compelling reason. Below are a few links worth looking at:
Comments
If you aren't selecting most of the table and you've index on page_lookup.baseUrl, then not exists should be most efficient.
select baseurl from tmp_page_tbl tmp
where not exists ( select 1 from page_lookup WHERE baseurl = tmp.baseurl );
On some RDBMS (Oracle DB and Postgres) you can use MINUS (or EXCEPT in Postgres). That is in some cases very efficient.
1 Comment
Varun Sheth
I agree with you but some people have reservations using nested queries vs joins i m just trying to figure out the best solution incase the lookup table grows very large