Questions tagged [null]
A database concept used to represent missing, unknown or inapplicable data.
276
questions
0
votes
1answer
20 views
Column values in SQL Server changes to null
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
25 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
17 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
21 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
votes
1answer
57 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 ...
1
vote
2answers
57 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 ...
-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 "...
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,...
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 ...
6
votes
2answers
490 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 || ', ''' || ...
3
votes
2answers
112 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
...
7
votes
1answer
296 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 ...
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
561 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
67 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
)
...
1
vote
1answer
390 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
...
0
votes
3answers
68 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
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,...
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 ...
asked Mar 31 at 17:19
peterh says reinstate Monica
1,4745 gold badges17 silver badges34 bronze badges
2
votes
2answers
170 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 ...
2
votes
1answer
158 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
0answers
37 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
1answer
382 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
227 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..
...
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] ...
1
vote
0answers
251 views
Allow Null Columns vs separate 1-to-many tables
Perhaps similar questions have been asked, but this is re having null columns.
I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better ...
3
votes
1answer
249 views
Is there a function which the opposite of NULLIF?
I am aware of the NULLIF function which compares two values and returns NULL if they match.
Is there a function which will compare two values and return NULL if they don’t match? That is filter out ...
1
vote
2answers
79 views
Replace Null values with values from the same column by matching values in a different column
I have a table with some null values in column "date":
platform date id
---------------------------
web 2018-10-10 1
mob 1
mob 1
web ...
-2
votes
2answers
85 views
How to display null records/rows from table
For ex: I have a record with name = "sga_target", and no record exists where name = "_exafusion_enabled". I still want to display a row with "_exafusion_enabled" as name and NULL as value, without ...
0
votes
1answer
212 views
MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls
I have the following table definition:
CREATE TABLE `invite` (
`id` varchar(255) CHARACTER SET utf8 NOT NULL,
`email` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `...
1
vote
1answer
65 views
Why doesn't jsonb constructor from a NULL record behave like hstore constructor?
When I construct an hstore
CREATE EXTENSION hstore;
SELECT hstore(null::my_type);
hstore
------------------------------------------------
"name"=>NULL, "...
0
votes
1answer
82 views
IS NOT DISTINCT FROM vs row-wise equality with =
Looking at the below query,
SELECT null IS NOT DISTINCT FROM null AS indf,
null = null AS eq;
indf | eq
------+----
t |
From this we can see the result of the IS NOT DISTINCT FROM is true, ...
0
votes
1answer
104 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 ...
1
vote
3answers
281 views
Returning 0 if value is NULL
SELECT
p.id
, ig.NumReceived
, og.NumShipped
, coalesce(
(p.StartingInventory - og.NumShipped + ig.NumReceived),
p.StartingInventory
) as OnHand
, p.ProductName
, p....
0
votes
3answers
230 views
Difference of two columns with possible NULL cells
I need to sort a table by the difference of two columns as
SELECT (first - second) AS dif
FROM table1
ORDER BY dif DESC
but the two columns can be NULL. In this case, it returns the error:
...
2
votes
3answers
97 views
Select the greatest value from each column, even if one of the values is null
I have a road_age_test table:
create table road_age_test (
surface_year int,
base_year int
);
insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (...
1
vote
1answer
2k views
How to represent NULL in relational algebra?
In the 3rd part of the question below:
Study the tables given below and write relational algebra expressions for the queries that follow.
STUDENT(+ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
...
1
vote
1answer
5k views
Does PostgreSQL index null values? [closed]
If I have a table like so:
Food
-----------------
name | price | x
Let's say I create an index on name & price, but the database has a bunch of null values for both name & price in some of ...
7
votes
2answers
147 views
Deriving a computed column based on several nullable columns
I'm a bit of an SQL novice.
I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks.
The syntax used to create the auto generated ...
2
votes
4answers
1k views
Can @parameter IS NULL slow down the execution of query?
Option 1:
CREATE PROCEDURE [dbo].[GetStudents]
@MinimumAge int = NULL
AS
select * from Students s where @MinimumAge is null or s.Age >= @MinimumAge
Option 2:
CREATE PROCEDURE [dbo].[GetStudents]...
5
votes
1answer
776 views
How to add a named constraint in Oracle SQL?
I have a table. I need to add a NOT NULL constraint to one of the columns. This is my best attempt at a solution:
alter table clookup add CONSTRAINT lookup_9 NOT NULL (clookup_col)
This produces ...
1
vote
1answer
840 views
SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records
The following was one of the practice questions in the online course i took on udemy for SQL.
I have a table 'emp' and consider the below names in the query map to their corresponding columns in ...
1
vote
0answers
410 views
Apache Phoenix Upsert not working while trying to update null/empty columns
Step 1: Update a column with null value.
Upsert into abc.tablename (Rowkeycombo1, Rowkeycombo2, columnname) values (123,to_date('1988-08-06'),null);
This works fine and the column then appears to ...
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 ...
-1
votes
1answer
19 views
Table for factory data along with user data
When creating master tables which are usually distinguished by a PK, say user_id, I'm often faced with the situation where I want to have a few pre-defined rows in the table.
I want data from these ...
