Perché i RDBMS non restituiscono le tabelle unite in un formato nidificato?

In fondo, nelle budella di un database relazionale, tutte le sue righe e colonne. Questa è la struttura con cui è ottimizzato un database relazionale con cui lavorare. I cursori funzionano su singole righe alla volta. Alcune operazioni creano tabelle temporanee (anche in questo caso devono essere righe e colonne).

Lavorando solo con le righe e restituendo solo le righe, il sistema è in grado di gestire meglio la memoria e il traffico di rete.

Come già detto, questo consente di eseguire determinate ottimizzazioni (indici, join, unioni, ecc.)

Se uno desiderava una struttura ad albero nidificata, ciò richiede che uno estrae tutti i dati contemporaneamente. Sono finite le ottimizzazioni per i cursori sul lato del database. Allo stesso modo, il traffico sulla rete diventa un grande scoppio che può richiedere molto più tempo del lento flusso di righe per riga (questo è qualcosa che occasionalmente si perde nel mondo di oggi).

Ogni lingua ha matrici all'interno di essa. Queste sono cose facili con cui lavorare e interfacciarsi. Usando una struttura molto primitiva, il driver tra il database e il programma, indipendentemente dalla lingua, può funzionare in modo comune. Una volta che si inizia ad aggiungere alberi, le strutture del linguaggio diventano più complesse e più difficili da percorrere.

Non è così difficile per un linguaggio di programmazione convertire le righe restituite in qualche altra struttura. Trasformalo in un albero o in un set di hash o lascialo come un elenco di righe su cui puoi scorrere.

