Category Archives: SQL

SQL vs NoSQL

An old video but still relevant

Key Take aways

  • Complex SQL queries- Joins : SQL
  • Transaction management / ACID Properties: SQL (commit and rollback are by default whereas you need to tackle them in NSQL)
  • Huge quantity of data/ fast scalability – NOSQL
  • Write Heavy – Logging system – NoSQL
  • Read Heavy- Queries/ indexes – SQL
  • Fixed Schema- SQL, Flexible schema-NOSQL (Alter table statements are costly and have restrictions)
  • JPA/ hibernate/ django- by default support SQL
  • Archiving and managing huge data- NoSQL

Handling Null Values in SQL- ISNULL, NVL2, IFNULL, Coalesce

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

Oracle- PLSQL
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;

Union vs Union All

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.

Create a table as result of select query

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;