在sql select中消除特定的null值 [英] Eliminating specific null values in sql select

问题描述

因此，我正在尝试创建一份报告，以生成最佳的男性和/或最佳女性提名/获奖者.但是，当我运行我的选择时，我会得到所有没有获奖者或提名的电影.无法弄清楚如何摆脱空值.这是我的代码.顺便说一下，这是在Oracle中.

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?

推荐答案

您需要添加一个where条件，但是如果您将联接与where子句分隔开，那也更好.

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;

这篇关于在sql select中消除特定的null值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！