Tag Info

Hot answers tagged

103

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


55

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


40

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


29

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


25

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


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


18

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


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

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

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


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

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


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

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


12

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


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.


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

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


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.


7

Oracle: The null literal does not have a type, but null can be cast to any type, and this may be necessary when calling overloaded procedures or functions controlling the return type of the decode function, eg: select decode('A','B',to_char(null),'A','1') from dual; DECODE('A','B',TO_CHAR(NULL),'A','1') ------------------------------------- 1 select ...


7

You should let your schema design and application requirements guide this decision. The performance differences are probably not noticeable either way in most cases.


7

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


7

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases: create table foo(bar integer not null); select sum(bar) from foo -- ...


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

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


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



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