쿼리 0 / 0 = NULL?

Question

안녕하세요 궁금사항이있어 질문드립니다.ABCD LEFT 걸고 (A가 모 B, C, D가 자) ROUND(IFNULL(C.AMT, 0) / IFNULL(C.AMT2, 0), 2) 이렇게 했는데 NULL이 나올가능성이 있나요...?LEFT니까 없으면 NULL이 붙을꺼고.. IFNULL 처리를 해줬는데 몇몇데이터가 NULL이 나오는 데이터가 있어 질문드립니다자세한내용이 있는건 아니라 확실한 상황을 이해하시긴 어렵겠지만 추측되는 내용이라도 아신다면 답변 부탁드리겠습니다..

bayleys · Answer

select round(ifnull(null, 0) / ifnull(null, 0), 2); null

코딩하는 두루미 · Answer

이거 두개 쿼리문 쳐보시는게 글 올리시는거보단 빨랐을텐데 한번쯤 직접 해보시는것도..

bayleys · Answer

The expression select round(ifnull(null, 0) / ifnull(null, 0), 2) returns NULL due to the behavior of division by zero in SQL. Let's break down the expression to understand why:1. First, let's evaluate the IFNULL functions: - ifnull(null, 0) returns 0 because the first argument is NULL[2]. - So, the expression becomes round(0 / 0, 2).2. Now, we have a division of 0 by 0: - In SQL, division by zero is undefined and results in NULL[5]. - This is a mathematical principle applied in most database systems, including MySQL.3. Finally, the ROUND function is applied: - Since the input to ROUND is NULL (result of 0/0), it simply returns NULL.It's important to note that in SQL, any arithmetic operation involving NULL typically results in NULL[3]. This is part of SQL's three-valued logic system, where operations on unknown values (NULL) produce unknown results.To avoid this issue in practical scenarios, you might want to add additional checks or use alternative functions depending on your specific requirements. For example, you could use a CASE statement or nested IFNULLs to handle potential division by zero situations.Citations:[1] https://stackoverflow.com/questions/53918298/mysql-ifnull-still-returns-null[2] https://dev.mysql.com/doc/refman/8.4/en/flow-control-functions.html[3] https://clickhouse.com/docs/en/sql-reference/functions/functions-for-nulls[4] https://www.castordoc.com/how-to/how-to-use-ifnull-in-mysql[5] https://www.w3schools.com/sql/func_mysql_ifnull.asp[6] https://www.w3resource.com/mysql/control-flow-functions/if-null-function.php[7] https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions[8] https://www.castordoc.com/how-to/how-to-use-ifnull-in-sql-server[9] https://www.w3schools.com/sql/sql_isnull.asp[10] https://www.y42.com/learn/snowflake/ifnull

코딩만할래 · Answer

저의 무지함을 깨달았습니다.. 감사합니다

쿼리 0 / 0 = NULL?

4개의 답변

Q&A 목록