La migliore strategia in SQL

Le due query che hai fornito non sono equivalenti. Nel secondo, user_role.user_id è esplicitamente collegato a user.id , mentre nel primo non c'è alcun collegamento tra la tabella user e la tabella user_role . (Quale database stai usando per questo, BTW? Un join interno senza collegamento al contenuto della query esistente non mi sembra un SQL valido.)

Il tuo amico ha ragione, a proposito. Si suppone che i criteri JOIN siano per specificare il collegamento tra le tabelle. Influisce sul modo in cui una intera tabella si riferisce a un'altra intera tabella. Il filtro (selezionando quali righe all'interno di una tabella sono valide) dovrebbe andare nella WHERE clause.

EDIT: in risposta al commento:

I understand what you say, but I still think that the first strategy is more efficient because all the lines are not loaded. Probably you're right, but I would understand why.

Innanzitutto, se per "linee" intendi "righe" o "record nella tabella", è altamente improbabile che tutte le righe vengano caricate comunque come parte di JOIN . Questo sarebbe molto inefficiente, ma solo un motore di database davvero orribile lo farebbe.

Il motore SQL utilizza un processo noto come algebra relazionale per trasformare la query che scrivi nel metodo ottimale per recuperare il dati per i quali stai interrogando. Proprio come l'algebra che hai imparato a scuola, può comportare spostamenti di termini da un luogo all'altro secondo regole specifiche che assicurano che tutto rimanga equivalente nel risultato finale.

Come DFord ha menzionato nel suo commento, il DBMS probabilmente finirà per produrre lo stesso piano di query da entrambi gli stili. Se vuoi testarlo, prova a eseguire i piani di query di entrambe le query (il meccanismo per farlo varia da un DBMS a un altro, dovrai cercare come è fatto sul tuo sistema) e confrontarli.

Essendo vero, la vera ragione per scrivere i tuoi join nel secondo stile non è efficienza di esecuzione , ma efficienza di manutenzione . Se qualcuno ha bisogno di leggere la tua query in un secondo momento e ha familiarità con lo stile SQL standard, sarà molto più facile per loro capire se la tua query è scritta anche in stile SQL standard, il che significa usare JOIN criteri per specificare il collegamento e la clausola WHERE per specificare il filtro.

Sono entrambi sbagliati, questo è il modo corretto:

SELECT u.UserName,r.RoleName
FROM user u
Left JOIN  user_role ur ON ur.user_id = u.id
Left JOIN  role r ON r.id= ur.role_id
WHERE u.id = @user_id

Qualsiasi cosa diversa da SQL BLASPHEMY!

(Ho guardato il tuo SQL per 20 minuti e ancora non so cosa ti aspettavi che facesse o restituisse)

Modifica: aggiungendo alcuni dei commenti sottostanti in quanto vi sono alcune cose buone lì:

D) Perché stai sostenendo i join a sinistra qui come Only Only Way?

A) Perché a che serve il ruolo? Desiderate chiaramente la combinazione User Name con Role. La sinistra si unisce per assicurare che nessun utente venga ignorato (nel caso in cui un utente non abbia un ruolo o se ci sia un problema di integrità relazionale) Pensaci .. Con i join interni come fai a sapere se hai un cattivo ID utente o se l'utente non ha un ruolo? -

D) Sai che non ci sono "problemi di integrità relazionale" perché hai un vincolo FK sul posto e il database si occupa di questo per te. (Se non lo fai, allora questa è l'eresia SQL!) E poiché ciò che viene interrogato qui è l'insieme di tutti i nomi di ruolo per un utente specifico, se l'utente non ha alcun ruolo, allora un set vuoto è perfettamente valido risposta. (E uno più corretto di un set contenente un singolo elemento il cui valore è NULL, che è ciò che lo scenario produrrebbe.)

A) Spesso è necessario interrogare sistemi esterni che infrangono ogni regola del libro. Questo è un dato di fatto. Codice in modo difensivo e mantenere buone abitudini. - Ripeto che se l'utente non esiste questa query restituisce un valore che il corridore interpreterà erroneamente come "L'utente non ha un ruolo"