MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (
  id INT
);

INSERT INTO table1 VALUES (111), (112), (113), (1111);

CREATE TABLE table2 (
  id VARCHAR(100)
);

INSERT INTO table2 VALUES ('111|aa'), ('112|ab'), ('114|ad'), ('1111|test');
-- be careful using the condition without '|'!
SELECT t1.id,t2.id 
FROM table1 t1 LEFT JOIN table2 t2 ON t2.id LIKE CONCAT(t1.id, '%')


id
id


111
1111|test

111
111|aa

112
112|ab

113
null

1111
1111|test


-- more secure condition to avoid wrong matches.
SELECT t1.id,t2.id 
FROM table1 t1 LEFT JOIN table2 t2 ON t2.id LIKE CONCAT(t1.id, '|%')


id
id


111
111|aa

112
112|ab

113
null

1111
1111|test