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;
A shared log in SQL query is applied by default by SELECT statements. The difference between Shared and exclusive locks is that exclusive lock will stop any update on the data being locked, whereas shared lock suggests the select query to wait if some update or insert operation is in progress for the row.
Shared lock will not stop any other request from selecting or updating the records.
For example- select * from employee;
The above statement will fetch employee data from employee table, but if at the same time some insert or update query is executing, it will impact the performance of select query as it has to wait for other operation (on the row/ page it is trying to read) to finish.
Workaround: If we are sure that other operations executing at the same time are not going to impact my select query, i.e. I am ok to read a few old records/ dirty read, Sql provides us keyword NOLOCK
select * from employee with (NOLOCK);
This will make sure our select query does not get impacted by insert and updates running at the same time, though we might have problem of dirty read.
Workaround 2: With NOLOCK we know we can have a dirty read problem, to avoid that, we can use READPAST. This will make the select query skip any rows being updated at the query time and hence avoids dirty reads, So we are achieving our goal of not waiting for insert/ update operations to be over plus. On the downside, the resultset will miss some of the records.
select * from employee with (READPAST);
Someone asked a question on a forum that why rownum=2 does not work for his query, so i though of sharing some details here.
Using rownum is a tricky affair. Safest bet is to use it only when you want to limit the number of results to be shown. For example rownum<2 or rownum<=5.
Why rownum=2 or rownum>2 will not work?
Read here – http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html
In summary, this is how oracle execute a query
- The FROM/WHERE clause goes first.
- ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
- SELECT is applied.
- GROUP BY is applied.
- HAVING is applied.
- ORDER BY is applied.
rownum<=2 clause will get converted to
ROWNUM = 1
for x in
( select * from emp )
exit when NOT(ROWNUM <= 2)
OUTPUT record to temp
ROWNUM = ROWNUM+1
if you change exit when NOT(ROWNUM <= 2) with rownnum=2, you can see it will fail in the first run itself
So if I cannot use rownum, what can I use. Try using row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
It works something like
SELECT last_name FROM
(SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
WHERE R BETWEEN 51 and 100;