I've created a table of students with columns student_id as primary key,
student_name and gender.
I've an another table gender which consists of gender_id and gender.
gender_id in student refers to table gender.
Tables data looks like this:
Student table
STUDENT_ID STUDENT_NAME GENDER
1 Ajith 1
2 Alan 1
3 Ann 2
4 Alexa 2
5 Amith 1
6 Nisha 2
7 Rathan 1
8 Rebecca 2
9 asdf null
10 asd null
11 dbss null
Gender Table
GENDER_ID GENDER
1 Male
2 Female
3 Others
My query and its result
SELECT S.STUDENT_NAME,
G.GENDER
FROM STUDENTS S
FULL OUTER JOIN GENDER G ON G.GENDER_ID = S.GENDER
result is giving with 12 rows including the Others value from the gender table.
STUDENT_ID STUDENT_NAME GENDER
1 Ajith Male
2 Alan Male
3 Ann Female
4 Alexa Female
5 Amith Male
6 Nisha Female
7 Rathan Male
8 Rebecca Female
Others
9 asdf
10 asd
11 dbss
I'm trying to restrict a particular student_id:
SELECT S.STUDENT_ID,
S.STUDENT_NAME,
G.GENDER
FROM STUDENTS S
FULL OUTER JOIN GENDER G ON G.GENDER_ID = S.GENDER
WHERE S.STUDENT_ID <> 11;
now the the total number of the rows are reduced to 10.
STUDENT_ID STUDENT_NAME GENDER
1 Ajith Male
2 Alan Male
3 Ann Female
4 Alexa Female
5 Amith Male
6 Nisha Female
7 Rathan Male
8 Rebecca Female
9 asdf
10 asd
Why has the one row with Others Values disappeared from the second select query? I'm trying to find the cause of this issue.
Your second select query returns all rows where student_id is different (<>) from 11.