Questions tagged [null]

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

Filter by
Sorted by
Tagged with
1
vote
3answers
60 views

NULL as a @variable within WHERE clasue

I would like to SET 'IS NULL' as a variable and use it in a WHERE clause. I will be unioning many tables together that carry the same WHERE clause and would like flexibility to use a variable for my ...
-2
votes
1answer
24 views

Working with NULL Date Fields

I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm ...
0
votes
1answer
52 views

Column values in SQL Server changes to null [closed]

There is one specific column whose value automatically changes to null after data is inserted into it. I have tried to update the column from designer, update the column with query, delete the ...
1
vote
1answer
27 views

Null check condition in Where clause

DECLARE @SubDesc1 nvarchar(100) SET @SubDesc1 = null I would like to do something like that. Query 1 SELECT * From Table1 where Category = 'Category1' AND [Description] ='ABC' AND ...
0
votes
2answers
22 views

Work around average behaviour with nulls

I have two tables: Product (PK product_id, brand, model) Order (PK invoice_id, customer_name, customer_email, FK product_id) I am trying to find the product(s) whose number of times that product is ...
3
votes
1answer
23 views

Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Using PostgreSQL 11, I have the following table with around 450 million rows: postgres=> \d+ sales Table "public.sales" ...
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
votes
1answer
58 views

Can't get this query to work properly

I've been trying to get the following query working for days but I can't seem to solve my issue. I'm using the MySQL version 5.7.9 I have the following database: CREATE TABLE user ( id INT NOT ...
7
votes
1answer
2k views

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

Due to business logic, we need a new column in a table that is critical to ensure is always populated. Therefore it should be added to the table as NOT NULL. Unlike previous questions that explain how ...
100
votes
8answers
17k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
1
vote
2answers
62 views

How to filter hstore where values are not null in a trigger?

The easiest way to explain the question is in the usage of an audit trigger. However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). ...
2
votes
2answers
36 views

NULL rows and IS NULL

I've been writing some queries that look like this: SELECT ... FROM table_a a LEFT JOIN table_b b ON b.id = a.id LEFT JOIN table_c c ON c.id = a.id In these queries only one of the joins can ...
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
votes
1answer
19 views

null value in postgres functions

I am new in postgres and i found this issue with this simple function CREATE OR REPLACE FUNCTION "public"."TEST"("disponibile" int2=0, "extra" int2=0, "ordini" int2=0, "prenotata" int2=0) RETURNS "...
13
votes
6answers
10k views

Why can't I use null values in joins?

I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a ...
2
votes
0answers
40 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
2
votes
1answer
163 views

Conditional ORDER BY based on NULL values in the column

I have a table like this. table(student_name nvarchar(20) not NULL, grades int). When I write select * from table order by grades asc It shows this student_name | grades --------------------- ...
0
votes
1answer
107 views

Using NULL values for foreign key with normalization

I am creating a table as follows: Building (PK: buildingID) Area (PK: areaID, FK: buildingID) Room (PK: roomID, FK: buildingID, FK: areaID) All rooms are in a building. Some rooms can be in an area ...
7
votes
4answers
9k views

In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 ...
4
votes
2answers
2k views

Count NULL values per row

I would like to count the number of null's present per row in a table without enumerating column names. For example: WITH t as (VALUES (NULL ,'hi',2,NULL,'null'), ('' ,'hi',2,3,'test'), (NULL ,'...
1
vote
3answers
62 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
5
votes
2answers
247 views

Is there some drawback to define SPARSE columns?

I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?
3
votes
2answers
114 views

How to understand “NULL NOT IN” when the set is empty?

The SQL in this question is for Oracle. But you can simply remove FROM dual to make it work on SQL Server (which returns the same result as Oracle). I have the following SQL: SELECT 1 FROM dual ...
6
votes
2answers
494 views

NULL value causes blank row in SELECT results for text concatenation

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || ...
7
votes
1answer
306 views

Multiple BIT datatype columns in the same table

Consider the following information coming from Microsoft SQL docs pages: The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the ...
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
44 views

Why null is getting compared with null in the join condition in SQL Server 2016?

It's a simple self join I am trying to do on below data and the O/P I am getting after running following query (This is not the complete output, just showing the null part of it) select a.col1, a....
12
votes
3answers
562 views

