CODEWITHSUNDEEP
mysqlsqloracle-database
LW39
LW39

Reputation: 1

Not showing tables with Null value when i select *

when i tried to select * from a table i've created where i've inserted a null value in. the entire row of data which contains the null value will be omitted;

CREATE TABLE Credit_transactions (
CT_id INT PRIMARY KEY,
Date_time DATE,
Money INT ,
Credits INT  ,
CT_type INT ,
Customer_id INT );

for example if a transaction does not include any money i will insert a null value when inserting the data such as;

INSERT INTO Credit_transactions(CT_id, Date_time, Money, Credits, CT_type, Customer_id)
VALUES('2', '10-DEC-2018', 'NULL', '3', '1', '1');

however after inserting all the data and upon trying to select * from the table i only get rows where theres no NULL values example i keyed id 1,2 and 3 id2 contains a null value in one of the columns, i will only get id1 and id3 after selecting all.

how can i fix this, i want to display the value as null value as it is E.g;

id:1 money:10
id:2 money:NULL
id:3 money 20

first time using stack overflow, and sorry for such a beginner question.

Upvotes: 0

Views: 1152

Answers (2)

whatspoppin
whatspoppin

Reputation: 383

The column Money is declared as INT, and you are trying to insert a String value 'NULL' instead of NULL, so the sql query will fail (inserting).
So it's obvious when selecting everything from the table, You wouldn't get the expected results.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You have only inserted one row into the table. That is why you are getting only one row in the result set.

If you want rows not in the table, you need something like a left join. And you either need a table with the values you want or to create one in the table:

select x.ct_id, ct.money
from (select 1 as ct_id from dual union all
      select 2 as ct_id from dual union all
      select 3 as ct_id from dual
     ) x left join
     Credit_transactions ct
     on ct.ct_id = x.ct_id

Upvotes: 0

Related Questions