[Solved] SQL \"not like\" versus exclusionary subquery
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

SQL \"not like\" versus exclusionary subquery

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.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
The optimized query:
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
        )

Related Articles



* original question posted on StackOverflow here.