C'è anche una storia al lavoro qui. Il trasferimento di dati strutturati era qualcosa di brutto ai tempi antichi. Guarda il formato EDI per avere un'idea di cosa potresti chiedere. Gli alberi implicano anche la ricorsione - che alcuni linguaggi non hanno supportato (le due lingue più importanti dei vecchi tempi non supportavano la ricorsione - ricorsione non entrare in Fortran fino alla F90 e nemmeno nell'era COBOL).

E mentre i linguaggi di oggi supportano la ricorsione e tipi di dati più avanzati, non c'è davvero una buona ragione per cambiare le cose. Funzionano e funzionano bene. Quelli che sono che cambiano sono i database nosql. È possibile memorizzare alberi nei documenti in un documento basato su uno. LDAP (in realtà è vecchio) è anche un sistema basato sull'albero (anche se probabilmente non è quello che cerchi). Chissà, forse la prossima cosa nei database nosql sarà quella che restituisce la query come un oggetto json.

Tuttavia, i "vecchi" database relazionali ... stanno lavorando con le righe perché questo è ciò che sono bravi e tutto può comunicare senza problemi o traduzioni.

12. In protocol design, perfection has been reached not when there is nothing left to add, but when there is nothing left to take away.

Da RFC 1925 - The Twelve Networking Truths

Restituisce esattamente ciò che hai richiesto: un singolo set di record contenente il prodotto cartesiano definito dai join. Esistono molti scenari validi in cui ciò è esattamente ciò che si vorrebbe, così dicendo che SQL sta dando un cattivo risultato (e quindi sottintende che sarebbe meglio se lo si modificasse) potrebbe effettivamente rovinare un sacco di domande.

Quello che stai vivendo è noto come " Mancata corrispondenza dell'Impostazione Relazionale / Oggetto, " le difficoltà tecniche che sorgono dal fatto che il modello di dati orientato agli oggetti e il modello di dati relazionali sono fondamentalmente diversi in diversi modi. LINQ e altri framework (noti come ORM, Object / Relational Mappers, non a caso,) non magicamente "aggirano questo"; semplicemente emettono query diverse. Può essere fatto anche in SQL. Ecco come lo farei io:

SELECT * FROM users user where [criteria here]

Fai scorrere l'elenco degli utenti e crea un elenco di ID.

SELECT * from EMAILS where user_id in (list of IDs here)
SELECT * from PHONES where user_id in (list of IDs here)

E poi fai il tuo ingresso sul lato client. Questo è il modo in cui LINQ e altri framework lo fanno. Non c'è vera magia coinvolta; solo uno strato di astrazione.

Potresti usare una funzione integrata per concatenare i record insieme. In MySQL puoi usare la funzione GROUP_CONCAT() e in Oracle puoi usare la funzione LISTAGG() .

Ecco un esempio di come potrebbe apparire una query in MySQL:

SELECT user.*, 
    (SELECT GROUP_CONCAT(DISTINCT emailAddy) FROM emails email WHERE email.user_id = user.id
    ) AS EmailAddresses,
    (SELECT GROUP_CONCAT(DISTINCT phoneNumber) FROM phones phone WHERE phone.user_id = user.id
    ) AS PhoneNumbers
FROM users user 

Ciò restituirebbe qualcosa come

username    department       EmailAddresses                        PhoneNumbers
Tim_Burton  Human Resources  [email protected], [email protected], [email protected]   231-123-1234, 231-123-1235

The problem with this is that it's returning the user's name, DOB, favorite color, and all the other information stored

Il problema è che non sei abbastanza selettivo. Hai chiesto tutto quando hai detto

Select * from...

... e ce l'hai (compresi DOB e colori preferiti).

Probabilmente dovresti essere un po 'più (ahem) ... selettivo, e dire qualcosa del tipo:

select users.name, emails.email_address, phones.home_phone, phones.bus_phone
from...

È anche possibile che tu possa visualizzare record che sembrano duplicati perché un user potrebbe partecipare a più email record, ma il campo che distingue questi due non è nel tuo Select statement, quindi potresti dire qualcosa di simile

select distinct users.name, emails.email_address, phones.home_phone, phones.bus_phone
from...

...over-and-over again for each record...

Inoltre, noto che stai facendo un LEFT JOIN . Questo unirà tutti i record a sinistra del join (cioè users ) a tutti i record a destra o in altre parole:

A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.

( link )

Quindi un'altra domanda è: hai bisogno di un join di sinistra, oppure un INNER JOIN è stato sufficiente? Sono tipi di join molto diversi.

Wouldn't be nicer if it returned a single row for each user, and within that record there was a list of emails

Se in effetti desideri che una singola colonna all'interno del set di risultati contenga un elenco generato al volo, è possibile farlo ma varia a seconda del database che stai utilizzando. Oracle ha la funzione listagg .

In definitiva, penso che il tuo problema potrebbe essere risolto se riscrivi la tua query vicino a qualcosa del genere:

select distinct users.name, users.id, emails.email_address, phones.phone_number
from users
  inner join emails on users.user_id = emails.user_id
  inner join phones on users.user_id = phones.user_id

Le query producono sempre un insieme di dati rettangolare (non frastagliato). Non ci sono sottoinsiemi nidificati all'interno di un set. Nel mondo degli insiemi tutto è un puro rettangolo non annidato.

Puoi pensare a un join come mettere 2 set affiancati. La condizione "on" è il modo in cui i record di ciascun set sono abbinati. Se un utente ha 3 numeri di telefono, vedrai una duplicazione di 3 volte nelle informazioni dell'utente. Un quesito rettangolare non frastagliato deve essere prodotto dalla query. È semplicemente la natura di unire insiemi con una relazione 1-a-molti.

Per ottenere quello che vuoi, devi usare una query separata come descritto da Mason Wheeler.

select * from Phones where user_id=344;

Il risultato di questa query è ancora un rettangolo con un set non frastagliato. Come è tutto nel mondo degli insiemi.

Devi decidere dove esistono i colli di bottiglia. La larghezza di banda tra il database e l'applicazione è in genere piuttosto veloce. Non c'è alcun motivo per cui la maggior parte dei database non può restituire 3 set di dati separati all'interno di una chiamata e nessun join. Quindi, se lo desideri, puoi unirlo a tutti insieme nella tua app.

Altrimenti, vuoi che il database inserisca questo set di dati e poi rimuovi tutti i valori ripetuti in ogni riga che sono il risultato dei join e non necessariamente le righe stesse che hanno dati duplicati come due persone con lo stesso nome o numero di telefono . Sembra un sacco di overhead per risparmiare sulla larghezza di banda. Faresti meglio a concentrarti sulla restituzione di meno dati con un filtraggio migliore e rimuovendo le colonne che non ti servono. Poiché Select * non viene mai utilizzato in produzione, ciò dipende.

Molto semplicemente, non unire i tuoi dati se desideri risultati distinti per una query utente e una query sul numero di telefono, altrimenti come altri hanno indicato il "Set" o i dati conterranno campi aggiuntivi per ogni riga.

Rilascia 2 query distinte anziché una con un join.

Nella stored procedure o query sql craft 2 inline e restituite i risultati di entrambi. La maggior parte dei database e delle lingue supportano più set di risultati.

Ad esempio, SQL Server e C # completano la funzionalità utilizzando IDataReader.NextResult() .

Ti manca qualcosa. Se vuoi denormalizzare i tuoi dati, devi farlo da solo.

;with toList as (
    select  *, Stuff(( select ',' + (phone.phoneType + ':' + phone.PhoneNumber) 
                    from phones phone
                    where phone.user_id = user.user_id
                    for xml path('')
                  ), 1,1,'') as phoneNumbers
from users user
)
select *
from toList

Il concetto di chiusura relazionale in pratica significa che il risultato di ogni query è una relazione che può essere utilizzata in altre query come se fosse una tabella di base. Questo è un concetto potente perché rende le query componibili.

Se SQL ti permettesse di scrivere query che generano strutture di dati annidate, violerebbe questo principio. Una struttura di dati nidificata non è una relazione, quindi è necessario un nuovo linguaggio di query o estensioni complesse a SQL, per poterle interrogare ulteriormente o per unirle a quali altre relazioni.

Fondamentalmente si dovrebbe costruire un DBMS gerarchico su un DBMS relazionale. Sarà molto più complesso per un beneficio discutibile e perderai i vantaggi di un sistema relazionale coerente.

Capisco perché a volte sarebbe conveniente poter emettere dati strutturati gerarchicamente da SQL, ma il costo della complessità aggiunta in tutto il DBMS a supporto di questo non vale sicuramente la pena.

I pl si riferiscono all'uso della funzione STUFF che raggruppa più righe (numeri di telefono) di una colonna (contatto) che può essere estratta come una singola cella di valori delimitati di una riga (utente).

Oggi utilizziamo ampiamente questo aspetto, ma affrontiamo problemi elevati di prestazioni e CPU. Il tipo di dati XML è un'altra opzione ma è una modifica di progettazione non una query di livello uno.