Joins

Att använda mer än en tabell

I tidigare kapitel tittad vi på enkla queries. Dock fanns ett problem, nämligen att vi bara kunde interagera med en tabell. Men hur gör vi om vi exempelvis vill veta användarnamnet på den som postade en viss tweet? För att kunna göra det måste vi slå ihop de två tabeller till en. Det kan vi göra med hjälp av joins. Dock finns det olika joins för olika situationer.

CROSS JOIN

Den minst använda (och mest naiva) lösningen är att joina med en CROSS JOIN. Det betyder att vi skapar en tabell som har alla möjliga kombinationer av tuples. Om vi har 10 användare och 10 tweets kommer det resultera i en table på 100 tuples. För att illustrera detta joinar vi två tabeller: first(character, number) och second(sign, symbol).

Varje enskild tuple i ena tabellen paras ihop med alla tuples i den andra. Detta skapar en ny temporär tabell third(character, number, sign, symbol).

Om vi gör detta på våra två tabeller users och tweets kommer vi få en ny tabell users_and_tweets(user_id, username, email, age, tweet_id, poster_id, content, time_posted). Utifrån denna tabell kan vi välja användarnamnet ifrån den tuple där tweet_id matchar den tweet vi söker (exempelvis 15) och där user_id och poster_id är densamma.

SELECT username
FROM   users CROSS JOIN tweets
WHERE  tweet_id = 15 AND user_id = poster_id;

För att ge en annan förklaring kan man tänka sig att vi tar alla våra användare och kombinerar dem med alla våra tweets. Sedan tittar vi igenom alla tuples och letar den tuple där tweet_id är 15 och user_id matchar poster_id.

Ett problem med CROSS JOIN (och andra joins för den delen) förekommer om du joinar två tabeller som har attribut med likadana namn. Då kan det vara oklart vilket attribut du refererar till. Då måste man explicit specificera attributet genom att först skriva tabellens namn, följt av punkt och sedan attributnamnet.

SELECT *
FROM  users CROSS JOIN followers
WHERE users.user_id = followers.user_id;

INNER JOIN

Ett effektivare sätt att göra vad vi gjorde ovan är att joina baserat på ett villkor, istället för att joina alla kombinationer och sen sålla bort de vi inte vill ha. Vad vi vill är att skapa en table där user_id och poster_id är detsamma värde. Denna query ger samma resultat som den ovan:

SELECT username
FROM   users INNER JOIN tweets ON user_id = poster_id
WHERE  tweet_id = 15;

Med denna join vet vi att den resulterande tabellen kommer att matcha alla användare med det tweet de har postat. Därefter behöver vi bara filtrera efter bort de tweet som inte har ID 15. Denna typ av join är så vanlig att man kan strunta i att skriva INNER och bara skriva JOIN.

NATURAL JOIN

NATURAL JOIN fungerar likt JOIN ON, men implicit sammanfogar attribut som har samma namn. Vi kommer inte använda denna i kursen då den har sina problem. Exempelvis kan ett namnbyte på ett attribut ge oväntade resultat.

Relationen mellan NATURAL JOIN och JOIN ON är följande:

SELECT *
FROM users NATURAL JOIN followers;

-- Ekvivalent som:
SELECT *
FROM users JOIN followers ON users.user_id = followers.user_id;

LEFT OUTER JOIN

Denna join är en utbyggnad på INNER JOIN. Först kommer alla tuples mellan de två tabellerna att sammanfogas baserat på villkoret som ges, precis som vid INNER JOIN, och sedan läggs de kvarstående tuples från den första tabellen till.

SELECT username, content
FROM   users LEFT OUTER JOIN tweets ON user_id = poster_id;

Med ovanstående query får vi en tabell med attributen username och content där alla tuples består av ett användarnamn och innehållet av användarens tweet eller ett användarnamn och NULL för de användare som inte postat några tweets. Ett exempel skulle vara:

För en INNER JOIN skulle de två sista inte komma med eftersom de inte kan sammankopplas med något tweet.

RIGHT OUTER JOIN

RIGHT OUTER JOIN är precis som LEFT OUTER JOIN, men istället för att lägga till de kvarstående tuplerna från den första tabellen så lägger man till de kvarstående tuplerna från den andra tabellen.

FULL OUTER JOIN

Denna join är en kombination av de tre föregående. Först joinar man de tuples som matchar villkoret, sedan lägger man till de kvarstående från den första tabellen, och till sist de kvarstående tuplerna från den andra tabellen.