Searching multiple columns for a row match in MySQL

To search multiple columns for a row match in MySQL, you can use the UNION operator. UNION combines the result sets of two or more SELECT statements into a single result, automatically removing duplicate rows. This is useful when you want to search different columns for different criteria and merge the results together.

Creating the Demo Table

Let us first create a table and insert some records −

CREATE TABLE DemoTable645 (
   Id INT,
   FirstName VARCHAR(100)
);

INSERT INTO DemoTable645 VALUES (100, 'Chris');
INSERT INTO DemoTable645 VALUES (101, 'Robert');
INSERT INTO DemoTable645 VALUES (101, 'Bob');
INSERT INTO DemoTable645 VALUES (102, 'Carol');
INSERT INTO DemoTable645 VALUES (100, 'John');
INSERT INTO DemoTable645 VALUES (100, 'Robert');
INSERT INTO DemoTable645 VALUES (101, 'Mike');
INSERT INTO DemoTable645 VALUES (101, 'John');

Display all records from the table using the SELECT statement −

SELECT * FROM DemoTable645;

This will produce the following output −

+------+-----------+
| Id   | FirstName |
+------+-----------+
|  100 | Chris     |
|  101 | Robert    |
|  101 | Bob       |
|  102 | Carol     |
|  100 | John      |
|  100 | Robert    |
|  101 | Mike      |
|  101 | John      |
+------+-----------+
8 rows in set (0.00 sec)

Searching Multiple Columns Using UNION

The following query searches the Id column for values matching 100 and the FirstName column for values matching John, then combines the results using UNION −

SELECT Id AS Records FROM DemoTable645 WHERE Id LIKE '%100%'
UNION
SELECT FirstName FROM DemoTable645 WHERE FirstName LIKE '%John%'
ORDER BY 1 LIMIT 3;

This will produce the following output −

+---------+
| Records |
+---------+
| 100     |
| John    |
+---------+
2 rows in set (0.00 sec)

The first SELECT finds all rows where Id matches 100, and the second SELECT finds all rows where FirstName matches John. UNION merges both result sets and removes duplicates, so 100 and John each appear only once even though they exist in multiple rows.

Conclusion

The UNION operator in MySQL lets you search across multiple columns by combining separate SELECT queries into a single result set. Use UNION ALL instead if you want to keep duplicate rows in the output.