A database concept used to represent missing, unknown or inapplicable data.

learn more… | top users | synonyms

46
votes
7answers
13k views

When to use NULL and when to use an empty string?

I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general: Is there a logical scenario in which it would be useful to distinguish an empty string from NULL? What ...
45
votes
6answers
9k views

Why shouldn't we allow NULLs?

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though. All I can seem to ...
41
votes
4answers
10k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
30
votes
2answers
7k views

NULL or NOT NULL by default?

In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter? I know in some DBMSs they ...
22
votes
3answers
13k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
17
votes
3answers
3k views

Why does ANSI SQL define SUM(no rows) as NULL?

The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets: COUNT(...) = 0 AVG(...) = NULL MIN(...) = NULL MAX(...) = ...
11
votes
4answers
1k views

Reason to not use nullable number in Oracle?

Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel ...
11
votes
4answers
745 views

Does NULL have a type?

Various sources (eg Wikipedia, PSOUG) state that Oracle's null does not have a type. Is this true? What about other RDBMSs?
11
votes
1answer
1k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...
10
votes
3answers
434 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
9
votes
2answers
1k views

What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL. What is the purpose ...
7
votes
1answer
391 views

Adding SPARSE made table much bigger

I have a generic log table, about 5m rows. There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...
7
votes
4answers
3k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
7
votes
1answer
7k views

MySQL column defined with NOT NULL is allowing nulls

I had created a table a while back and started adding data to it. Recently I added a new column (address) to it with NOT NULL as part of the new column. The old rows (pre-addition) are still null, ...
6
votes
2answers
2k views

Comparing Columns that can contain NULLS - is there a more elegant way?

I know that you can't compare a value to NULL and expect a result without adding something like in the following code... SELECT * FROM A INNER JOIN B ON A.ID = B.ID WHERE A.STRING ...
6
votes
2answers
3k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
5
votes
7answers
758 views

Database table and NULLs

I know that there have been many opinions/sides concerning NULL values in a database. I have not understood though what is the best practice for this. I.e. if I have a relational table with an ...
4
votes
4answers
4k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
4
votes
2answers
74 views

Deleting NULL results

Currently I'm just trying to delete objects with the Name column being Null from a temp table with the following... delete from #PulledData where #PulledData.Name0 = NULL I've also tried = 'NULL', ...
4
votes
3answers
4k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
4
votes
2answers
179 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
4
votes
1answer
208 views

Is there some drawback to define SPARSE columns?

I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?
4
votes
2answers
4k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
3
votes
4answers
5k views

In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 ...
3
votes
3answers
2k views

Best way to work around NOT IN NULL issue?

There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?) I have 6 ...
3
votes
2answers
520 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
3
votes
2answers
18k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
3
votes
2answers
2k views

PostgreSQL: count datasets / lines containing NULL values

I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved. For example: CREATE TABLE tab1 ( id INTEGER ...
3
votes
1answer
4k views

Oracle: How to create a not null column in a view

Given a table like: CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL ); I want to create a view like: CREATE VIEW "MyView" AS SELECT CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn" FROM ...
3
votes
2answers
401 views

Remove nulls from TSQL connection string

I have a bunch of queries, against a couple different databases, that I use to generate delimited reports. I'm trying to create some new reports against a MSSQL database and I'm having trouble ...
3
votes
1answer
646 views

PostgreSQL pl/perl trigger, differentiate null vs empty

I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the ...
3
votes
1answer
617 views

A function to check if a column allows NULL

Is there a way to write an insert/update query that checks if a column allows NULLs: If it does set the column to NULL, and '' (empty string) otherwise? I would be something like: UPDATE mytable ...
3
votes
3answers
1k views

Empty LONGTEXT or NULL?

In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use ...
3
votes
0answers
271 views

To NULL or not to NULL? [duplicate]

Possible Duplicate: Why shouldn't we allow NULLs? I've heard that it's a bad idea to do database design such that nullable fields exist. However, eliminating nullables often cause much ...
2
votes
1answer
5k views

“Cannot insert the value NULL”, but there is no NULL values! [closed]

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table The Person.LastName column has a NOT NULL constraint. When I execute this code: CREATE ...
2
votes
2answers
321 views

How do I include nulls during comparisons in SQL Server?

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL ...
2
votes
3answers
10k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
2
votes
2answers
250 views

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures. I have the following tables setup. I normalized the tables to avoid using ...
2
votes
1answer
575 views

Update “NULL” string to Actual NULL value

I have a table that contains NULL values but the problem is that some of the values are actually string "NULL" and not actual NULLS so when you trying something like where date is null it will not ...
2
votes
3answers
9k views

Questions about handling NULLs and empty strings in nvarchar and numeric fields

I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I ...
2
votes
1answer
2k views

Dealing with NULL values and EMPTY strings in UNION of two tables

I want return records from two database tables when a record differs in at least one of the fields. I have written a query for it using the UNION operator. It is working absolutely fine. However, I ...
2
votes
1answer
1k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
2
votes
1answer
2k views

MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database. The bash command looks something like this: mysql -e ...
2
votes
1answer
677 views

Is there a way to explicitely have a not-null column in a view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table: create table tq84_t ( col_1 number, col_2 varchar2(15) not null ); create view ...
2
votes
1answer
301 views

Null Values and Performance

I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.) But still there are times when saving ...
1
vote
2answers
479 views

MYSQL only Show columns which allow null

I need to be able to pull the column names which allow a NULL value, I know that show columns from TABLE Will give show me the table properties and whether or not the column allows null values, ...
1
vote
3answers
2k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
1
vote
3answers
4k views

Oracle empty string converts to null

When doing an insert, an empty string is converted to null: insert into test (f) values (''); Now, there is a row with f containing a null. But, when I query the table, I cannot use '': select * ...
1
vote
1answer
367 views

How different clauses treat Null/Unknow in SQL Server 2012?

(relatively new to SQL and SQL Server) I am reading SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan. Autor states that: SQL has different treatments for UNKNOWN in different language ...
1
vote
4answers
622 views

MySql Order by isnull() Performance Problem

my sql below is use for listing stock added from 10 days ago. Order by isnull(Price) is use so that stock without any price yet will still being listed. AddDate and Price has an index. SELECT Id, ...