We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [null]

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

Filter by
Sorted by
Tagged with
126
votes
8answers
49k 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 ...
94
votes
3answers
93k 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), ...
100
votes
8answers
17k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
58
votes
4answers
36k 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 ...
56
votes
5answers
39k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-...
21
votes
1answer
13k 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. ...
5
votes
3answers
5k views

Query to find GUID's “not in” null containing subquery returns no results

When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and ...
28
votes
3answers
7k 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(...) = ...
1
vote
1answer
9k views

WITH ROLLUP WHERE x IS NULL

I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just ...
2
votes
2answers
635 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
36
votes
5answers
43k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
15
votes
2answers
5k 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 ...
16
votes
5answers
72k views

Test if any columns are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
12
votes
1answer
10k views

How to index a query with `WHERE field IS NULL`?

I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting ...
5
votes
3answers
31k views

Why does COUNT() aggregate return 0 for 'NULL'?

A SELECT statement returns several rows: SELECT ColA FROM TableA WHERE ColA IS NULL I get 47 rows that have 'NULL' for ColA within TableA. ColA NULL NULL NULL etc... If I add an aggregate to ...
9
votes
2answers
5k 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 ...
6
votes
1answer
2k views

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

Due to business logic, we need a new column in a table that is critical to ensure is always populated. Therefore it should be added to the table as NOT NULL. Unlike previous questions that explain how ...
6
votes
2answers
7k 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 ...
11
votes
2answers
14k 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 ...
7
votes
1answer
4k views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return ...
3
votes
1answer
757 views

SSIS - SSAS: Missing data handling

I have a solution in SSDT with a SSIS and a SSAS project, with missing values in both numeric and text columns. Is it a good practice converting all the missing values of these columns to NULL during ...
3
votes
1answer
6k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
2
votes
3answers
819 views

Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: select coalesce(co....
82
votes
9answers
67k 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 ...
55
votes
7answers
20k views

If a person's name is Null then how would it break the database?

I was reading this article on BBC. It tells a story of a person named Jenifer Null and how she faces day to day problems while using online databases like booking plane tickets, net banking etc. I am ...
41
votes
2answers
18k 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 ...
14
votes
2answers
7k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
13
votes
3answers
1k 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 ...
7
votes
2answers
74k 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 ...
14
votes
4answers
853 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?
5
votes
4answers
12k 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) ...
10
votes
3answers
1k views

What are the consequences of not specifying NOT NULL in PostgreSQL for fields which can't be null?

I have an application (data is stored in PostgreSQL), where majority of the fields in the tables are always not null, but the schema for these tables does not enforce this. For example look at this ...
6
votes
4answers
22k 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?
5
votes
2answers
244 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
2k views

Count NULL values per row

I would like to count the number of null's present per row in a table without enumerating column names. For example: WITH t as (VALUES (NULL ,'hi',2,NULL,'null'), ('' ,'hi',2,3,'test'), (NULL ,'...
3
votes
1answer
145 views

Can an Oracle IDENTITY column be nullable?

I want to create an ID column that automatically generates unique ID numbers by default. Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box ...
7
votes
2answers
44k 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 ...
6
votes
7answers
1k 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
2answers
278 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 other ...
2
votes
0answers
39 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
2
votes
2answers
145 views

How can the concatenatation of a text and a NULL value return a non-null result?

Assert 1 The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the ...
1
vote
1answer
199 views

Count where any 3 columns have values (not null)

I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 ...
1
vote
1answer
64 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
1
vote
0answers
238 views

Allow Null Columns vs separate 1-to-many tables

Perhaps similar questions have been asked, but this is re having null columns. I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better ...
1
vote
1answer
492 views

Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

I experienced very strange problem today with TSQL function. The function has several parameters (int and bit). One of the (int) is set with value NULL. When calling a function with NULL inside the ...
0
votes
3answers
7k views

SQL left join returns null column [closed]

I am trying to do a LEFT JOIN on two tables, where one table has a datetime column, and the other table has a date column. As I am working in SQL Server, I used CONVERT to change the datetime to date. ...
0
votes
1answer
5k views

MySQL is inserting “” as 0 in decimal fields. How to stop that?

The fields are nullable, so they do allow nulls. Why would MySQL convert "" to 0 instead of NULL? Is there a way to force it to be NULL instead?