Questions tagged [null]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
119 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
57 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 ...
13
votes
4answers
39k 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
935 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
56 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
516 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
37 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
809 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 script ...
8
votes
1answer
7k 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
626 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 ...
4
votes
3answers
1k 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
47 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
1k 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's ...
1
vote
1answer
1k 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
1k 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
3k 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
212 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
2answers
8k 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
3k 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 aggregate ...
0
votes
4answers
32k views

ISNULL() OR Is NULL in UPDATE statement

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

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
4
votes
2answers
2k views

Adding hstore entries to an uninitialized (NULL) column

I got bitten by this "feature" recently. If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error. Is this expected? create table ...
1
vote
1answer
5k views

check numeric column for empty cells

How can I check for a numeric column for empty values I want something like this: select * from table where column_numeric = '' This does not works obviously, but what can be the equivalent of this ...
1
vote
0answers
299 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 ...
4
votes
2answers
2k views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
0
votes
1answer
90 views

multi-column FKs with NULL values [duplicate]

I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
0
votes
1answer
45 views

Dealing with co-dependent not null columns

Say I have a table entities (subclassed by people and organizations tables), where each entry requires a primary_email_id. The entities table looks like this: entities ======== id INT AUTO_INCREMENT ...
13
votes
1answer
10k views

How to index a query with `WHERE field IS NULL`?

I 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, setting ...
36
votes
5answers
43k views

Why does a UNIQUE constraint allow only one NULL?

Technically, NULL = NULL is False, by that logic no NULL is equal to any NULL and all NULLs are distinct. Shouldnt this imply that all NULLs are unique and a unique index should allow any number of ...
1
vote
1answer
2k 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
1answer
178 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
131 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 ...
0
votes
0answers
1k views

Oracle 12c, exclude rows wher two columns are both null

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit I am trying to select rows with a non-null value in one of two columns. Data is being selected from a view (MY_VIEW) with both ...
8
votes
2answers
45k 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 ...
1
vote
0answers
46 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 ...
2
votes
4answers
17k 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 OR ...
-2
votes
1answer
2k 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
3k views

MySQL server parameters default empty string to null [closed]

How can I set MySQL server parameters to default empty strings to null on insert? is this possible?
3
votes
1answer
4k 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
41 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 , count(*...
1
vote
1answer
273 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 ...
9
votes
3answers
36k 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 | ------------------...
2
votes
1answer
726 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 ...
5
votes
2answers
11k 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 null....
1
vote
1answer
534 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
31k 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
3k 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 ...