I simply would like to know if there are benefits going one way or another for the following scenario:
I have a SQL query with many "WHERE" items, like so:
Select
*
from
a_table
WHERE
(W like X) AND
(Y like Z) AND
(A not like B) AND
(C not like D)
The thing is, I'm wondering for the "Not-like" portion if it would be faster/more efficient as an 'exclusionary subquery', by which I mean I do a select FOR those things instead of cutting those out, and then at the bottom of my query throw in something to the tune of:
AND someId NOT IN
(select distinct someId
FROM a_table
WHERE
A like B AND
C like D)
Another note, this is of course an example query. The real one has 5+ "where's" and 5+ "where not's". in case that makes any difference.
Is there a huge difference in efficiency/time/processing between these two methods? I was always taught that the "like" function was bad and I should feel bad for using it, and by nature I would like to avoid it if I can.
Thank you for your time.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT
*
FROM
a_table
WHERE
(
a_table.W LIKE a_table.X
)
AND (
Y LIKE a_table.Z
)
AND (
a_table.A NOT LIKE a_table.B
)
AND (
a_table.C NOT LIKE D
)