Selecting rows where a column is null

Question

I have 2 tables.

submission(submissionID, teamID)  
team(teamID, teamNames)

I have 2 records inside team: (2, john) and (3, peter)

inside submission i have: (3,3)

When i use this:

select teamName, t.teamID
from team t
inner join submission s on t.teamID = s.teamID
where submissionID is null

it return no result back

I have seen some of the example of selecting column when is null but it doesn't work for me.

MySQL: selecting rows where a column is null

Select rows where column is null

change the `inner join` to `left join` – Abhik Chakraborty Dec 15 '14 at 12:35 — Abhik Chakraborty, Dec 15 '14 at 12:35

score 2 · Accepted Answer · answered Dec 15 '14 at 12:38

If you are trying to find teams without submissions, use an outer join:

select teamName, t.teamID
from team t
LEFT join submission s on t.teamID = s.teamID
where submissionID is null

Teams with no matching rows in submission will still be returned with a left join, but all columns from the joined table will be null.

The where clause filters the results after the join is made, so specifying null for a non-null column of the joined table will only return joins that missed.

score 0 · Answer 2 · answered Dec 15 '14 at 12:45

I am not sure about the mysql but in oracle you can use Outer joins There are 3 types of outer join

1.Left Outer Join 2.Right Outer Join 3.Full Outer Join

And you can either use a full outer join:-

select * from submission s full outer join team t on (t.teamID = s.teamID);

Selecting rows where a column is null

2 Answers2