How to Join a Subquery in MySQL

MySQL supports the usage of a subquery as a table that can be used in a JOIN. The following query will work:

SELECT T1.*, SUB.*
FROM table1 as T1
LEFT JOIN (SELECT T2.ID, T2.name, concat(T2.name,'-subquery') as nameSubquery FROM table2 as T2) as SUB
ON T1.name = SUB.name;

Below you find the code to set up the tables to run the above queries:

CREATE TABLE `table1` (
`ID` INT AUTO_INCREMENT,
`name` varchar(30),
PRIMARY KEY (`ID`)
);
INSERT INTO `table1` SET `name`='car';
INSERT INTO `table1` SET `name`='bicycle';
INSERT INTO `table1` SET `name`='plane';

CREATE TABLE `table2` (
`ID` INT AUTO_INCREMENT,
`name` varchar(30),
PRIMARY KEY (`ID`)
);
INSERT INTO `table2` SET `name`='car';
INSERT INTO `table2` SET `name`='bicycle';
INSERT INTO `table2` SET `name`='bicycle';
INSERT INTO `table2` SET `name`='plane';
INSERT INTO `table2` SET `name`='plane';
INSERT INTO `table2` SET `name`='plane';

Published: August 30, 2019
Last modification: August 30, 2019