Tag Info

Hot answers tagged

95

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


53

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


38

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


21

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. Additional notes No use for mixed case identifiers ...


20

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


20

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


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

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


16

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


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

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


13

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


13

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

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


11

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


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


9

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.


9

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


8

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

This link 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 handles them in SELECT ...


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.


7

From a purely relational point of view (prior to sixth normal form), I don't see any need to move a set of columns out into a separate table, just because they are frequently null. As a trivial example, consider a customer account table with an end date as one of the columns - until the customer closes their account, the end date will be NULL. You are ...


7

Perhaps like this: select foo , exists (values (null), ('A') except select foo) chk_any , not exists (values (null), ('A') intersect select foo) chk_all from ( values ('A'),('Z'),(null) ) z(foo); foo | chk_any | chk_all -----+---------+--------- A | t | f Z | t | t | t | f Note that not only the null in the "array" ...


7

Operator This is building on @Daniel's clever operator. While being at it, create the function/operator combo using polymorphic types. Then it works for any type - just like the construct. And make the function IMMUTABLE. CREATE FUNCTION is_distinct_from(anyelement, anyelement) RETURNS bool LANGUAGE sql IMMUTABLE AS 'SELECT $1 IS DISTINCT FROM $2'; ...


6

Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to Choose (and document) what it means for each nullable field Make sure it means one thing only With those rules, consider the following use case: You have a ...


6

If you're looking at it purely from just what's better from a performance perspective ... I'd just test both of your methods and see what's faster for your data. ... but as you only have three states anyway, and this is going in a where clause, it might be even faster to just check that it's not the third case, and then you don't need the NVL test: column ...


6

One thing worth keeping in mind is that when you have a field that is not required, but any values that are present must be unique will require you to store empty values as NULL. Otherwise, you'll only be able to have one tuple with an empty value in that field. There are also some differences with relational algebra and NULL values: NULL != NULL, for ...


6

Interesting questions. All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings). It's more complicated than that. Null has a number of distinct meanings and one really important reason not to allow nulls in many columns is that ...


6

DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + ' ' + QUOTENAME(name) + ' = CASE WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE ' + QUOTENAME(name) + ' END,' FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.YourTableName') AND system_type_id IN (35,99,167,175,231,239); SELECT @sql = N'UPDATE dbo.YourTableName SET ' + ...


6

No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.



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