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

learn more… | top users | synonyms

3
votes
2answers
594 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 ...
3
votes
3answers
117 views

Is it better to create another table to possible null values in relational databases?

I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like: Table name: tabla ...
3
votes
1answer
2k 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 ...
3
votes
3answers
2k 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 ...
3
votes
0answers
285 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 ...
2
votes
2answers
7k 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 ...
2
votes
2answers
298 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 ...
2
votes
2answers
711 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, ...
2
votes
3answers
13k 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
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 ...
2
votes
4answers
4k 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
1k 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
1answer
816 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 ...
2
votes
3answers
18k 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
4k 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
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 ...
2
votes
1answer
90 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
94 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
599 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 ...
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 ...
2
votes
1answer
414 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
70 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
41 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
9k 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
730 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
1answer
134 views

INSERT don't work with NOT NULL but UPDATE does, why?

I have this table definition: CREATE TABLE `media` ( `id` int(11) NOT NULL AUTO_INCREMENT, `brands_id` int(11) DEFAULT NULL, `media_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL, ...
1
vote
2answers
287 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
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
1k 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 ...
1
vote
1answer
70 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
65 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
182 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
242 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
1answer
414 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 ...
1
vote
2answers
554 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
2k 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
100 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
1k 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
28 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
118 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
42 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
459 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
83 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
109 views

If I assign a NVL constraint to a nullable column will it display an error or it will retrieve the records?

I have a database in which some tables have to be joined to fetch the data. The problem is that in one table the column is nullable (nullable is set to yes or no constraint given for that column) and ...
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
789 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 ...
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
157 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 ...