CODEWITHSUNDEEP
mysqljoin
Pancho Carpio
Pancho Carpio

Reputation: 45

JOIN ON IN does a full table scan

I have a query where I have to match serial numbers where sometimes the serial numbers are the same and sometimes one of the serial numbers has a preceding 's' so I have to account for both scenarios. This is a sample code of what I am asking about.

SELECT * 
FROM table1 t1
JOIN table2 t2 ON t1.serial_number IN(concat('s', t2.serial_number), t2.serial_number);

If I use one or the other case in the JOIN the query runs fine, but when I use both cases and do an explain it does a full table scan making it painfully slow. Any ideas? Help will be greatly appreciated.

Upvotes: 0

Views: 63

Answers (1)

Atlas
Atlas

Reputation: 143

You could run two INNER JOINS. Since INNER will only include values where both give you hits, you wouldn't have to worry about duplicates since if data is present in one, then it won't be in another.

Like so:

SELECT * FROM table1 t1
INNER JOIN table2 t2a on t1.serial_number = t2a.serial_number
INNER JOIN table2 t2b on t1.serial_number = concat('s',t2b.serial_number)

Upvotes: 2

Related Questions