Podzapytanie po selekcie-co robi, kiedy stosować?

Witam,
Szukalem w internecie, ale nigdzie nie znalazłem satysfakcjonującej mnie odpowiedzi.
Mógłby ktoś wytłumaczyć, do czego służą podzapytania po SELECT?
Kiedy je stosować?

Jeżeli dobrze rozumiem to chodzi ci o podzapytania w liście pól po select. One wyciągają dane i pokazują w kolumnie, muszą zwrócić dokładnie jeden wiersz i kolumnę (przynajmniej w MsSQL). Często stosowane z filtrem na podstawie danych z tabeli zewnętrznej.
Kiedy używać? Nie znam przypadku sensownego, lepszy jest join...

Daj jakiś konkretny przykład bo nie bardzo wiadomo o co Ci chodzi. Czy o zapytania w klauzuli FROM czy może WHERE czy w JOIN, a może mówisz o CTE ... a może jeszcze coś innego. Daj coś konkretnego albo sprecyzuj pytanie.

ok oto przykład:
Polecenie jest takie:
"Podać informacje o tych departamentach, w których pracują osoby reprezentujące wszystkie możliwe stanowiska i osoba, która została zatrudniona w firmie jako druga. Podać numer departamentu, liczbę osób, pracujących na stanowiskach innych niż Informatyk."

Zapytanie SQL w Oraclu:

SELECT d.nazwa, d.nr_departamentu,(SELECT count(*) FROM pracownik WHERE stanowisko !='INFORMATYK' AND nr_departamentu=d.nr_departamentu)liczba
FROM departament d, pracownik p
WHERE p.nr_departamentu=d.nr_departamentu and
d.nr_departamentu=any(SELECT nr_departamentu FROM pracownik WHERE data_zatrudnienia=(SELECT min(data_zatrudnienia) FROM pracownik where data_zatrudnienia>
(SELECT min(data_zatrudnienia) FROM pracownik)))
GROUP BY d.nazwa, d.nr_departamentu
HAVING  count(distinct p.stanowisko)=(SELECT count(Distinct stanowisko) FROM pracownik);

Ktoś mógłby wyjaśnić?

oto schemat bazy danych:

To jest podzapytania, które zwraca liczbę pracowników w departamencie które nie pracują na stanowisku informatyk.

Zwróć uwagę na warunek where:

WHERE stanowisko !='INFORMATYK' AND nr_departamentu=d.nr_departamentu

Bierze pracowników którzy nie są na stanowisku informatyk i kluczowe: dla departamentu z zapytania zewnętrznego, Alias d tabeli. To samo osiągniesz funkcja agregujące sum:

Sum(case when stanowisko='informatyk' then 0 else 1 end)

Zadam może jeszcze tu jedno pytanie, bo chyba nie ma co zaśmiecać forum i tworzyć nowego tematu.

Dlaczego to zapytanie nie działa(błąd się nie pokazuje, ale nie zwraca żadnego rekordu, a wiem że są rekordy które spełniają zapytanie)?
Czy ma to coś wspólnego z NULL'em? Wiem że są wartości nullowe w kolumnie "id_kierownika"

Wypisać pracowników, którzy nie są kierownikami.

SELECT id_pracownika FROM pracownik
WHERE id_pracownika NOT IN (SELECT id_kierownika FROM pracownik)

Baza identyczna jak w pierwszym poście.

To ma związek z null.

Select p.* From pracownik p
Left join pracownik k on k.id_kierownika=p.id_pracownika
Where k.id_pracownika is null

Dzieki wielkie.
Mam kolejne pytanie dotyczące podzapytania po SELECIE

. Dla każdego departamentu podać stanowiska i liczbę pracowników na danym stanowisku.

Moja odpowiedź:

SELECT DISTINCT d.nazwa, p.stanowisko,
(SELECT COUNT(pp.id_pracownika) FROM pracownik pp
WHERE pp.nr_departamentu=d.nr_departamentu AND pp.stanowisko=p.stanowisko) AS Liczba
FROM departament d, pracownik p
WHERE d.nr_departamentu=p.nr_departamentu
ORDER BY 1

Czy tu dobrze zrobiłem?

Dobrze tylko niepotrzebnie podzapytaniem. Całe podzapytaniem zamień na count(*)

Mam jeszcze na dziś ostatnie podzapytanie, które sprawiło mi trudność.
Baza ćwiczeniowa nie ma niestety dostatecznie dużo danych aby to zapytanie coś zwracało i nie mogę sprawdzić poprawności

Podać nazwy tych projektów, przy realizacji których pracują tylko kierownicy.

SELECT DISTINCT z.nr_projektu FROM zlecenie z
WHERE z.nr_projektu NOT IN
(SELECT DISTINCT nr_projektu FROM zlecenie zz, pracownik pp WHERE
pp.id_pracownika=zz.id_pracownika AND pp.id_pracownika NOT IN(SELECT id_kierownika from pracownik WHERE
id_kierownika is not null))

Formatuj te zapytania, bo ciężko analizować...
Ja jestem zwolennikiem pisania joinów, bo nie znając struktury muszę szukać jak są powiązane tabele w zapytaniu, tak jest czytelniej:

SELECT DISTINCT 
	z.nr_projektu 
FROM 
	zlecenie z
WHERE 
	z.nr_projektu NOT IN (SELECT DISTINCT 
							nr_projektu 
						FROM 
							zlecenie zz
							inner join pracownik pp on pp.id_pracownika=zz.id_pracownika 
						WHERE pp.id_pracownika NOT IN(SELECT id_kierownika FROM pracownik WHERE id_kierownika IS NOT NULL)
						)

Samo podzapytanie możesz uprościć dla czytelności:

SELECT DISTINCT 
	z.nr_projektu 
FROM 
	zlecenie z
WHERE 
	z.nr_projektu NOT IN (SELECT DISTINCT 
							nr_projektu 
						FROM 
							zlecenie zz
						WHERE 
                                                        zz.id_pracownika NOT IN(SELECT id_kierownika FROM pracownik WHERE id_kierownika IS NOT NULL)
						)

Wydaje się to poprawne, ale przez skrzywienie zawodowe słabo wyglądają te negacje (silnik nie optymalizuje negacji) dużo prościej zrobić to funkcją agregującą i warunkiem having:

SELECT
	nr_projektu 
FROM 
	zlecenie z
	left join pracownik p on p.id_kierownika=z.id_pracownika 
group by
    nr_projektu
HAVING
    SUM(case when p.id_kierownika is null then 1 else 0 end) = 0

W having jeżeli mam pracownika to jest 1 jeżeli kierownika to 0, suma = 0 daje projekty z samymi kierownikami.