Eliminating specific null values in sql select

问题:

So I'm trying to create a report that generates the best male and/or best female nominations/winners. However when I run my select I'm getting all the movies who do not have winners or nominations. Having trouble figuring out how to get rid of the nulls. Here's my code. This is in Oracle by the way.

/*Oscar Nominations for Best Actor Report */
SELECT 
    movie.MVYEAR as "Movie Year", 
    movie.MVTITLE as "Movie Title",
    star.STARNAME as "Star Name", 
    movstar.BESTM as "Best Male", 
    movstar.BESTF as "Best Female"
FROM 
    movie, 
    star, 
    movstar
WHERE 
    star.STARNUM = movstar.STARNUM
AND 
    movie.MVNUM = movstar.MVNUM
ORDER BY movie.MVYEAR ASC;

But my outcome contains movies with award winners and nominees as well as movies without any winners/noms resulting in a query that shows the null values. How can I get rid of the results/movies with nulls that show up in the query?

回答1:

You need to add a where condition, but it's also better if you keep your joins separated from the where clause.

      SELECT movie.MVYEAR as "Movie Year", 
             movie.MVTITLE as "Movie Title", 
             star.STARNAME as "Star Name", 
             movstar.BESTM as "Best Male", movstar.BESTF as "Best Female"
        FROM movie
  INNER JOIN movstar 
          ON movie.MVNUM = movstar.MVNUM
  INNER JOIN star
          ON star.STARNUM = movstar.STARNUM
       WHERE movstar.BESTM IS NOT NULL 
          OR movstar.BESTF IS NOT NULL
    ORDER BY movie.MVYEAR ASC;