Czy możliwe jest użycie JOIN z zapytaniami podrzędnymi w SQL?

Tak, jest to możliwe. Zagnieżdżone zapytanie można użyć jako źródła danych (tabeli) do JOIN.

Przykład:

SELECT t1.kolumna1, sub.kolumna2
FROM tabela1 AS t1
JOIN (
    SELECT id, kolumna2
    FROM tabela2
    WHERE warunek = 'jakas_wartość'
) AS sub ON t1.id = sub.id;

Tutaj zagnieżdżone zapytanie SELECT id, kolumna2 FROM tabela2 WHERE warunek = 'jakas_wartość' zwraca wynik, który następnie jest używany jako tymczasowa tabela z aliasem sub do wykonania JOIN z tabela1.