Veramente questa non è una JOIN, ma una select sulla prima tabella controllando i valori nei campi delle altre.

una join su 3 tabella sarebbe

SELECT * FROM ordini INNER JOIN ordini_prodotti ON ordini.ordine_id = ordini_prodotti.ordine_id INNER JOIN ordini_descr_agg ON ordini_descr_agg.prodotto_id = ordini_prodotti.prodotto_id WHERE ...

Due cose sulle JOIN:
Una join non è una ricerca tra più tabelle, ma un merge, una unione tra i risultati di più tabelle. Quanto ottieni i valori, non li ottieni come fossero il risultato di 3 tabelle separate, ma come una unica riga di una tabella.

Inoltre, io ho eseguito una INNER JOIN che è diversa da una LEFT o uno RIGHT JOIN:

INNER JOIN ritorna risultati solo se esiste almeno una riga tra le due tabelle che unisce. Nel tuo esempio tornerebbe solo le righe della tabella ordini che hanno almeno una riga in ordini_prodotti con ordine_id = al proprio che hanno almeno una riga in ordini_descr_agg con prodotto_id = al proprio.

LEFT JOIN ritorna risultati se esistono righe nella tabella di sinistra che rispettano il WHERE (per esempio facendo SELECT * FROM ordini LEFT JOIN ordini_prodotti ON ordini.ordine_id = ordini_prodotti.ordine_id

tornerebbe tutte le righe della tabella ordini, con campi null nella parte ordini_prodotti, in più tutte le righe di una INNER JOIN)

Infine RIGHT JOIN torna i risultati se esistono righe nella tabella di destra che rispettano il WHERE (per esempio facendo SELECT * FROM ordini RIGHT JOIN ordini_prodotti ON ordini.ordine_id = ordini_prodotti.ordine_id

tornerebbe tutte le righe della tabella ordini_prodotti, con campi null nella parte ordini, in più tutte le righe di una INNER JOIN)

Spero di essere stato chiaro, ciao a tutti