Could you rewrite this for me? Left join a table and a subquery

CREATE TABLE TR(STUDENT_ID int, SUBJECT_ID int, grade int);
INSERT INTO tr SELECT * FROM results where results.STUDENT_ID=3;
SELECT * FROM subjects LEFT JOIN tr ON subjects.SUBJECT_ID=tr.SUBJECT_ID;
DROP TABLE TR;

Is it possible to rewrite the above as one query? I've searched all over the net and still can't do it. I'm using derby database.

The idea is that I want to join table subjects, which has a certain number of rows, with matching results from table results, if there are any开发者_开发问答 for a specific student. If there are no results, I want the ouput rows to contain only data from table subjects. So, the number of output rows will always equal the number of rows in subjects.

The above code works well, but it gives me trouble in my program, because a table has to be created. I would like to avoid that.

SELECT *
FROM subjects s
LEFT JOIN (
    SELECT * FROM
    results r
    WHERE r.STUDENT_ID = 3
) x ON s.SUBJECT_ID = x.SUBJECT_ID

Based on the example query given above, it can be rewritten as

SELECT subjects.* FROM 
subjects LEFT JOIN results 
ON subjects.SUBJECT_ID = results.SUBJECT_ID
WHERE results.STUDENT_ID = 3;

I haven't worked with derby. But I think, this should work.

SELECT * FROM subjects LEFT JOIN results ON subjects.SUBJECT_ID=results.SUBJECT_ID AND results.STUDENT_ID=3;

That should be equivalent to

SELECT * FROM subjects
LEFT JOIN results ON results.SUBJECT_ID = subjects.SUBJECT_ID AND results.STUDENT_ID = 3;

Assuming you have at most one entry in results for a specific student.