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

learn more… | top users | synonyms

1
vote
1answer
17 views

Alter TEXT column to NOT NULL when there is existing NULL rows

Mysql does not support default values for TEXT columns. I have a live database with NULL values What is the proper way to change all null values to a predefined value and change the column to not ...
0
votes
2answers
34 views

General Database Optimizations - correct data types - why to avoid NULL? [closed]

Oracle recommends: Choose the correct data types and size to: – Avoid NULLs – Improve performance – Protect data – Use data compression where appropriate I don't understand why I need to avoid ...
0
votes
3answers
46 views

What data type for a column which can have three states: yes, no, unknown

I have an application where the user can decide between: yes no unknown I started to use a boolean column which is nullable. But things get quite complicated since my framework (django) does not ...
2
votes
1answer
38 views

Using GROUP BY WITH ROLLUP on column that contains NULL

I have the following table (see it on SQL Fiddle) (I created to break down my problem): | ID | word | ---------------- | 5 | "Hello" | | 6 | NULL | | 7 | "World" | | 8 | "World" | Now I ...
-1
votes
0answers
27 views

View NULLABLE Column Issue

I have a View that contains a column from a base table. The column is set to NOT NULL in the base table. However, in the view it is set as NULL. Even if I mouse over the CREATE VIEW script it shows ...
0
votes
0answers
44 views

Simplifying this NULL check [duplicate]

Is there a way to simplify this check? I would like to compare if two columns are equal and that includes if one is NULL and the other has a value (which would mean they're considered different). (A.[...
0
votes
1answer
18 views

Where clause returns null, but need to return 0

Need an SQL Statement for the following: Table a partID partNumber Table b partID purchased SELECT a.partNumber FROM a WHERE (SELECT SUM(b.purchased) WHERE b.partID=a.partID) > 0 If ...
2
votes
5answers
131 views

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

I have an Access table called Engagement with four fields: Emp_id, Year, Week, Act_id It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the ...
-3
votes
2answers
77 views

Case statement by using IsNull and IsNotNull [closed]

ALTER FUNCTION [dbo].[addC]() RETURNS varchar(255) AS BEGIN DECLARE @desc varchar(255); DECLARE @desc1 varchar(255); SET @desc = '' SET @desc1 = '' Declare c1 Cursor for ...
2
votes
3answers
86 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....
1
vote
1answer
49 views

Distinguish between unknown and non-present foreign keys

I'm in the situation of importing data from multiple other applications. The data is stored in multiple tables that contain foreign keys. Some of these applications don't provide all the needed data ...
7
votes
2answers
147 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 ...
4
votes
2answers
338 views

How do I order by NULLS LAST if my database doesn't support it?

Select FullName, Login FROM [User] ORDER BY FullName desc, login asc Results FullName Login ----------------------------- ------------------------------ M...... ...
3
votes
2answers
80 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
0
votes
2answers
37 views

Compare possible null values in one query

Question is pretty short and simple: I have following query: SELECT * FROM CTE Now I want to get all rows with only maximum value of some field. SELECT * FROM CTE WHERE NodeId = (SELECT MAX(...
1
vote
2answers
39 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
5
votes
2answers
117 views

Need help with null field values

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the ...
1
vote
3answers
185 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
52
votes
7answers
14k 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 ...
6
votes
1answer
456 views

SQL Server Left Join strange NULL check

I've just been going through some old stored procedures, doing some optimizations and stumbled across this oddity: SELECT ISNULL(bt.BusinessTypeID, 0) AS BusinessTypeID FROM BusinessType bt LEFT JOIN ...
0
votes
2answers
73 views

How to omit multiple columns where is not null?

I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended. I suspect my error is somewhere ...
0
votes
0answers
57 views

Comparisons with NULL values and not using ISNULL function

Hello and thank you in advance. My question is that with ansi-nulls on, how can I do a proper comparison between two fields without using an isnull function (or coalesce)? For example the following ...
4
votes
5answers
984 views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
3
votes
1answer
99 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 ...
1
vote
1answer
189 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, `...
7
votes
2answers
1k views

Why is a NOT NULL computed column considered nullable in a view?

I have a table: CREATE TABLE [dbo].[Realty]( [Id] [int] IDENTITY(1,1) NOT NULL, [RankingBonus] [int] NOT NULL, [Ranking] AS ([Id]+[RankingBonus]) PERSISTED NOT NULL .... ) And a ...
2
votes
1answer
233 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
72 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 ( ...
1
vote
1answer
30 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 ...
5
votes
4answers
3k views

How to exclude NULL values inside CONCAT MySQL?

If I have this - tadd is the Address table: CONCAT(tadd.street_number, ' ', tadd.street_name,', ', tadd.apt_number,', ', tadd.city,', ', tadd....
3
votes
1answer
135 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 |...
2
votes
0answers
42 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
1answer
98 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
0answers
29 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 ...
0
votes
2answers
115 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 ...
6
votes
1answer
852 views

MySQL loading NULLs in numeric columns

MySQL 5.6.23, InnoDB I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to ...
1
vote
1answer
217 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 ...
3
votes
3answers
188 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 ...
1
vote
1answer
43 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 ...
0
votes
1answer
109 views

How to prevent #Error values derived from Nulls in an outer join

Suppose I have two tables to outer join Tab1: ID Nam 1 Joe 2 Moe 3 Flo Tab2: ID Val y w 1 stuff 66 33 2 duff 67 44 and one of the resulting columns is derived -- i.e., it'...
1
vote
0answers
24 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 ...
0
votes
1answer
216 views

Filling not applicable fields with N/A rather than leaving them empty or Null [duplicate]

I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several ...
1
vote
2answers
369 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
1answer
759 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
93 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?
0
votes
2answers
3k views

mysql UNIQUE KEY multiple columns with one column as NULL

I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created. In following schema and I would finally want that family_relation should have ...
0
votes
1answer
531 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
4answers
4k views

ISNULL() OR Is NULL in UPDATE statement

I have to run an UPDATE statement on live online database with about 5 lac records. I want to know which statement will run faster: Update Table set REC_ID = isnull(REC_ID,'') Update Table set ...
2
votes
1answer
107 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 ...
0
votes
1answer
1k 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?