sqlite left-join composite-primary-key cross-join natural-join

Sqlite left join composite keys

Unrolling an SQLite pivot query for a static ORM, the problem is that it needs nulls for missing values.

table student
student_id | name
    1       "Fred"
    2       "Tim"
PK(`student_id`)

table grade 
student_id | data_id | grade
    1           1       5.0
    1           2       5.0
    2           2       5.0
PK(`student_id`,`data_id`),
FK(student.studentid)
FK(data.data_id)

table data 
data_id | description
   1       "summer"
   2       "autumn"
PK(`data_id`)

I need the results to include a null row for the static ORM to tablulate correctly. In my mind, that should mean a LEFT join:

SELECT * FROM student
join grade using (student_id)
LEFT OUTER JOIN data
ON grade.data_id = data.data_id

As Tim was absent for his summer exam, there is no row for student_id | data_id PK_pair(2,1) in table grade.

The query currently returns:

sID |  name  | dID | grade | description
"1"   "Fred"   "1"   "5.0"   "summer"
"1"   "Fred"   "2"   "5.0"   "autumn"
"2"   "Tim"    "2"   "5.0"   "autumn"

This row is missing in result:

sID |  name  | dID | grade | description
"2"    "Tim"   "1"    null    "summer"

Solution

Left join returns inner join rows plus unmatched left table rows extended by nulls. If you think you want a left join then you need to identify the associated inner join. Here you don't seem to know the tables & condition. But you seem to at least want a row for every possible student-data pair; indeed, for every combination of (student_id, name) & (data_id, description). So those must be in the left table. Also, column grade is null, so presumably it's involved with the right table. Maybe you want:

select *
from students
natural join data
left natural join grade

I picked that query because it's (for no nulls in common columns & no duplicate rows):

/* rows where
    student [student_id] has name [name]
and term [data_id] has description [description]
and (student [student_id] got grade [grade] in term [data_id]
    or not exists grade [student [student_id] got grade [grade] in term [data_id]]
    and [grade] is null
    )
/*

Sqlite left join composite keys

Although constraints tell us some things about a query result, they aren't needed to query. What is needed is the query result's membership criterion, ie its (characteristic) predicate. Which in this case I gave in that code comment. Notice how it is built from the base tables' criteria/predicates:

natural join holds rows satisfying the and of its tables' criteria/predicates.
left natural join holds rows satisfying the and of its left table's membership criterion with a certain or using the right table's criterion/predicate & the condition that each column unique to the right table is null.

Is there any rule of thumb to construct SQL query from a human-readable description?.

(If columns description & name had the same name then you'd have to either rename one before the natural join or use inner join using (student_id, data_id). But again, that would arise from composing the appropriate predicate.)