4. Relationships

Subqueries are one of those elements that confuse newcomers the most, but don't worry, they are learned quickly. Their function is to obtain data through some references.

Before getting into practical examples, it is important to understand the different types of relationships that can exist between tables.

Types of relationships

1:1 relationship (One to One)

A row in table A relates to a single row in table B, and vice versa. This type of relationship is less common, but useful when you want to separate information for security or performance reasons.

Example: A user has a single privacy profile.

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

1:N relationship (One to Many)

A row in table A can relate to many rows in table B, but a row in table B only relates to one row in table A. It is the most common type of relationship.

Example: A customer can have many invoices, but each invoice belongs to a single customer.

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

N:N relationship (Many to Many)

A row in table A can relate to many rows in table B, and vice versa. This type of relationship requires an intermediate table (bridge or pivot table) to work.

Example: A song can be in many playlists, and a playlist can contain many songs.

erDiagram
    Track ||--o{ PlaylistTrack : "is in"
    Playlist ||--o{ PlaylistTrack : contains
    Track {
        int TrackId PK
        string Name
        int AlbumId FK
    }
    PlaylistTrack {
        int PlaylistId FK
        int TrackId FK
    }
    Playlist {
        int PlaylistId PK
        string Name
    }

Practical example of a 1:N relationship

Let's put it into an example. I have 2 tables:

Invoice (invoices) with the columns

InvoiceId
CustomerId
InvoiceDate
BillingAddress
BillingCity
BillingState
BillingCountry
BillingPostalCode
Total

and Customer (customers) with the columns.

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

Visually, the relationship between these two tables would be:

erDiagram
    Customer ||--o{ Invoice : "makes"
    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
    }

If I want to see the name of the customer who has invoice 12, it is impossible for me. I know that InvoiceId (in Invoice) must be 12, but FirstName is in another table (Customer).

If we pay closer attention, we see that both tables have a column in common (CustomerId). A careless web architect? No, it is important. We are talking about a field that joins one table with another. This happens because SQL is a relational database! So yes, we can achieve it.

First I need to know what the CustomerId is inside the Invoice table.

SELECT CustomerId FROM Invoice WHERE InvoiceId = 12;

It tells me it is 2. Now I run another query to get the name.

SELECT FirstName FROM Customer WHERE CustomerId = 2;

The customer of invoice 2 is Leonie.

To do it in a single statement you have to use parentheses:

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

Remember that what is executed first is always what is inside the parentheses.

Another option is to perform a JOIN. A more advanced feature, which we won't cover in the course, whose use is simple: joining 2 tables when showing the results.

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

In the previous case we knew that there was only one Customer who had a CustomerId, and it cannot happen that it is duplicated. But... what if our subquery returned several results? How do we handle it? By replacing the = symbol with 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

Let's list the name of all the Tracks (Songs) of the Albums that start with the letter B.

First we get all the AlbumId.

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

2
5
12
16
17
...

35 results in total. Now the name of the Tracks.

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
...

We have it! 279 results.

To negate and achieve the opposite effect you can use NOT IN.

Solutions