2
votes
2answers
235 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 ...
0
votes
0answers
16 views

Avoid obvious potential NULL-values [duplicate]

I've been searching through the different threads discussing NULL values and how it is good whether using it or not. I cant find any concrete examples of how to design a relational data model to avoid ...
1
vote
2answers
180 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 ...
2
votes
1answer
2k 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 ...
0
votes
1answer
222 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 ...
10
votes
3answers
412 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 ...
1
vote
3answers
1k 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
0answers
55 views

How to merge rows based on how much data they have?

I am cleaning data and I formed a table something like this: file | product | color | product_id --------------------------------------------- one.csv pipe blue null ...
2
votes
1answer
455 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 ...
1
vote
2answers
924 views

Only one NULL for one part of primary key

I have two date columns : "start date" and "finish date". I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one ...
15
votes
3answers
2k 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(...) = ...
4
votes
2answers
173 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 ...
10
votes
1answer
972 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
1answer
337 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 ...
5
votes
7answers
727 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 ...
17
votes
3answers
10k 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), constraint ...
3
votes
0answers
268 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 ...
1
vote
2answers
739 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
1k 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 ...
41
votes
4answers
9k 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 ...