MySQL 8.0

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

CREATE TABLE T1
    (`c1` int, `c2` varchar(1))
;
    
INSERT INTO T1
    (`c1`, `c2`)
VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c'),
    (4, 'd'),
    (5, 'e'),
    (6, 'f')
;


CREATE TABLE T2
    (`c1` int, `c3` varchar(2))
;
    
INSERT INTO T2
    (`c1`, `c3`)
VALUES
    (1, 'l1'),
    (2, 'l2'),
    (3, 'l3'),
    (4, 'l2'),
    (6, 'l1')
;
SELECT T1.c1, T1.c2, T2.c1, T2.c3
FROM T1
LEFT JOIN T2
ON T1.c1 = T2.c1 AND T2.c3 = 'l1'


c1
c2
c1
c3


1
a
1
l1

2
b
null
null

3
c
null
null

4
d
null
null

5
e
null
null

6
f
6
l1


SELECT T1.c1, T1.c2, T2.c1, T2.c3
FROM T1
LEFT JOIN T2
ON T1.c1 = T2.c1 
WHERE (T2.c3 = 'l1' OR T2.c3 IS NULL)


c1
c2
c1
c3


1
a
1
l1

5
e
null
null

6
f
6
l1


SELECT T1.c1, T1.c2, T2.c1, T2.c3
FROM T1
INNER JOIN T2
ON T1.c1 = T2.c1 AND T2.c3 = 'l1'


c1
c2
c1
c3


1
a
1
l1

6
f
6
l1

c1	c2	c1	c3
1	a	1	l1
2	b	null	null
3	c	null	null
4	d	null	null
5	e	null	null
6	f	6	l1