Page tree
Skip to end of metadata
Go to start of metadata

The single most powerful feature of SQL is it allows you to match elements from two table.

Let's assume two tables

Pupils
NameGradeParentAddress
Noah11Michael Wilson72 Oxford Street 
Kim8Ed Thumpson67 York Street
Viktor3Brian Carter16 Darlinghurst Street
James9Helen Wright59 Paul Street
Liam5Karen Collins82 Newtown Park
Sandra6Karen Collins82 Newtown Park
Marks
PupilNameSubjectMark
NoahMathematicsB+
NoahDrawingB-
JamesDrawingA-
JamesSportsC

Querying on two tables (JOIN)

You want to get all marks for each pupil:

SELECT Pupils.Name, Pupils.Parents, Marks.Subject, Marks.Mark
FROM Pupils
LEFT JOIN Marks ON Pupils.Name = Marks.PupilName
Name
Parents
Subject
Mark
NoahMichael WilsonMathematicsB+
JamesHelen WrightDrawingA-
...

 

This query:

  • Takes all records of the Pupils table and the ones of the Marks table;
  • And filters them, keeping only the records where the Pupil Name matches the name on the Marks table.

The technical name is a Join. This is often referred to as a "Multiplication", because the number of records in the intermediary table (before filtering) is a multiplication of the two tables:

Pupils
NameGradeParentAddress
Noah11Michael Wilson72 Oxford Street 

... other records ...

x

Marks
PupilNameSubjectMark
NoahMathematicsB+
NoahDrawingB-
JamesDrawingA-
JamesSportsC

 

=

Multiplication
Repeat each record of the table PupilsFor each record on the left, attempt to associate all records from Marks
Pupils.NamePupils.GradePupils.ParentPupils.AddressMarks.PupilNameMarks.SubjectMarks.Mark
Noah11Michael Wilson72 Oxford StreetNoahMathematicsB+
Noah11Michael Wilson72 Oxford StreetNoahDrawingB-
Noah11Michael Wilson72 Oxford StreetJamesDrawingA-
Noah11Michael Wilson72 Oxford StreetJamesSportsC
...

Red cells are associations which don't match the criteria "Pupils.Name = Marks.PupilName". After filtering them out and retaining only columns listed in the SELECT clause, here's the reduced table:

Multiplication
Each record of the table PupilsFor each record on the left, attempt to associate all records from Marks
NameParentAddressNameSubjectMark
NoahMichael Wilson72 Oxford StreetNoahMathematicsB+
NoahMichael Wilson72 Oxford StreetNoahDrawingB-

Several kinds of JOINs

  • LEFT JOIN will keep records from the left table in case no association matches it. Example: If a pupil doesn't have any mark yet, its record will still appear, and the columns on the right will be empty (NULL in SQL).
  • INNER JOIN will filter out records which don't match.
  • OUTER JOIN will be the same as the left join, except records from both sides, which don't match any association, will be retained.
  • Join by listing tables with commas. Example: SELECT * FROM Pupils, Marks. This is a less explicit form of joining, therefore I will not explain it here.

The most intuitive form for beginners is the LEFT JOIN.

Using the WHERE clause with LEFT JOIN

So you have a student with no mark, such as the result of the query is:

SELECT Pupils.Name, Pupils.Parents, Marks.Subject, Marks.Mark
FROM Pupils
LEFT JOIN Marks ON Pupils.Name = Marks.PupilName
Name
Parents
Subject
Mark
NoahMichael WilsonDrawingA-
NoahMichael WilsonMathematicsB+
JamesHelen WrightDrawingA+
JamesHelen WrightMathematicsB-
ViktorBrian Carter--
...

The minus indicates empty fields (Fields with no values are named "NULL" in SQL).

How would you get everyone's mark in Mathematics?

Will you spot the mistake?
SELECT Pupils.Name, Pupils.Parents, Marks.Subject, Marks.Mark
FROM Pupils
LEFT JOIN Marks ON Pupils.Name = Marks.PupilName
WHERE Marks.Subject = 'Mathematics'
Name
Parents
Subject
Mark
NoahMichael WilsonMathematicsB+
JamesHelen WrightMathematicsB-

Do you notice how Viktor has disappeared?

We've set a criteria, but Viktor doesn't have any mark in Mathematics, so his record is filtered. How can I get the list of all students?

  • First, you must use a LEFT JOIN, so all students appear in the result even if no record from Marks can be associated;
  • Second, you mustn't keep the criteria in WHERE, you must move it to the ON clause.

Here's the result:

Solution
SELECT Pupils.Name, Pupils.Parents, Marks.Subject, Marks.Mark
FROM Pupils
LEFT JOIN Marks ON Pupils.Name = Marks.PupilName AND Marks.Subject = 'Mathematics'
Name
Parents
Subject
Mark
NoahMichael WilsonMathematicsB+
JamesHelen WrightMathematicsB-
ViktorBrian Carter--

Excellent! You've learnt the most usual pitfall of using joins.

 

If you find any mistake on this page, please email me. My license for this website is restricted to a few users.

  • No labels