use student_cz
go

/*
	Join tables with WHERE
*/

--dotaz na vysledky s WHERE s dotazem na konkrétního člověka
select STUDENT_NAME, SURNAME, SUBJECT_NAME, EXAM_RESULT_POINTS, EXAM_RESULT
   from STUDENT, RESULT, SUBJECTS
     where STUDENT.ID_STUDENT = RESULT.ID_STUDENT
              and
           SUBJECTS.ID_SUBJECT = RESULT.ID_SUBJECT
                    and
           STUDENT.SURNAME = 'Turek'



/*
	Join tables with operator JOIN
*/

select STUDENT_NAME, SURNAME, SUBJECT_NAME, EXAM_RESULT_POINTS, EXAM_RESULT 
   from STUDENT
	left join RESULT on STUDENT.ID_STUDENT = RESULT.ID_STUDENT
	left join SUBJECTS  on  SUBJECTS.ID_SUBJECT = RESULT.ID_SUBJECT
        where        
           STUDENT.SURNAME = 'Turek'


-- What will be differnce between this two queries?
-- query with LEFT JOIN
select STUDENT_NAME, SURNAME, SUBJECT_NAME, EXAM_RESULT_POINTS, EXAM_RESULT
   from STUDENT
	left join RESULT on STUDENT.ID_STUDENT = RESULT.ID_STUDENT
	left join SUBJECTS  on  SUBJECTS.ID_SUBJECT = RESULT.ID_SUBJECT


-- query with RIGHT JOIN
select STUDENT_NAME, SURNAME, SUBJECT_NAME, EXAM_RESULT_POINTS, EXAM_RESULT
   from STUDENT
	left join RESULT on STUDENT.ID_STUDENT = RESULT.ID_STUDENT
	right join SUBJECTS  on  SUBJECTS.ID_SUBJECT = RESULT.ID_SUBJECT
       


/*
	Right join - all rows from right table (SUBJECTS) despite that there are no equivalents in results (table RESULT)
*/