Return in which or statement results are from SQL

Question

I'm having the following SQL statement, to return worksheets that teachers have liked, and worksheets that teachers made by themselves.

SELECT worksheets.title, worksheets.worksheet_id
FROM worksheets
WHERE worksheets.worksheet_id 
IN (
    SELECT worksheet_id FROM likes WHERE teacher_id = 5
) OR worksheets.worksheet_id 
IN (
    SELECT worksheet_id FROM worksheets WHERE teacher_id = 5
)

It's working fine, but now I also want to return if the worksheet was liked, or made by the teacher. How do I do that? I tried things with an if statement, but could not figure out a working solution.

Answer 1

How about a left join and conditional logic?

select w.title, w.worksheet_id,
    (w.teacher_id = 5)           as is_made,
    (l.worksheet_id is not null) as is_liked
from worksheets w
left join from likes l 
    on l.worksheet_id = w.worksheet_id and l.teacher_id = 5
where w.teacher_id = 5 or l.worksheet_id is not null

This puts the "made or like" information in two different columns (after all, a teacher could have made and liked a worksheek).

OR sometimes kills performance. union all might be more efficient:

select title, worksheet_id, max(is_made) as is_made, max(is_liked) as is_liked
from (
    select title, worksheet_id, 1 as is_made, 0 as is_liked 
    from worksheets 
    where teacher_id = 5
    union all
    select w.title, w.worksheet_id, 0, 1
    from worksheets w
    where exists (select 1 from likes l where j.teacher_id = 5 and l.worksheet_id = w.worksheet_id)
) t
group by title, worksheet_id