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 )