There can be situations when if a value is null in sql query, you want to replace it with something else. Different DBs provide you different options
select NVL2(teamid, teamid, deptid) allocationid, empid, name from employee; //If team id is available that is returned as allocation id else deptid
IN MS-SQL we have ISNULL
select ISNULL(teamid, deptid) allocationid, empid, name from employee;
Similarly MySQL has IFNULL
Another option is COALESCE, this works for both Oracle and MS-SQL. This is slightly different from others in implementation that it makes columns to be skipped unless first non-null value is found
select COALESCE(teamid, deptid, organizationid) allocationid, empid, name from employee;
The simple difference between using Union and Union All is that Union selects only distinct records from both the resultsets, whereas Union All will return everything.
But today I figured out one more use of union All today. In a ms sql query, I was using union to add up resultset of multiple queries, and got this error
“The text data type cannot be selected as DISTINCT because it is not comparable.”
As I knew none of my queries will return duplicate records, I did not need distinct clause. Replacing Union with “Union All” did the trick in this case.
If you want to dump the result of your select query into a table, this query will help in Oracle or MySql.
Create table newTable select * from oldTable where condition=true;
In case of MsSql this will work
Select * into newTable from oldTable where condition=true;