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

learn more… | top users | synonyms

2
votes
3answers
10k 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 * ...
2
votes
2answers
2k 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 ...
2
votes
1answer
624 views

How to index a query `WHERE field IS NULL` (postgres)

So I basically 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, ...
2
votes
3answers
14k 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
3k 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
32 views

Unique Index/Constraint with multiple columns, one column is nullable

My questions is whether it's possible to have unique index comprised of multiple columns where one of the columns may contain NULL. Example: A table named 'regulated_person' has a number of columns ...
2
votes
1answer
79 views

Efficient manual cleanup of foreign keys in MySQL [closed]

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added ...
2
votes
1answer
1k 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
373 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 ...
2
votes
1answer
63 views

Why does coalescing make this query quicker?

I have the following query SELECT SQL_NO_CACHE `table1`.* FROM `table1` LEFT JOIN `table2` ON table2.id = table1.table2_id WHERE (`table2`.`date_assigned` >= '2015-06-21') AND ( ...
2
votes
0answers
39 views

Better performance to have nullable columns or an extra table? [duplicate]

There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
1
vote
3answers
3k 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 ...
1
vote
3answers
6k 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
1answer
619 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
2answers
118 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
1
vote
4answers
991 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
1answer
42 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 ...
1
vote
1answer
46 views

Pivoting with NULL Dates

I'm attempting the pivot below: SELECT EmpID as ID, [1] as January, [2] as February, [3] as March, [4] as April, [5] as May, [6] as June, [7] ...
1
vote
1answer
158 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 ...
1
vote
1answer
198 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 ...
1
vote
2answers
479 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 ...
1
vote
2answers
1k 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 ...
1
vote
1answer
87 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
1
vote
1answer
625 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
1answer
955 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 ...
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 ...
1
vote
1answer
21 views

Normalize End Date - Nulls or separate table

I was hoping for some advice on best practice in schema design - as far as End Date values are concerned. In this case it is for a Suppliers table - we are required to store information on the Start ...
1
vote
1answer
32 views

always error “cannot insert value NULL” sqlserver

I want to update table "tmpiscunitmap.tr_kpi_id" year_period=2016 with value from table "tmp_kpi.tr_kpi_id where year_period=2016" with condition tmpiscunitmap.tr_kpi_id = tmp_kpi.tr_kpi_id on ...
1
vote
1answer
39 views

generate new column from another table column, treating nulls are unique

I have a table that looks like the following: |device_id | login_id | ----------------------- | 1 | NULL | | 2 | A | | 3 | A | | 4 ...
1
vote
1answer
232 views

LEFT JOIN DEFAULT NULL VALUE

My query can be simplified as this: SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id Which b is from group concat select (very dynamic column). Sometimes there are 3 columns in b, sometimes ...
1
vote
1answer
68 views

Null in Varchar datatype

I am using a SQL Server database. If NULL value is inserted to a VARCHAR datatype column, what will be the memory size used for the column?
1
vote
1answer
34 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
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 ...
1
vote
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 ...
1
vote
1answer
143 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 ...
1
vote
0answers
23 views

How to best distinguish between “types” of missing data?

I am working on large tables (> 100 fields each) currently organized in flat-files in csv-format. An Oracle DB is to be created to store all the data and allow for several more or less intricate ...
1
vote
0answers
20 views

Null Dimensions in SSAS [closed]

Several years ago 2008, I create a data warehouse using Business Objects. Business Objects did not process Outer Joins well and used SQL for all of its queries. Therefore, for every dimension I ...
1
vote
0answers
93 views

How to replace empty, multi-column result with single 'none'

If it is possible to make query in Oracle which will return single column/row with value "none" instead of empty, multi-column result? Otherwise return proper amount of columns with result. This is ...
1
vote
1answer
424 views

Populate one table with data from another one on selected cells

I have a problem with filling empty cells in one table with data from another one. I got a table with user emails - some of the fields are empty, some are already containing emails. I gathered the ...
1
vote
0answers
23 views

How do I include nulls during comparisons in SQLServer? [duplicate]

So basically in SQLServer 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 but ...
1
vote
1answer
390 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 ...
1
vote
0answers
116 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
94 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 ...
0
votes
1answer
135 views

SQL Server's aggregate functions ignore nulls. How do I correct this?

According to MS's knowledgebase, Except for COUNT, aggregate functions ignore null values. Does anyone know how I can get it to work correctly, that is, return a null if it attempts to ...
0
votes
2answers
763 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 ...
0
votes
1answer
331 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 ...
0
votes
3answers
984 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 ...
0
votes
2answers
170 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 ...
0
votes
2answers
32 views

How to get the second string in the ORA-02296 error to show something meaningful

Whenever I run the following script*: ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL; I receive the following error: ORA-02296: cannot enable (ANIMAL.) - null values found I have a ...
0
votes
1answer
356 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 ...