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:
Last modification:



This websites uses cookies and cookies of third parties for analytical purposes and advertisement. By using our website you agree to that. More information. You can change your choice any time.