Mysql variable format for a "NOT IN" list of values

Tackling NOT IN the Dynamic Way

Sometimes static just doesn't cut it. Let's go dynamic!

Expressional Banking With Dynamic SQL

If your value list isn't static or comes from a selective condition, say hello to dynamic SQL:

SET @exclude = (SELECT GROUP_CONCAT(id) FROM table WHERE condition); -- A dynamic twist!
SET @query = CONCAT('SELECT * FROM another_table WHERE column NOT IN (', @exclude, ')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Directing Selective Traffic with FIND_IN_SET()

When you can't unscrew your SQL syntax and are dealing with commas, the FIND_IN_SET() function feels like divine intervention:

SET @exclude = '1,3,5'; -- Odds only, please
SELECT * FROM table WHERE FIND_IN_SET(column, @exclude) = 0;


This acts as a filter, effectively snubbing rows where column matches your comma-separated values.

Array Simulation with Subqueries

As a grand finale, here's a clever way to simulate arrays using a subquery clutching a UNION ALL. Take a look:

SELECT * FROM table WHERE column NOT IN (
  SELECT value FROM (SELECT 1 AS value UNION ALL SELECT 2 UNION ALL SELECT 3) AS subquery
);


Feels like magic, right?

Taking on Complex List Formats

Tackling complex formats like a seasoned SQL superhero.

Rendezvousing with CONCAT and Separators

Got a pipe (|) instead of a comma? Let's call CONCAT() and REPLACE() to the rescue!

SET @exclude = '1|2|3'; -- Exclusions going plumbing style
SET @formatted_exclude = REPLACE(@exclude, '|', '\',\'');
SET @query = CONCAT('SELECT * FROM table WHERE column NOT IN (\'', @formatted_exclude, '\')');


Guarding Your Codes with NOT LIKE

A pipe-separated list? No worries, just use NOT LIKE and keep your exclusions at bay!

SET @exclude = '%|1|%|2|%|3|%'; -- Exclusions hiding in % |
SELECT * FROM table WHERE column NOT LIKE @exclude;


This approach works when % wildcards are around each pipe-separated value within the variable.

Best Practices for Super Clean Performance

Ensuring MySQL compatibility is a lifesaver. You don’t want compatibility issues spoiling your coding journey.

Trustworthy solutions: Multiple upvotes, similar to likes for coding skills, are often good guides. Accepted answer: Now that’s an Oscar in the coding universe!

Providing sample queries: Think of this aspect as a hands-on training session right in the middle of your coding adventure!