MySQL 8.0

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

create table users (id integer, firstname varchar(100), lastname varchar(100),src varchar(100));

    insert into users (id, firstname, lastname,src) values
    (1, 'albert', 'einstein','L'),
    (2, 'isaac', 'newton','S'),
    (3, 'marie', 'curie','L'),
    (4, 'isaac', 'newton','L');

Records: 4  Duplicates: 0  Warnings: 0


SELECT * FROM users WHERE id in (SELECT id  from users where src ='L')


id
firstname
lastname
src


1
albert
einstein
L

3
marie
curie
L

4
isaac
newton
L


SELECT * FROM users u WHERE 
exists (SELECT 1  from users usr where src ='L' and u.id=usr.id)


id
firstname
lastname
src


1
albert
einstein
L

3
marie
curie
L

4
isaac
newton
L


SELECT firstname  from users where src ='L'


firstname


albert

marie

isaac


SELECT * FROM users WHERE FirstName='isaac'


id
firstname
lastname
src


2
isaac
newton
S

4
isaac
newton
L