MySQL JOIN Using a Subquery in ON Condition

MySQL supports the usage of a subquery in the ON condition of a JOIN. The following query will work:

SELECT T1.*, T2.*
FROM table1 as T1
LEFT JOIN table2 as T2
ON T2.ID = (SELECT T22.ID FROM table2 as T22 WHERE T22.name='car');

You are also permitted to reference table1 in the subquery. For example, if you wanted to join to each entry in table1 the first entry in table2 that has the same name as the entry in table1:

SELECT T1.*, T2.*
FROM table1 as T1
LEFT JOIN table2 as T2
ON T2.ID = (SELECT T22.ID FROM table2 as T22 WHERE T22.name=T1.name LIMIT 1);

If you wanted to join the second entry, then simply replace LIMIT 1 with LIMIT 1,1 in the above query.

If your subquery returns more than one row you will get the error "ERROR 1242 (21000): Subquery returns more than 1 row". Fortunately, the IN keyword also works with subqueries:

SELECT T1.*, T2.*
FROM table1 as T1
LEFT JOIN table2 as T2
ON T2.ID IN (SELECT T22.ID FROM table2 as T22 WHERE T22.name=T1.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 27, 2019
Last modification: August 27, 2019

Leave a Comment