Questions tagged [null]

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

Filter by
Sorted by
Tagged with
4
votes
1answer
745 views

Why Is Null Ignored In Query Results

I have a table with similar structure to the below, and a field has a NULL value in it. When I query the table this record is not returned, it is omitted. How should the query be altered so that ...
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 ...
0
votes
1answer
134 views

Empty result set when matching NULL?

If I run a statement such as SELECT * FROM stuff WHERE something=NULL I will get an empty result set. I know that the solution is to use something IS NULL. I also understand why: SQL interprets NULL ...
2
votes
3answers
50 views

Counting NULLs as zeroes and filtering with the WHERE statement

I have a SQL puzzle: How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased? I ...
-1
votes
1answer
233 views

Will removing NTEXT columns improve performance and reduce space used?

I have some questions regarding the (still deprecated) (N)TEXT datatype in Sql Server - in many tables I can find (N)TEXT columns and many (90%+) rows where those values are NULL, so their use case ...
2
votes
1answer
538 views

Nullable columns or jsonb for storing user profile?

I'm deciding between using nullable fields or jsonb to store user profile. Initially, this would be used for contacts: email and phone. I anticipate that there may be other columns added later, such ...
0
votes
1answer
5k views

How to set a NOT NULL column into NULL in PostgreSQL?

I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow ...
2
votes
4answers
568 views

Computed field when NULL

I have two fields in my table : A int, B int. I want to add two computed fields (CF1 & CF2) that uses these two fields A & B. CF1 = (case when [A]>[B] then (CF1=3) else case when [A]<[B]...
4
votes
1answer
1k views

Check constraint on nullable data, how to approach?

This is one of the tables in my database. Constraints omitted for clarity. CREATE TABLE [Person].[Person] ( [ID] INT NOT NULL IDENTITY, [Forename] ...
3
votes
2answers
192 views

Avoiding null comparisons in where clause

It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given. If that statement is true, ...
16
votes
1answer
3k views

(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL

Why is x IS NOT NULL not equal to NOT x IS NULL? This code: CREATE TABLE bug_test ( id int, name text ); INSERT INTO bug_test VALUES (1, NULL); DO $$ DECLARE v_bug_test bug_test; BEGIN ...
7
votes
1answer
9k views

mysql - unique index with nulls - actual solution anyone?

CREATE TABLE IF NOT EXISTS b2c_constants ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, is_deleted BOOL DEFAULT FALSE, UNIQUE (name) ) ENGINE InnoDB ...
0
votes
1answer
121 views

Combining nullif while checking if field is an integer with a case statement

As part of an ETL process, I need to copy a varchar field in table1 (staging table) which is meant to contain an integer value to a tinyint field in table2 (production table). However I am having some ...
6
votes
1answer
3k views

Field both NOT NULL and DEFAULT NULL

This MySQL table had me perplexed for a moment: mysql> desc quux; +---------------------------+-----------------------+------+-----+---------+----------------+ | Field | Type ...
1
vote
1answer
392 views

MySql trigger having null variables during bulk inserts

I have an SQL trigger. (mysql). when am doing bulk inserts the summed values become null, PS. single inserts work.bulk inserts don't. DROP TRIGGER IF EXISTS `calculate_balances`; CREATE TABLE ...
-4
votes
1answer
101 views

How can i insert data into a NULL record in a column that allows NOT NULLS? [closed]

I hope my questions makes sense but what i am trying to do (which in my mind is obscenely simple which makes it more frustrating that i cant figure it out) is to change a column that consists of NULLS ...
2
votes
2answers
110 views

Comparing Two Values when one or more could be NULL

I am writing a trigger and want to see if one column has changed could be NULL and trying to avoid the following: IF NEW.value != OLD.value OR (NEW.value IS NULL AND OLD.value IS NOT NULL) ...
5
votes
3answers
34k views

Why does COUNT() aggregate return 0 for 'NULL'?

A SELECT statement returns several rows: SELECT ColA FROM TableA WHERE ColA IS NULL I get 47 rows that have 'NULL' for ColA within TableA. ColA NULL NULL NULL etc... If I add an aggregate to ...
0
votes
1answer
65 views

Return 2 lines from a table if one line contains a column > 0 and the other is NULL [closed]

How to return 2 lines even if one line is null but the other > 0 item_id | location | quantity ------------------------------------- 14 | 1 | 10 14 | 2 | ...
3
votes
2answers
2k views

count null values of table from information_schema tables

Is there any way to count null values in a column by using information_Schema tables? EG: SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='myschema' and ...
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 ...
3
votes
1answer
457 views

decode(null,null,'true','false') = 'true'?

Today my database world was shattered. For my whole lifetime I thought, comparing with a null value in Oracle results in the "false-case". But not so for the decode function. Anybody know, what's ...
11
votes
3answers
6k views

Is there a way to make a null check on a variable in a WHERE clause only occur once?

I have a query on a large table that looks like this: declare @myIdParam int = 1 select * from myTable where (@myIdParam is null or myTable.Id = @myIdParam) There are several similar conditionals ...
1
vote
2answers
58 views

SQL to combine records only if data is available [closed]

I have a table with the following format: id description1 description2 123 null abcd 123 zyxw abcd 124 null efgh 124 vuts ...
0
votes
2answers
108 views

Select single id from NULL

We are having a database full with set ID's. To fill the database, we have to select one single ID every time. This may only be an ID where the rest of the values are set with NULL (empty). How to do ...
1
vote
2answers
14k views

casting empty string as null datetime

Consider the following code in Microsoft SQL Server 2012: INSERT INTO [dbo].Production SELECT [field1] ,[field2] ,cast([datefield] as datetime) FROM [RAW].Staging The staging table is ...
1
vote
1answer
333 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
70 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 NULL. ...
0
votes
3answers
822 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 ...
5
votes
2answers
10k 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 ...
0
votes
1answer
132 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 ...
0
votes
1answer
776 views

Joining columns with null marks into one row [closed]

I used a sub select to get the data I want. I'm struggling to merge it all into one row now though. This is an example of the result where its bike but there are loads on them: bike bike ...
4
votes
5answers
1k 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 ...
2
votes
3answers
866 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
77 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 ...
10
votes
3answers
1k 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 ...
6
votes
2answers
4k 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
149 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
558 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(NodeId)...
2
votes
2answers
2k 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 ...
1
vote
4answers
4k views

Display both null and not null under same id but not both null or not null like XOR logic

Consider the following Employee table: In this table I need to select only the ids which have both null and not null marks, and it should not be both not null or both are null in col1.
4
votes
2answers
133 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
2k 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?
55
votes
7answers
20k 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
656 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
4k 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 ...
7
votes
5answers
4k 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 ...
7
votes
1answer
4k 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 ...
2
votes
1answer
1k 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, `...
15
votes
2answers
7k 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 ...