Joins are used to make queries that collect data from two tables.
The simplest form of a JOIN, called a Cross Join or a Cartesian Join, consists of simply listing all the tables we want to collect data from.
For example if we want the name of each person in four tables we could use this query:
SELECT Lead.name, Rhythm.name, Bass.name, Drums.name FROM Lead, Rhythm, Bass, Drums;
Let’s say we have 5 names in Lead, 6 in Rhythm, 7 in Bass, 9 in Drums.
Using a cross join will return every combination, i.e. 5x6x7x9 results in our results table.
Because the number of results grows so fast, we should carefully consider our constraints in the WHERE clause to limit the results.
Let’s say we have a relationship like this:
The example below is another way of executing a JOIN, called an Inner Join.
SELECT Planet.Name, Moon.Name, HasLiquidWater FROM Planet, Moon WHERE Planet.Name=Moon.HasPlanet AND DayLength < 11;
This will work but a more explicit (or better) version of the Inner Join would be:
SELECT Planet.Name, Moon.Name, HasLiquidWater FROM Planet INNER JOIN Moon ON Planet.Name=Moon.HasPlanet WHERE DayLength < 11;
The Outer Join is another type of JOIN. There are 3 types of Outer Join:
An example of a query:
SELECT Planet.Name, Moon.Name, HasLiquidWater FROM Planet LEFT JOIN Moon ON Planet.Name=Moon.HasPlanet;
Cardinality is how many rows in each of the tables that participate in a join match with how many rows in the other table.
It's often expressed in terms of a ratio (1:1, 1:n etc).
A 1:n relationship is where one row in table x joins with zero, one or more rows in table y
eg planets can have no moons (Mercury), one moon (Earth) or many moons (Jupiter)
A 1:1 relationship is where one row in table x joins one row in table y
This is quite rare. eg students only have one final project, so this is an example of a 1:1 relationship.
A m:n relationship is many to many - any number of rows in table x join with any number of rows in table y.
We use different letters to show that they aren't necessarily the same number.
eg Students on a degree programme may have multiple tutors; tutors may have multiple students.
Why should we care about cardinality?
Because it changes the implementation.
For 1:n relationships (or n:1, depending on which order the tables are in):
Use a FOREIGN KEY - place the primary key of the table with 1 value into the table with n values.
This is how you draw 1:n cardinality: (m = many, 1 = 1)
If a moon always has a planet, you can show this with a double line:
Here's an example of how to declare this:
CREATE TABLE Moons ( MoonName CHAR(20) PlanetName CHAR(10), Diameter INT, PRIMARY KEY (MoonName), FOREIGN KEY (PlanetName) REFERENCES Planets(PlanetName) );
The first PlanetName in the foreign key refers to the PlanetName in Moons.
For 1:1 relationships:
You'd normally put them in the same table if this is the case. But sometimes you might not and we'll look at this later.
CREATE TABLE Projects ( Student VARCHAR(100) Title VARCHAR(100), Mark INT, PRIMARY KEY (Student), );
For m:n relationships:
Take the example where students on a degree programme may have multiple tutors; tutors may have multiple students.
We need to create a link table to link up those many relationships between students and tutors.
CREATE TABLE TutorRole ( Student VARCHAR(100) Tutor VARCHAR(100), PRIMARY KEY (Student, Tutor), );