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

learn more… | top users | synonyms

2
votes
4answers
5k views

Null Or Multiple Parameters When Querying

I have a stored procedure that has a parameter that must take a comma delimited varchar list of values that can be turned into a table of values or can be NULL, such as @Values VARCHAR(10) = NULL ...
-2
votes
1answer
310 views

Inserting null value without quotes postgresql

I'm creating a php application which relies on postgresql. Now i have this query: $query= 'INSERT INTO "users" ("name", "username", "email", "phone") VALUES (\''. $name .'\', \''. $username .'\', ...
0
votes
1answer
1k views

Return a default value in group by if selected column is null

I am trying to run a group by statement which works fine however, one of the "group" is null. How do I return "Unknown" for that row? For example: 10000 A 9999 B 9990 C 4 etc. First ...
1
vote
1answer
35 views

cant order NULL value properly (mysql)

So I got this view in mysql : CREATE VIEW vote_question_totals AS SELECT question_id , SUM((vote_question_type)='1') question_total_plus , SUM((vote_question_type)='-1') question_total_minus , ...
1
vote
1answer
191 views

How can I insert records 'into null' in Oracle?

I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL ...
2
votes
3answers
5k views

Count null and not null values in a column

How to count and retrieve null and not null on same column in MySQL? mytable --------------------------------------------------- id | name | visited | registDate | ...
1
vote
1answer
269 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
3
votes
2answers
3k 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 ...
1
vote
1answer
442 views

TSQL change column constraint between null and not null

I am doing a exercise to change a column's constraint between null to not null. Table name: Production.categoriesTest. Please review my question and give me suggestion on what I did wrong and how to ...
6
votes
1answer
19k 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
1answer
704 views

Database Normalization: A Case Of The NULLs [duplicate]

I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL ...
0
votes
1answer
470 views

sql-server optimize query with lots of null checks

I have a query such as: select something from somewhere join stuff on condition join more_stuff on condition where somewhere.sometime is null and somewhere.someone is null and stuff.whatever is null ...
2
votes
1answer
798 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 ...
11
votes
3answers
676 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 ...
0
votes
1answer
850 views

MySQL warnings while loading integer data into table (SOLVED)

For some reason, after loading a large amount of data into the following table (from a single file), all of the values register as 0. CREATE TABLE secondTable ( freq int NULL, w1 int NULL, w2 int ...
0
votes
0answers
61 views

MySQL - query join returning zero recs from table with data

I've provided a file that has the table structure and some sample data that I'm working with. I don't understand why I'm getting nulls from a joined table, where there is data. Here's my query ...
1
vote
3answers
10k 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 ...
0
votes
2answers
278 views

Accept null value on parent column in multipart relationship

EDIT: I've come up with one poor solution (below) that requires some denormalization, but does avoid the risks of UDFs in CHECK constraints. I would appreciate your help if you have a better answer. ...
1
vote
0answers
168 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 ...
1
vote
0answers
133 views

Dealing with null values in non-nullable columns in a NoSQL database

If I have an existing table in a NoSQL database cluster and I want to add a new column that should never return null, how do I handle it in my application code? I can update all exist records and ...
1
vote
1answer
1k views

How to update where subquery value is null in SQL Server

I am attempting to update a column if another value from a subquery is less than a certain value or null: UPDATE Customer SET PriceClassID = 'A' FROM (SELECT custid, SUM(curybaldue) as last_sum FROM ...
0
votes
1answer
2k views

How to show NULL results from query?

i'm trying to create a procedure with filters for search. If the parameter is "NULL" the value of parameter will be column value; Here my problem: When my parameter and column value is NULL. I have ...
0
votes
1answer
249 views

Geography union via cursor returning null

I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is ...
4
votes
3answers
15k 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 * ...
5
votes
3answers
9k 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 ...
3
votes
3answers
3k 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 ...
2
votes
1answer
2k 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 ...
-1
votes
1answer
1k views

Totals Column - Blank if Zero or NULL

A report currently computes a "totals" column like so: ,CASE WHEN ISNULL(CLM_BREAKFAST_TYPEA.MealsA, 0) + ISNULL(CLM_BREAKFAST_TYPEB.MealsB, 0) = 0 THEN '' ...
1
vote
4answers
1k 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, ...
1
vote
3answers
2k views

Two nullable columns one required to have value

No-Explanation Question: Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have ...
6
votes
4answers
38k 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
1answer
894 views

Creating a partial unique constraint for MySQL

I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL. My ...
1
vote
3answers
4k views

Easier way to handle so many isnull() situation

Is there any good way to avoid writing so many times isnull() function inside sproc ? I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
0
votes
1answer
6k 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 ...
1
vote
1answer
2k views

Conditional Where Statement Involving a NULL value?

Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how ...
4
votes
1answer
2k 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 ...
0
votes
1answer
688 views

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition. However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the ...
1
vote
2answers
611 views

Expand sparse table with self outer join on distinct values

I have this: g1 | g2 | x ---------+-----------+----- New York | Monday | 210 New York | Tuesday | 258 Chicago | Monday | 30 Chicago | Wednesday | 25 LA | Thursday | 40 ...
0
votes
2answers
217 views

oracle null value in quotes - yet another newbie question

I have a where clause like "and uc.completion_status in ('[P....]') " here ('[P....]') is the user input from taken a dropdown list in the user interface. and dropdown list consists three options ...
2
votes
2answers
3k 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 ...
8
votes
2answers
12k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
3
votes
1answer
1k 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 ...
42
votes
5answers
30k 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 ...
5
votes
2answers
46k 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
3answers
3k 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 ...
2
votes
1answer
2k views

Query performance of a NULL vs an empty ('') varchar in SQL Server 2012

I have 3 indexed fields in a query: int, int, and varchar(250). The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty ...
0
votes
1answer
2k views

Find ID columns with null value in oracle database

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database Any suggestion?
2
votes
1answer
427 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 ...
19
votes
3answers
5k 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
1k views

How to change the display of an empty string in mysql command line tool?

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the ...