How Can a Temporary Table Improve Efficiency When Using Multiple LIKE Conditions in SQL?

Use temporary tables to handle multiple conditions in the LIKE operator

In SQL, the LIKE operator is used to perform pattern matching on string values. Although it is possible to combine multiple LIKE conditions using the OR operator, a better solution is to use a temporary table.

Consider the following statement:

<code class="language-sql">select * from tbl where col like ('ABC%','XYZ%','PQR%');</code>

This statement is designed to retrieve all rows in the tbl table where the col column matches any pattern ABC%, XYZ% or PQR%. While using OR is an efficient approach, for a large number of conditions it can become tedious and inefficient.

Instead, we can create a temporary table called patterns to store all the patterns we want to match:

<code class="language-sql">CREATE TEMPORARY TABLE patterns (
  pattern VARCHAR(20)
);

INSERT INTO patterns VALUES ('ABC%'), ('XYZ%'), ('PQR%');</code>

After creating the temporary table, we can join it with the tbl table using the LIKE operator:

<code class="language-sql">SELECT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern);</code>

In this query, the pattern column in the patterns table is used to match the col column in the tbl table. The result is a list of rows in tbl that match any pattern in the patterns table.

It is important to note that if a row in the tbl matches multiple patterns, it will appear multiple times in the results. If this is not desired, you can use the DISTINCT query modifier:

<code class="language-sql">SELECT DISTINCT t.* FROM tbl t JOIN patterns p ON (t.col LIKE p.pattern);</code>

This will ensure that each row in the tbl only appears once in the results, even if it matches multiple patterns.

The above is the detailed content of How Can a Temporary Table Improve Efficiency When Using Multiple LIKE Conditions in SQL?. For more information, please follow other related articles on the PHP Chinese website!