SQL - All About NULL
I can be reachable on [email protected].
You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.
My Snowflake Udemy Course:
https://www.udemy.com/course/snowflak...
Queries For Practice
====================
CREATE TABLE EMP
( EMPID INT NOT NULL,
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30),
AGE INT NULL,
SALARY DOUBLE,
LOCATION VARCHAR(20),
DEPTID INT NOT NULL,
PRIMARY KEY(EMPID)
);
INSERT INTO EMP VALUES
(1, 'Anitha', 'Rao', 28, 60000, 'Hyderabad', 101),
(2, 'Vinay', 'Kumar', 31, 70000, '', 102),
(3, 'Divya', null, 26, 50000, 'Hyderabad', 102),
(4, 'Rama', 'Devi', 28, null, 'Bangalore', 101),
(5, 'Naresh', 'Raju', 30, 65000, ' ', 103),
(6, 'Uma', 'Shankar', null, 55000, 'Chennai', null); -- change null to 102
SELECT * FROM EMP;
======================
1. How to find Null or Blank?
By using IS NULL
select * from emp where last_name is null;
select * from emp where salary is null;
select * from emp where location is null;
2. Length of Null:
Length of a null value is null where length of a blank value is 0
select location, length(location) from emp;
select first_name, last_name, length(last_name) from emp;
select first_name, last_name, length(salary) from emp;
=======================
3. Concatenation with nulls:
We can’t concatenate strings and nulls, if we do entire result will become null.
select first_name, last_name, first_name||last_name as full_name from EMP;
select 'RAM'||null as name;
select 'RAM'||'' as name;
4. IFNULL:
How to handle nulls in above case, Answer is with IFNULL.
select first_name, last_name, first_name||ifnull(last_name,'') as full_name from EMP;
select first_name, IFNULL(last_name,'NA') from emp;
==================
5. Aggregate functions with Nulls
Null is neither maximum nor minimum
select max(salary) from emp;
select min(salary) from emp;
select min(ifnull(salary,0)) from emp;
Count() function will not consider null values.
select count(*) from emp; -- 6
select count(empid) from emp; -- 6
select count(last_name) from emp; -- 5
select count(salary) from emp; -- 5
select count(location) from emp; -- 6
Aggregate functions with Nulls
AVG(), MEAN() will not consider nulls
select avg(salary) from emp;
select avg(ifnull(salary,0)) as avg_sal from emp;
select avg(case when salary is null then 0 else salary end) as avg_sal from emp;
==================
6. Handling Blank spaces
Find list of employees whose location info is not available
select * from emp where location is null;
select * from emp where location is null or location = ‘’;
select * from emp where location is null or trim(location) = '';
=================
7. Null never matches with another null
select case when null = null then 'True' else 'False' end as null_match;
create table table_A(ID int);
insert into table_A values(1),(2),(3),(null);
create table table_B(ID int);
insert into table_B values(1),(2),(4),(null);
select A.* from table_A A join table_B B on A.ID = B.ID;
I can be reachable on [email protected]