SQLite 3.8

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Students
(
  StudentId INT PRIMARY KEY,
  Name VARCHAR(100)
);
CREATE TABLE Scores
(
  ScoreId INT PRIMARY KEY,
  ExamId INT NOT NULL,
  StudentId INT NOT NULL,
  Score DECIMAL(4,1) NOT NULL DEFAULT 0,
  FOREIGN KEY (StudentId) 
    REFERENCES Students(StudentId)
);
INSERT INTO Students
(StudentId, Name) VALUES
(11,'Joe Shmoe'),
(12,'Jane Doe'),
(47,'Norma Nelson');
INSERT INTO Scores
(ScoreId, ExamId, StudentId, Score) VALUES
(1, 101, 11, 65.2),
(2, 101, 12, 72.6),
(3, 102, 11, 69.6);
--
-- Using an INNER JOIN
--
-- Only Students that have scores
-- So only when there's a match between the 2 tables
--
SELECT stu.Name, sco.Score
FROM Students AS stu
INNER JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId
ORDER BY stu.Name


Name
Score


Jane Doe
72.6

Joe Shmoe
65.2

Joe Shmoe
69.6


--
-- Using an LEFT JOIN
--
-- All Students, even those without scores
-- Those that couldn't be matched will show NULL's
-- for the fields from the joined table
--
SELECT stu.Name, sco.Score, sco.ScoreId
FROM Students AS stu
LEFT JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId
ORDER BY stu.Name


Name
Score
ScoreId


Jane Doe
72.6
2

Joe Shmoe
65.2
1

Joe Shmoe
69.6
3

Norma Nelson
null
null


--
-- Using an LEFT JOIN
-- But with an extra criteria in the ON clause
--
-- All Students again.
-- That have scores >= 66
-- But also the unmatched without scores
-- 
SELECT stu.Name, sco.Score, sco.ScoreId
FROM Students AS stu
LEFT JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId

 AND sco.Score >= 66.0
 
ORDER BY stu.Name


Name
Score
ScoreId


Jane Doe
72.6
2

Joe Shmoe
69.6
3

Norma Nelson
null
null


--
-- Using an LEFT JOIN
-- But with an extra criteria in the WHERE clause
--
-- Only students with scores >= 66
-- The WHERE filters out the unmatched.
-- 
SELECT stu.Name, sco.Score
FROM Students AS stu
LEFT JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId

WHERE sco.Score >= 66.0

ORDER BY stu.Name


Name
Score


Jane Doe
72.6

Joe Shmoe
69.6


--
-- Using an INNER JOIN
-- And with an extra criteria in the WHERE clause
--
-- Only Students that have scores >= 66
--
SELECT stu.Name, sco.Score
FROM Students AS stu
INNER JOIN Scores AS sco
  ON sco.StudentId = stu.StudentId
WHERE sco.Score >= 66
ORDER BY stu.Name


Name
Score


Jane Doe
72.6

Joe Shmoe
69.6
Name	Score	ScoreId
Jane Doe	72.6	2
Joe Shmoe	65.2	1
Joe Shmoe	69.6	3
Norma Nelson	null	null