¿Por qué los RDBMS no devuelven tablas unidas en un formato anidado?

En el fondo, en las entrañas de una base de datos relacional, todas sus filas y columnas. Esa es la estructura con la que una base de datos relacional está optimizada para trabajar. los cursores funcionan en filas individuales a la vez. Algunas operaciones crean tablas temporales (de nuevo, deben ser filas y columnas).

Al trabajar solo con filas y devolver solo filas, el sistema puede manejar mejor la memoria y el tráfico de red.

Como se mencionó, esto permite realizar ciertas optimizaciones (índices, uniones, uniones, etc.)

Si uno deseara una estructura de árbol anidada, esto requiere que uno extraiga todos los datos a la vez. Se acabaron las optimizaciones para los cursores en el lado de la base de datos. Del mismo modo, el tráfico a través de la red se convierte en una gran ráfaga que puede llevar mucho más tiempo que el lento goteo de fila por fila (esto es algo que se pierde ocasionalmente en el mundo web actual).

Cada idioma tiene matrices dentro de él. Estas son cosas fáciles para trabajar e interactuar con ellas. Al usar una estructura muy primitiva, el controlador entre la base de datos y el programa, sin importar el idioma, puede funcionar de una manera común. Una vez que uno comienza a agregar árboles, las estructuras en el lenguaje se vuelven más complejas y más difíciles de recorrer.

No es tan difícil para un lenguaje de programación convertir las filas devueltas en otra estructura. Conviértalo en un conjunto de árbol o hash o déjelo como una lista de filas que puede iterar.

Aquí también hay historia en acción. Transferencia de datos estructurados era algo feo en los días de antaño. Mire el formato EDI para tener una idea de lo que podría estar pidiendo. Los árboles también implican recursión, que algunos idiomas no admitían (los dos idiomas más importantes de los viejos tiempos no eran compatibles con la recursión, recursión no No ingrese a Fortran hasta F90 y de la era COBOL tampoco lo hizo).

Y mientras que los idiomas de hoy tienen soporte para recursión y tipos de datos más avanzados, realmente no hay una buena razón para cambiar las cosas. Ellos trabajan, y funcionan bien. Los que están cambiando las cosas son las bases de datos nosql. Puede almacenar árboles en documentos en un documento basado en uno. LDAP (en realidad es antiguo) también es un sistema basado en árbol (aunque probablemente no sea lo que buscas). Quién sabe, tal vez la próxima cosa en las bases de datos nosql sea una que devuelva la consulta como un objeto json.

Sin embargo, las "antiguas" bases de datos relacionales ... están trabajando con filas porque eso es lo que hacen bien y todo puede hablarles sin problemas ni traducción.

  

12. En el diseño de protocolo, se ha alcanzado la perfección no cuando no queda nada que agregar, sino cuando no hay nada que quitar.
  

De RFC 1925 - Las doce verdades de conexión en red

Está devolviendo exactamente lo que solicitó: un único conjunto de registros que contiene el producto cartesiano definido por las combinaciones. Hay muchos escenarios válidos en los que eso es exactamente lo que querría, por lo que decir que SQL está dando un mal resultado (y, por lo tanto, implica que sería mejor si lo cambiara) en realidad arruinaría muchas consultas.

Lo que estás experimentando se conoce como " Objeto / Discrepancia de impedancia relacional, " las dificultades técnicas que surgen del hecho de que el modelo de datos orientado a objetos y el modelo de datos relacionales son fundamentalmente diferentes de varias maneras. LINQ y otros frameworks (conocidos como ORMs, Object / Relational Mappers, no por casualidad) no mágicamente "evitan esto"; Simplemente emiten diferentes consultas. También se puede hacer en SQL. Así es como lo haría:

SELECT * FROM users user where [criteria here]

Iterar la lista de usuarios y hacer una lista de ID.

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

Y luego haces la unión del lado del cliente. Así es como LINQ y otros marcos lo hacen. No hay magia real involucrada; sólo una capa de abstracción.

Podrías usar una función incorporada para concatenar los registros juntos. En MySQL puede usar la función GROUP_CONCAT() y en Oracle puede usar la función LISTAGG() .

Aquí hay una muestra de cómo se vería una consulta en 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 

Esto devolvería algo así como

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

  

El problema con esto es que está devolviendo el nombre del usuario, el DOB, el color favorito y toda la otra información almacenada

El problema es que no eres lo suficientemente selectivo. Pediste todo cuando dijiste

Select * from...

... y lo tienes (incluyendo DOB y colores favoritos).

Probablemente deberías ser un poco más (ejem) ... selectivo, y dijiste algo como:

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

También es posible que veas registros que parecen duplicados porque un user podría unirse a múltiples registros email , pero el campo que los distingue no está en tu Select instrucción, por lo que es posible que desee decir algo como

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

  

