Hot answers tagged

153

I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?" Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my ...


72

Nope. Here's a simple test: SELECT COALESCE(1, (SELECT 1/0)) -- runs fine SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error If the second condition is evaluated, an exception is thrown for divide-by-zero. Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter - it's just an easy way to write a CASE statement. CASE ...


64

COUNT(*) will include NULLS COUNT(column_or_expression) won't. This means COUNT(any_non_null_column) will give the same as COUNT(*) of course because there are no NULL values to cause differences. Generally, COUNT(*) should be better because any index can be used because COUNT(column_or_expression) may not be indexed or SARGable From ANSI-92 (look for "...


55

How about this one - as reported to me by Itzik Ben-Gan, who was told about it by Jaime Lafargue? DECLARE @i INT = 1; SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END; Result: Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered. There are trivial workarounds of course, but the point is still that CASE does not always guarantee left-to-...


52

You can do that in pure SQL. Create a partial unique index in addition to the one you have: CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL; This way you can have (1, 2, 1) and (1, 2, 2) and (1, 2, NULL) for (a, b, c) in your table, but none of these a second time. Or use two partial indexes instead of one complete unique ...


50

Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given. I don't believe the physical storage difference is worth ...


41

I have seen database interfaces (e.g. framework libraries) that return 'null' as a string for null columns. I believe there was a flag that would turn this on or off for debugging. This flag allows developers to easily determine if the empty field was a result of a null value or an empty value. This is a bad setting, especially in production, and would ...


39

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done ...


36

Established reasons are: NULL is not a value, and therefore has no intrinsic data type. Nulls need special handling all over the place when code that otherwise relies on actual types might also receive the un-typed NULL. NULL breaks two-value (familiar True or False) logic, and requires a three-value logic. This is far more complex to even implement ...


30

My view on this is that the documentation makes it reasonably clear that the intention is that CASE should short-circuit. As Aaron mentions, there have been several cases (ha!) where this has been shown to not always be true. So far, all these have been acknowledged as bugs and fixed - though not necessarily in a version of SQL Server you can buy and patch ...


25

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


25

There's a good chance that a good chunk of your confusion stems from the journalist's. The article talks about problems using entire application systems, not just databases. Completely reasonable since this is a piece of writing aimed at mass consumption, but technical details are glossed over or misunderstood by the author. Likely a number of these issues ...


24

I disagree, nulls are an essential element of database design. The alternative, as you alluded too, would be a proliferation of known values to represent the missing or unknown. The problem lies with null being so widely misunderstood and as a result being used inappropriately. IIRC, Codd suggested the current implementation of null (meaning not present/...


23

In most DBs a NOT NULL column will be more efficient in terms of stored data for the reason you state, and also more efficient to query and index - so unless you want to allow NULLs in a column you should explicitly disallow them. There will be a slight performance implication, as the extra NOT NULL constraints will potentially need to be checked for each ...


22

When carrying out the command ALTER COLUMN ... NOT NULL This seems to be implemented as an Add Column, Update, Drop Column operation. A new row is inserted into sys.sysrscols to represent a new column. The status bit for 128 is set indicating the column does not allow NULLs An update is carried out on every row of the table setting the new columnn value ...


22

There is a (closed) connect item requesting support for NULLS LAST in SQL Server. A couple of other ways would be ORDER BY CASE WHEN FullName IS NULL THEN 1 ELSE 0 END, FullName, Login I prefer this as it doesn't rely on hardcoding a string that it is assumed no legitimate data will sort after. I'd rather not have to consider ...


21

I do not know about MySQL and PostgreSQL, but let me treat this a bit generally. There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. This clearly demonstrates that it is not necessary to distinguish between both. There are some annoying consequences: You set a varchar2 to an empty string like this: Update mytable ...


19

Boolean logic - or Three valued logic IN is shorthand for a series of OR conditions x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL) ... is the same as x <> 1 AND x <> 2 AND x <> NULL ... is the same as true AND true AND unknown ** ... = unknown ** ... which is almost the same as false in this case as it will not pass ...


18

The article itself includes a link to a Stack Overflow question that demonstrates the problem; it was in a Flex application where the code: currentChild.appendChild("Fred"); would append an element containing the word Fred to an XML document but the code: currentChild.appendChild("null"); would append an empty element, not an element containing the ...


17

Realistically, the requirement is crazy. Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale. It can be reasonable to design a database schema such that no NULL values are allowed. If you do that, however, you are ...


16

You can use SELECT * FROM A INNER JOIN B ON A.ID = B.ID AND EXISTS(SELECT A.* EXCEPT SELECT B.*)


15

It depends on the domain you are working on. NULL means absence of value (i.e. there is no value), while empty string means there is a string value of zero length. For example, say you have a table to store a person' data and it contains a Gender column. You can save the values as 'Male' or 'Female'. If the user is able to choose not to provide the gender ...


15

There is No Valid Reason to use a magic value instead of NULL. This might be the thought process of someone creating this mess. They write something like this: SELECT c1, c2 FROM t1 WHERE c3 < 30; When this doesn't return the results they are expecting, they realize that it does not include NULLs and would need to write this: SELECT c1, c2 FROM t1 ...


15

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*). It's just one of the extremely many inconsistencies in the SQL language. They make the language harder ...


14

In any recent (ie 8.x+) version of Oracle they do the same thing. In other words the only difference is semantic: select count(*) from any_table is easily readable and obvious what you are trying to do, and select count(any_non_null_column) from any_table is harder to read because it is longer it is less recognizable you have to think about whether ...


13

SELECT col1, col2, col3, 1 AS query_id FROM players WHERE username='foobar' union all select null, null, null, 1 where not exists (select 1 from players where username = 'foobar'); Or as an alternative (might be faster as no second subselect is required): with qid (query_id) as ( values (1) ) select p.*, ...


12

Take a look at PSOUG's notes on NULL. As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'. In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison. You need the special IS [NOT] NULL syntax to check if a value is NULL or not.


12

I've come across another case where CASE / COALESCE do not short circuit. The following TVF will raise a PK violation if passed 1 as a parameter. CREATE FUNCTION F (@P INT) RETURNS @T TABLE ( C INT PRIMARY KEY) AS BEGIN INSERT INTO @T VALUES (1), (@P) RETURN END If called as follows DECLARE @Number INT = 1 ...


12

NULL is not a value. Something cannot '=' NULL You want: SELECT Name FROM FinancialInstitution WHERE Name IS NULL


12

One thing I don't think I've seen mentioned: We aren't just talking about SQL. The name might start/end in the database... but to get there normally involves MULTIPLE channels. Database, Sql, php, html, javascript... Java, C#, VB, Perl, Phython, Ruby, bash, batch, etc, etc, etc... Each of these steps in the pipeline might involve converting data from one ...



Only top voted, non community-wiki answers of a minimum length are eligible