Search code examples
mysqlsql-like

MYSQL with Where and Like And not in

I compare two tables and output the values there was not in the rows.

    SELECT one, two FROM `table1` WHERE `two` NOT IN 
(SELECT `two2` FROM `table2`) AND `one` NOT IN (SELECT `one1` FROM `table2`)

How can i add a like... like row '%two' not in '%two2'?

i like to check: 

SELECT one, two FROM table1 WHERE LIKE %two NOT IN (SELECT LIKE %two2 FROM table2)

because in this tables are from time to time a 0 at first place but this i must fade out

I have no variables only the row names. Is any way i can do that?

Solution

  • You may invert your condition and use tricky LEFT JOIN instead. Then, your query will look like this:

    SELECT one, two FROM `table1`
LEFT JOIN `table2` ON
    `table1`.`one` = `table2`.`one1`
    OR `table1`.`two` = `table2`.`two2`
    -- List others field comparasion using OR here --
WHERE `table2`.`one1` IS NULL;

    That approach will help you reduce the number of subqueries and improve performance and readability greatly.