Tag Info

Hot answers tagged

127

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 ...


62

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 ...


62

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 ...


49

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 ...


46

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 ...


39

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 ...


29

Fabian Pascal has an exchange with someone asking essentially the same question: “NULL confusion”. Established reasons are: NULL is not a value, and therefore has no data type. Nulls need special handling all over the place when NULL is allowed. NULL breaks two-value (familiar True or False) logic, and requires a three-value logic. This is far more ...


26

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 ...


21

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 ...


19

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 ...


19

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 ...


18

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 ...


17

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 ...


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 ...


15

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


14

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 ...


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 ...


14

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 ...


14

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 ...


12

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


11

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 ...


11

You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE SET NOCOUNT ON; CREATE TABLE Thing ( ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY, USER_CHAR1 nvarchar(150) null, ...


11

Looking at it as a grammar problem, ANY is defined as (in Row and Array Comparisons): expression operator ANY (array expression) But is distinct from is not an operator, it's a "construct" as we're told in Comparison Operators: When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs Since PostgreSQL has user-defined ...


10

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.


10

If you want to skip NULL values (but not empty strings), you can use CONCAT_WS() function: CONCAT_WS( ', ', -- Separator CONCAT_WS(' ', tadd.street_number, tadd.street_name), tadd.apt_number, tadd.city, tadd.postal_code, tadd.country ) AS Address From the docs: CONCAT_WS(separator,str1,str2,...) ...


9

In a recent version there is indeed no difference between count(*) and count(any not null column), with the emphasize on not null :-) Have incidentally covered that topic with a blog post: Is count(col) better than count(*)?


8

A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row (example EscapeVelocity for a black hole Star). In my opinion, it's usually better to normalize the tables by eliminating all Nulls. In your case, you want to ...


8

What version of mysql is this? What mode are you running in? SELECT @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE; (This should be run in the context of your application, just in case it is changing it). MySQL is documented thus: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html As of MySQL 5.0.2, if a column definition includes no explicit ...


8

It's utter madness and there's no justification for it. NULL was created to represent the absence of a value & to use an actual value like -5000 is bonkers. Ordinarily I wouldn't write an answer this short, but the question deserves to be one of the most visible on dba.se & the more answers the better.


8

Wikipedia's article on SQL Null has some interesting remarks about the NULL value, and as a database-agnostic answer, as long as you are aware of the potential affects of having NULL values for your specific RDBMS, they are acceptable in your design. If they were not, you wouldn't be able to specify columns as nullable. Just be aware of how your RDBMS ...



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