... una y otra vez para cada registro ...

También, me doy cuenta de que estás haciendo un LEFT JOIN . Esto unirá todos los registros a la izquierda de la unión (es decir, users ) a todos los registros a la derecha, o en otras palabras:

  

Una combinación externa izquierda devuelve todos los valores de una combinación interna más todos los valores de la tabla izquierda que no coinciden con la tabla derecha.

( enlace )

Otra pregunta es: ¿realmente necesita una unión a la izquierda, o un INNER JOIN hubiera sido suficiente? Son tipos de unión muy diferentes.

  

No sería mejor si devolviera una sola fila para cada usuario, y dentro de ese registro había una lista de correos electrónicos

Si realmente desea que una sola columna dentro del conjunto de resultados contenga una lista que se genere sobre la marcha, eso se puede hacer pero varía según la base de datos que esté usando. Oracle tiene la función listagg .

En última instancia, creo que su problema podría solucionarse si reescribe su consulta cerca de algo como esto:

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

Las consultas siempre producen un conjunto de datos tabular rectangular (no dentado). No hay subgrupos anidados dentro de un conjunto. En el mundo de los conjuntos, todo es un rectángulo puro no anidado.

Puedes pensar en una unión como poner 2 conjuntos uno al lado del otro. La condición "en" es cómo se comparan los registros de cada conjunto. Si un usuario tiene 3 números de teléfono, verá una duplicación de 3 veces en la información del usuario. La consulta debe producir un conjunto rectangular sin dientes. Es simplemente la naturaleza de unir conjuntos con una relación de 1 a muchos.

Para obtener lo que desea, debe usar una consulta separada, como describió Mason Wheeler.

select * from Phones where user_id=344;

El resultado de esta consulta sigue siendo un conjunto rectangular sin rectificar. Como es todo en el mundo de los conjuntos.

Tienes que decidir dónde existen los cuellos de botella. El ancho de banda entre la base de datos y la aplicación suele ser bastante rápido. No hay razón para que la mayoría de las bases de datos no puedan devolver 3 conjuntos de datos separados dentro de una llamada y no se unan. Luego puedes unirte a todos en tu aplicación si lo deseas.

De lo contrario, desea que la base de datos junte este conjunto de datos y luego elimine todos los valores repetidos en cada fila que son el resultado de las combinaciones y no necesariamente las mismas filas que tienen datos duplicados como dos personas con el mismo nombre o número de teléfono . Parece una gran cantidad de sobrecarga para ahorrar ancho de banda. Será mejor que se centre en devolver menos datos con un mejor filtrado y eliminar las columnas que no necesita. Debido a que Select * nunca se usa en la producción, eso depende.

Muy simple, no junte sus datos si desea obtener resultados distintos para una consulta de usuario y una consulta de número de teléfono, de lo contrario, como otros han señalado el "Conjunto" o los datos contendrán campos adicionales para cada fila.

Emita 2 consultas distintas en lugar de una con una combinación.

En el procedimiento almacenado o en línea parametrizado sql craft 2 consultas y devolver los resultados de ambos. La mayoría de las bases de datos e idiomas admiten varios conjuntos de resultados.

Por ejemplo, SQL Server y C # logran esta funcionalidad utilizando IDataReader.NextResult() .

Te estás perdiendo algo. Si desea desnormalizar sus datos, debe hacerlo usted mismo.

;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

El concepto de cierre relacional básicamente significa que el resultado de cualquier consulta es una relación que se puede usar en otras consultas como si fuera una tabla base. Este es un concepto poderoso porque hace que las consultas sean compuestas.

Si SQL le permitiera escribir consultas que generaran estructuras de datos anidadas, rompería este principio. Una estructura de datos anidada no es una relación, por lo que necesitaría un nuevo lenguaje de consulta, o extensiones complejas a SQL, para poder seguir consultándola o unirla con otras relaciones.

Básicamente, usted construiría un DBMS jerárquico sobre un DBMS relacional. Será mucho más complejo para un beneficio dudoso, y perderá las ventajas de un sistema relacional consistente.

Entiendo por qué a veces sería conveniente poder generar datos estructurados jerárquicamente desde SQL, pero el costo en la complejidad agregada en todo el DBMS para respaldar esto definitivamente no vale la pena.

Pls se refiere al uso de la función STUFF que agrupa varias filas (números de teléfono) de una columna (contacto) que se pueden extraer como una sola celda de valores delimitados de una fila (usuario).

Hoy estamos usando esto ampliamente, pero enfrentamos algunos problemas de rendimiento y de CPU elevados. El tipo de datos XML es otra opción, pero es un cambio de diseño, no un nivel de consulta uno.