Increase speed altering column on large table to NON NULL

I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
0
votes
2answers
70 views

MySQL 8.0 cannot insert NULL values to date field

I'm trying to insert a data from file to MYSQL 8.0 using the InnoDB engine. I'm using this query: LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat' INTO TABLE Item FIELDS TERMINATED BY '|'; ...
0
votes
1answer
43 views

Comparing nullable column values using “IS NULL” versus “= NULL”

I'm dealing with nullable values in a column of a table, i'm wondering why this: with cte as ( select 'c1'::text as c1,null::text as c2 union select 'c2'::text as c1,null::text as c2 ) ...
96
votes
3answers
95k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
0
votes
1answer
247 views

MsMerge_genhistory has alot of rows with pubid = null

I have a merge replication and I am worried that the cleanup of metadata might not be enough. I have a retention period of 60 days and I can see thet the metadatacleanup-job do remove rows i ...
0
votes
3answers
71 views

MySQL, IF statement, variables, and NULL

After struggling for too long with something that should have been simple, I had a question to ask, but while writing it, I kept looking for solutions and I finally found the solution. I am posting it ...
1
vote
1answer
428 views

LEFT JOIN and handling NULL Values [closed]

I have two tables. Table 1: A B ------ 1 'A' 2 'B' 3 'C' Table 2: A C ---------- 2 15 3 20 I'm using the following query: SELECT table1.A, table1.B, table2.C FROM table1 ...
-1
votes
1answer
49 views

Replace NULLs with value

Here is my temporary table: CREATE TABLE #test123( c1 float, c2 float, c3 float, c4 float, c5 float ); INSERT #test123(c1,c2,c3,c4,c5) VALUES (2,3,NULL,1,2), (2,NULL,NULL,...
1
vote
1answer
219 views

Count where any 3 columns have values (not null)

I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 ...
12
votes
3answers
2k views

How to get the last not-null value in an ordered column of a huge table?

I have the following input: id | value ----+------- 1 | 136 2 | NULL 3 | 650 4 | NULL 5 | NULL 6 | NULL 7 | 954 8 | NULL 9 | 104 10 | NULL I expect the following ...
13
votes
2answers
8k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
2
votes
2answers
180 views

How can the concatenatation of a text and a NULL value return a non-null result?

Assert 1 The concatenation operator || can concatenate any string type values, returning text. In Postgres, every type has a text representation and can be cast to text. Consequently, quoting the ...
0
votes
1answer
57 views

Display all fields with null values in a particular row in SQL Server

I have a requirement where I want to fetch and display all the fields with NULL value for a particular row in the table. Some thing like: select 'all columns with NULL' from table_xyz where ...
0
votes
0answers
38 views

Pagination using seek-method with nullable columns, sharing a composite index with ORDER BY

I have the following table: CREATE TABLE users( id text PRIMARY KEY, -- actually a uuid organization_id text NOT NULL, lname text NOT NULL, fname text NOT NULL -- other irrelevant fields ); ...
9
votes
1answer
1k views

Why do I need to cast NULL to column type?

I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this: (Both the active and core fields are of type boolean) UPDATE fields as t set "active" =...
0
votes
3answers
18 views

MYSQL: Display NULL value when foreing key is NULL

I have the following database structure in MYSQL: CREATE TABLE xs_bugtracking ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `product` VARCHAR(64) NOT ...
2
votes
2answers
644 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
2
votes
1answer
421 views

PostgreSQL performance with (col = value or col is NULL)

This question deals with PostgreSQL 9.5 query performance. The table is: CREATE TABLE big_table ( id integer NOT NULL, flag bigint NOT NULL, time timestamp with timezone NOT NULL, val ...
0
votes
1answer
232 views

WHEN MATCHED clause fails if there's a NULL in the ON columns [duplicate]

I'm using a query with merge that works fine if all columns in the ON clause have a value but WHEN MATCHED omits if there's a NULL mark in one of the columns: MERGE PEPS AS peps USING (Lots of Cols.. ...
16
votes
5answers
76k views

Test if any columns are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
3
votes
3answers
5k views

Best way to work around NOT IN NULL issue?

There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?) I have 6 ...
1
vote
0answers
38 views

Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] ...
0
votes
1answer
46 views

Are null values allowed in any column of a table in SQL?

I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?