Oracle optional select value I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query:

SELECT H.STUDENT_CODE,
       H.FIRSTNAME,
       H.LASTNAME,
       H.PROGRAM,
       H.TEACHER_CODE,
       H.COUNTRY_CODE,
       AC.COUNTRY_NAME,
       P.PROGRAM_NAME,
       T.TEACHER_NAME
    FROM HTUD H,
         PROGRAM P,
         TEACHER T,
         AP_COUNTRY AC
    WHERE H.FIRSTNAME LIKE '%ark%'
        AND H.PROGRAM = P.PROGRAM
        AND H.TEACHER_CODE = T.TEACHER_CODE
        AND H.COUNTRY_CODE = AC.COUNTRY_CODE
    ORDER BY H.STUDENT_CODE ASC 


These two columns H.TEACHER_CODE and H.COUNTRY_CODE have some empty values. If I run the query above, it returns only students that have data in these two columns H.TEACHER_CODE and H.COUNTRY_CODE. 

However, I want to select all students wether they have data in these two columns or not. If they have data in H.TEACHER_CODE and H.COUNTRY_CODE column, then return Teacher and Country also, if they don't have, just return students without Teacher or Country.

So, how do I select all students with or without Teacher or Country?
You want to make an outer join. In Oracle 8i one has to use the (+) notation.

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H,
     PROGRAM P,
     TEACHER T,
     AP_COUNTRY AC
WHERE H.FIRSTNAME LIKE '%ark%'
    AND H.PROGRAM = P.PROGRAM
    AND H.TEACHER_CODE = T.TEACHER_CODE(+)
    AND H.COUNTRY_CODE = AC.COUNTRY_CODE(+)
ORDER BY H.STUDENT_CODE ASC 


Starting with Oracle 10g xou can use the ANSI join syntax,too

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H JOIN PROGRAM P ON (H.PROGRAM = P.PROGRAM)
    LEFT OUTER JOIN TEACHER T ON (H.TEACHER_CODE = T.TEACHER_CODE)
    LEFT OUTER JOIN AP_COUNTRY AC ON (H.COUNTRY_CODE = AC.COUNTRY_CODE)
WHERE H.FIRSTNAME LIKE '%ark%'
ORDER BY H.STUDENT_CODE ASC