Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 20 July 2017

SQL is NULL AND is NOT NULL


What is Null values:


SQL NULL is an undefined and incomparable value.
It is not equal to zero or space.
It will not occupy any memory.
It is represented by NULL keyword and displayed as space.
It is a standard value supported by every RDBMS tool defined by EF code.
It provides unique treatment for all types of data any athematic operation with null values gives null.

Is null/is not null:


Used to compare null values
Supports with all types of data

The following statement List the employees who are not having commission.

SELECT ename, sal, comm
  FROM emp
 WHERE comm IS NULL;

The following statement List the employees who are having commission.

SELECT ename, sal, comm
  FROM emp
 WHERE comm IS NOT NULL;

The following statement Assign job as executive if job is null.

UPDATE emp
   SET job = 'EXECUTIVE'
 WHERE job IS NULL;

The following statement Assign dept as 10 if deptno is null.

UPDATE emp
   SET hiredate = '19-NOV-2011'
 WHERE hiredate IS NULL;

The following statement removing a column where comm having 5000

UPDATE emp
   SET comm = NULL
 WHERE comm = 5000; 

SQL is NULL or empty


The following statement returns values where commission having NULL or EMPTY

SELECT *
  FROM emp
 WHERE (comm IS NULL OR comm = '');


SQL is NOT NULL or empty


The following statement returns values where commission having NOT NULL or EMPTY

SELECT *
  FROM emp
 WHERE (comm IS NOT NULL OR comm = '');


SQL is NULL or 0(zero)


The following statement returns values where commission having NULL or 0(ZEO)

SELECT *
  FROM emp
 WHERE (comm IS NULL OR comm =0);



You May Like                                             
                            SQL IN Operator
                            SQL Like Operator
                            SQL Between Operator                       



Share this article :

1 comments:

  1. i Buddy,


    I genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.


    i created one alert that picks employees that have probation end date before one month and mail is sent to supervisor and it works fine but a new requirement is that when the probation initiation begins it should pick from hr_api_transactions table and stop the alert from sending afte probation confirmation please help. below is the query

    SELECT papf.employee_number,
    papf.full_name employee_name,
    pjt.NAME job,
    pp.NAME position_name,
    papf.start_date,
    paaf.date_probation_end,
    paaf.probation_period, notice_period,
    DECODE (probation_unit,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) probation_unit,
    DECODE (notice_period_uom,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) notice_period_uom,
    hl_freq.meaning, paaf.effective_start_date, paaf.effective_end_date,
    mg.full_name supervisor_name, mg.email_address
    supervisor_email
    FROM per_all_assignments_f paaf,
    per_jobs_tl pjt,
    per_grades_tl pgt,
    per_all_people_f papf,
    hr_lookups hl_freq,
    per_all_positions pp,
    per_all_people_f mg
    WHERE 1 = 1
    AND paaf.person_id = papf.person_id
    AND paaf.business_group_id = papf.business_group_id
    AND pgt.grade_id(+) = paaf.grade_id
    AND pjt.job_id = paaf.job_id
    AND paaf.frequency = hl_freq.lookup_code
    AND papf.business_group_id = pp.business_group_id
    AND pp.position_id = paaf.position_id
    AND paaf.person_id = papf.person_id
    AND paaf.job_id = pp.job_id
    AND paaf.supervisor_id = mg.person_id(+)
    AND TRUNC (SYSDATE) BETWEEN TRUNC (mg.effective_start_date)
    AND TRUNC (mg.effective_end_date)
    AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND hl_freq.lookup_type = 'FREQUENCY'
    AND paaf.probation_period IS NOT NULL
    AND TRUNC (paaf.date_probation_end) BETWEEN paaf.effective_start_date
    AND NVL (paaf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (paaf.date_probation_end) BETWEEN papf.effective_start_date
    AND NVL (papf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TO_CHAR (papf.original_date_of_hire, 'yyyy') =
    TO_CHAR (SYSDATE, 'yyyy')
    AND TO_DATE (SYSDATE, 'DD-MM-RRRR') =
    TO_DATE (paaf.date_probation_end, 'DD-MM-RRRR')
    - 30





    Great effort, I wish I saw it earlier. Would have saved my day :)


    Obrigado,

    ReplyDelete