4. Relaciones

Las subconsultas son uno de esos elementos que más confunde a los recién llegados, pero no te agobies que se aprende rápido. Su función es la de obtener datos a través de unas referencias.

Antes de entrar en ejemplos prácticos, es importante entender los diferentes tipos de relaciones que pueden existir entre tablas.

Tipos de relaciones

Relación 1:1 (Uno a Uno)

Una fila de la tabla A se relaciona con una única fila de la tabla B, y viceversa. Este tipo de relación es menos común, pero útil cuando quieres separar información por motivos de seguridad o rendimiento.

Ejemplo: Un usuario tiene un único perfil de privacidad.

erDiagram
    Usuario ||--|| PerfilPrivacidad : tiene
    Usuario {
        int UsuarioId PK
        string Nombre
        string Email
    }
    PerfilPrivacidad {
        int PerfilId PK
        int UsuarioId FK
        boolean PerfilPublico
        boolean MostrarEmail
    }

Relación 1:N (Uno a Muchos)

Una fila de la tabla A puede relacionarse con muchas filas de la tabla B, pero una fila de la tabla B solo se relaciona con una fila de la tabla A. Es el tipo de relación más común.

Ejemplo: Un cliente puede tener muchas facturas, pero cada factura pertenece a un único cliente.

erDiagram
    Customer ||--o{ Invoice : tiene
    Customer {
        int CustomerId PK
        string FirstName
        string LastName
    }
    Invoice {
        int InvoiceId PK
        int CustomerId FK
        decimal Total
    }

Relación N:N (Muchos a Muchos)

Una fila de la tabla A puede relacionarse con muchas filas de la tabla B, y viceversa. Este tipo de relación requiere una tabla intermedia (tabla puente o pivot) para funcionar.

Ejemplo: Una canción puede estar en muchas playlists, y una playlist puede contener muchas canciones.

erDiagram
    Track ||--o{ PlaylistTrack : "está en"
    Playlist ||--o{ PlaylistTrack : contiene
    Track {
        int TrackId PK
        string Name
        int AlbumId FK
    }
    PlaylistTrack {
        int PlaylistId FK
        int TrackId FK
    }
    Playlist {
        int PlaylistId PK
        string Name
    }

Ejemplo práctico de relación 1:N

Pongamos un ejemplo. Tengo 2 tablas:

Invoice (facturas) con las columnas

InvoiceId
CustomerId
InvoiceDate
BillingAddress
BillingCity
BillingState
BillingCountry
BillingPostalCode
Total

y Customer (clientes) con las columnas.

CustomerId
FirstName
LastName
Company
Address
City
State
Country
PostalCode
Phone
Fax
Email
SupportRepId

Visualmente, la relación entre estas dos tablas sería:

erDiagram
    Customer ||--o{ Invoice : "realiza"
    Customer {
        int CustomerId PK
        string FirstName
        string LastName
        string Email
        string Country
    }
    Invoice {
        int InvoiceId PK
        int CustomerId FK
        date InvoiceDate
        string BillingCity
        string BillingCountry
        decimal Total
    }

Si yo quiero ver el nombre del cliente que tiene la factura 12 me resulta imposible. Sé que InvoiceId (en Invoice) debe ser 12, pero FirstName está en otra tabla (Customer).

Al prestar más atención vemos que ambas tablas tiene una columna en común (CustomerId). ¿Un arquitecto web despistado? No, es importante. Hablamos de un campo que une una tabla con otra. Esto ocurre porque ¡SQL es una base de datos relacional! Por lo que si podemos conseguirlo.

Primero debo saber cual es el CustomerId dentro de la tabla Invoce.

SELECT CustomerId FROM Invoice WHERE InvoiceId = 12;

Me dice que es 2. Ahora hago otra consulta para obtener el nombre.

SELECT FirstName FROM Customer WHERE CustomerId = 2;

El cliente de la factura 2 es Leonie.

Para hacerlo en una sola sentencia hay que usar paréntesis:

SELECT FirstName FROM Customer WHERE CustomerId = (SELECT CustomerId FROM Invoice WHERE InvoiceId = 12);

Recuerda que lo primero que se ejecuta es siempre lo que hay entre paréntesis.

Otra posibilidad es realizando un JOIN. Una característica más avanzada, que no tocaremos en el curso, cuyo uso es simple es la de unir 2 tablas al mostrar los resultados.

SELECT * FROM Invoice JOIN Customer ON Customer.CustomerId = Invoice.CustomerId;

1|2|2009-01-01 00:00:00|Theodor-Heuss-Straße 34|Stuttgart||Germany|70174|1.98|2|Leonie|Köhler||Theodor-Heuss-Straße 34|Stuttgart||Germany|70174|+49 0711 2842222||leonekohler@surfeu.de|5
2|4|2009-01-02 00:00:00|Ullevålsveien 14|Oslo||Norway|0171|3.96|4|Bjørn|Hansen||Ullevålsveien 14|Oslo||Norway|0171|+47 22 44 22 22||bjorn.hansen@yahoo.no|4
3|8|2009-01-03 00:00:00|Grétrystraat 63|Brussels||Belgium|1000|5.94|8|Daan|Peeters||Grétrystraat 63|Brussels||Belgium|1000|+32 02 219 03 03||daan_peeters@apple.be|4
4|14|2009-01-06 00:00:00|8210 111 ST NW|Edmonton|AB|Canada|T6G 2C7|8.91|14|Mark|Philips|Telus|8210 111 ST NW|Edmonton|AB|Canada|T6G 2C7|+1 (780) 434-4554|+1 (780) 434-5565|mphilips12@sh
aw.ca|5
5|23|2009-01-11 00:00:00|69 Salem Street|Boston|MA|USA|2113|13.86|23|John|Gordon||69 Salem Street|Boston|MA|USA|2113|+1 (617) 522-1333||johngordon22@yahoo.com|4

IN

En el caso anterior sabíamos que solo había un Cliente que tuviera una CustomerId, y no se puede dar el caso que se duplique. Pero... ¿y si nos diera varios resultados nuestra subconsulta? ¿Cómo lo gestionamos? Sustituyendo el símbolo = por IN.

SELECT InvoiceId, BillingCity, Total FROM Invoice WHERE InvoiceId IN (1, 2, 3);

1|Stuttgart|1.98
2|Oslo|3.96
3|Brussels|5.94

Vamos a listar en nombre de todas las Tracks (Canciones) de los Albums que empiecen con la letra B.

Primero obtenemos todos los AlbumId.

SELECT AlbumId FROM Album WHERE Title LIKE 'b%' COLLATE NOCASE;

2
5
12
16
17
...

35 resultos en total. Ahora el nombre de los Trancks.

SELECT name FROM Track WHERE AlbumId IN (SELECT AlbumId FROM Album WHERE Title LIKE 'b%' COLLATE NOCASE);

Balls to the Wall
Walk On Water
Love In An Elevator
Rag Doll
What It Takes
Dude (Looks Like A Lady)
Janie's Got A Gun
...

¡Lo tenemos! 279 resultados.

Para negar y conseguir el efecto contrarío se puede usar NOT IN.

Soluciones