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

learn more… | top users | synonyms

11
votes
4answers
935 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 ...
2
votes
3answers
8k 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
2answers
247 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 ...
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', ...
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 ...
4
votes
2answers
176 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
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
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 ...
1
vote
2answers
455 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, ...
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 ...
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. ...
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 ...
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 ...
3
votes
2answers
382 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 ...
6
votes
1answer
366 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
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, ...
1
vote
1answer
112 views

Why is replication attempting to distribute NULL values into Identity columns

We are sporadically getting this error: Explicit value must be specified for identity column in our replication environment. I have checked the transaction command that is reporting the error and it ...
5
votes
7answers
742 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 ...
22
votes
3answers
12k 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), ...
3
votes
4answers
4k 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
1answer
631 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 ...
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
3k views

How can I help SQL Server recognize my 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 ...
3
votes
0answers
269 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 ...
44
votes
6answers
8k 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 ...
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 ...
11
votes
4answers
742 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?
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?
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 ...
1
vote
2answers
817 views

null check and string concatenation

Can I do better than this query? select func_returns_str('key1') || nvl2( func_returns_str('key2'), ',' || func_returns_str('key2'), null) from dual; I ...
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 ...
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?
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 ...
0
votes
2answers
650 views

Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0

Think of x as a column containing null, 0, 1. Only less than 1 % of the rows contain 1. There is no logical difference between 0 and Null. Is it a good idea to insist, that isnull rather than NVL is ...
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 ...
46
votes
7answers
12k 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 ...