NULL

Handle NULL values in SQL:

>  Primary Key column/s can not have NULL values
>  Foreign Key column can have any number of NULL values.
>  Distinct command allow one NULL value.
>  Unique command allow any number of NULL values.
>  Group functions such as SUM(), AVG(), MAX(), MIN(),COUNT() ignore NULL values. Such as : AVG(5,NULL)=5, SUM(5,NULL)=5

For example:

Name	Dept	Salary	comm	State
Ram	Technical	1000	null	TX
Sam	Admin	1100	null	CA
Hari	Mechanical	1200	null	TX
Raju	Helper	800	800	TX
Govind	HR	1000	null	CA
Murali	Driver	900	null	NY
Murali1	DHead	null	null	NY
Sita	Actor	1400	111	TX

1.   Select Count(salary), count(name), sum(salary), avg(salary), sum(salary)/8, max(salary), min(salary) from TableUnitTest;

Output: 

Count(salary)	count(name)	sum(salary)	avg(salary)	sum(salary)/8	max(salary)	min(salary)
7	8	7400	1057.142857	925	1400	800

2.    Select state, avg(salary) from TableUnitTest group by state ;

State      avg(salary)

CA          1050

NY          900

TX           1100

 

>  NVL > To convert a NULL value to other values , use NVL command. Such as:
Select Name, NVL(Income,0), Department from TableName;
In above query, if salary is NULL then it returns '0'.

>   COALESCE  >  use this command to replace NULL with value from another column in the same row. Such as :
Select FirstName, MiddleName, LastName, COALESCE (FirstName,LastName,MiddleName) as LegalName, Salary, Department from TableName;
In above example, if FirstName is NULL then LastName will be used as LegalName, if FirstName and LastName both are NULL then MiddleName will be used as LegalName.

> NVL2  >  use this command to manipulate a column which might have NULL value. Let's see an example:
Select Name, Salary, Commission, NVL2(Commission, Salary+Commision,Salary) as TotalSalary Department from TableName;
In above example, if commission is not null then it displays Salary+Commission, if Commission is NULL then it displays only Salary as Total Salary. 

 

>NULLif  > example: nullif(a,b) will return null if a and b are equal

Let's see in the below table:

Name	Dept	Salary	comm
Ram	Technical	1000	null
Sam	Admin	1100	null
Hari	Mechanical	1200	null
Raju	Helper	800	800
Govind	HR	1000	null
Murali	Driver	900	null
Murali1	DHead	null	null
Sita	Actor	1400	111

Select nullif(salary,comm) as NullCheck from TableUnitTest where name = 'Raju';

Output: 

NullCheck

null

SubQueries in SQL

SubQueries in SQL

Sub Queries are the queries within another  SQL query(outer query or parent query). You can have any number of layers of inner queries. While executing  the SQL, the innermost query is executed first and gradually executes the outer queries . The outer queries might use the data from the subqueries(inner queries).

Once you understand the nature of sub query , you can use it anywhere in your SQL as per  your need and appropriateness of using the subquery . We are discussing the use of the subquery in below clauses:

SELECT

FROM

WHERE

HAVING

Subquery is also a complete query . As already mentioned, when the SQL is executed , the subquery  is executed first  and produces result that the outer query uses.

Subquery in SELECT :

TABLE1: INFO_EMP

BATCH_ID	NAME	JOB_NUMBER	POSITION	DEPARTMENT	HIREDATE
100	DAVID	143658	MANAGER	HR	10-Jan-15
103	SMITH	548900	ENGINEER	TECHNICAL	12-Dec-11
101	LINDA	784510	ASSISTANT	FINANCE	1-Jun-12
102	MIKE	543433	OPERATOR	TECHNICAL	1-Feb-12
NULL	SMITH	543433	ASSISTANT	TECHNICAL	2-Nov-13
NULL	SHARMA	543401	OFFICER	DEPLOYMENT	22-Aug-15

TABLE2 : TEST

EMP_ID	LOCATION	DEPT	SALARY
101	MD	NULL	2000
100	Chicago	NULL	1950.65
102	NY	NULL	3000
103	NY	NULL	NULL

Example1:

Below query uses  SubQuery  in SELECT  to fetch Location for the employees :

Select BATCH_ID , NAME , (SELECT A.LOCATION FROM TEST A  WHERE INFO_EMP.BATCH_ID = A.EMP_ID ) AS LOCATION , POSITION , HIREDATE FROM INFO_EMP ;

OutPut:

BATCH_ID	NAME	LOCATION	POSITION	HIREDATE
100	DAVID	Chicago	MANAGER	10-Jan-15
103	SMITH	NY	ENGINEER	12-Dec-11
101	LINDA	MD	ASSISTANT	1-Jun-12
102	MIKE	NY	OPERATOR	1-Feb-12
NULL	SMITH	NULL	ASSISTANT	2-Nov-13
NULL	SHARMA	NULL	OFFICER	22-Aug-15

Example2:

Let's  find Average salary , Name , Batch ID , Position, HireDate :

Select BATCH_ID, NAME , ( SELECT AVG(SALARY) FROM TEST ) AS AVG_SAL , POSITION , HIREDATE

FROM INFO_EMP;

OutPut:

BATCH_ID	NAME	AVG_SAL	POSITION	HIREDATE
100	DAVID	2316.883	MANAGER	10-Jan-15
103	SMITH	2316.883	ENGINEER	12-Dec-11
101	LINDA	2316.883	ASSISTANT	1-Jun-12
102	MIKE	2316.883	OPERATOR	1-Feb-12
NULL	SMITH	2316.883	ASSISTANT	2-Nov-13
NULL	SHARMA	2316.883	OFFICER	22-Aug-15

Example3:

Select BATCH_ID, NAME , (( SELECT AVG(SALARY) FROM TEST ) +1000 ) AS BONUS, POSITION , DEPARTMENT

FROM INFO_EMP ;

OutPut:

BATCH_ID	NAME	BONUS	POSITION	DEPARTMENT
100	DAVID	3316.883333	MANAGER	HR
103	SMITH	3316.883333	ENGINEER	TECHNICAL
101	LINDA	3316.883333	ASSISTANT	FINANCE
102	MIKE	3316.883333	OPERATOR	TECHNICAL
NULL	SMITH	3316.883333	ASSISTANT	TECHNICAL
NULL	SHARMA	3316.883333	OFFICER	DEPLOYMENT

SubQuery in WHERE :

EXAMPLE 1:

Find the employee information from INFO_EMP table , the employee who is having maximum salary in TEST table.  In below query, you can see , we have used two layers of the subqueries.

Select  NAME , POSITION , DEPARTMENT , HIREDATE

FROM INFO_EMP

WHERE BATCH_ID = ( SELECT EMP_ID FROM TEST WHERE SALARY = ( SELECT MAX(SALARY) FROM TEST ));

OutPut:

NAME	POSITION	DEPARTMENT	HIREDATE
MIKE	OPERATOR	TECHNICAL	1-Feb-12

EXAMPLE 2:

Find employee information from 'TECHNICAL' department.

Select  NAME , POSITION , DEPARTMENT , HIREDATE

FROM INFO_EMP

WHERE JOB_NUMBER IN ( SELECT JOB_NUMBER FROM INFO_EMP WHERE DEPARTMENT = 'TECHNICAL');

OutPut:

NAME	POSITION	DEPARTMENT	HIREDATE
SMITH	ENGINEER	TECHNICAL	12-Dec-11
SMITH	ASSISTANT	TECHNICAL	2-Nov-13
MIKE	OPERATOR	TECHNICAL	1-Feb-12

Sub Query in HAVING clause :

Let's take a table EMP_INFO:

NAME	DEPARTMENT	SALARY	POSITION	STATE	JOINDATE
DAVID	MARKETING	2000	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	HR	TX	5-Jan-06
RAM	RESOURCES	1200	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	MARKETER	FL	9-Feb-08
BRITNI	TECHNICAL	1800	ASSISTANT	PA	1-Feb-09
ADAM	TECHNICAL	2200	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2500	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	MANAGER	VA	21-Aug-12

Let's find  the departments having average salary greater than the average salary of all department.

Select  DEPARTMENT , AVG(SALARY)  AS AVG_SAL

FROM EMP_INFO

GROUP BY DEPARTMENT

HAVING AVG(SALARY) > (SELECT AVG(SALARY) FROM EMP_INFO);

OutPut:

DEPARTMENT	AVG_SAL
TECHNICAL	2000
EXECUTIVE	2350

Subquery in FROM :

Let's find employee name , department , state , salary from the subquery which filters out the data with salary 2000 or more.

Select  NAME, DEPARTMENT, STATE , SALARY  FROM (SELECT * FROM EMP_INFO WHERE SALARY <2000 p="">

OutPut:

NAME	DEPARTMENT	STATE	SALARY
RICHARD	SALES	MD	1000
SMITH	RESOURCES	TX	1500
RAM	RESOURCES	TX	1200
GOPI	SALES	FL	1200
BRITNI	TECHNICAL	PA	1800
JAMES	MARKETING	VA	1200

Let's have a TEST table as below :

EMP_ID	LOCATION	DEPT	SALARY
101	MD	NULL	2000
100	VA	NULL	1950.65
102	PA	NULL	3000
103	NY	NULL	NULL

Now, let's find the employee NAME, DEPARTMENT, STATE from the two tables EMP_INFO and TEST  for the states which are common to two tables.

Select  *  FROM (SELECT A.NAME , A.DEPARTMENT, A.STATE  FROM EMP_INFO A , TEST B  WHERE A.STATE = B.LOCATION ) ;

OutPut:

NAME	DEPARTMENT	STATE
RICHARD	SALES	MD
BRITNI	TECHNICAL	PA
ADAM	TECHNICAL	MD
JAMES	MARKETING	VA
JULI	EXECUTIVE	VA

Let's find out the Name and Department from the above result , using subquery.  We are interested to know the employee name from other than TECHNICAL department even though the subquery returns data/information for Technical department also.

Select  NAME, DEPARTMENT  FROM (SELECT A.NAME , A.DEPARTMENT, A.STATE  FROM EMP_INFO A , TEST B  WHERE A.STATE = B.LOCATION )

WHERE DEPARTMENT !='TECHNICAL';

OutPut:

NAME	DEPARTMENT
RICHARD	SALES
JAMES	MARKETING
JULI	EXECUTIVE