Skip to main content

Posts

Showing posts from March, 2013

Differerence in Exists and In operator in sql query

You can get Difference in Exists and In operator in sql query by below example. Using SQL server database 2008 R2 in query editor. SELECT * FROM Dept where DeptID IN (select Deptid from subDept ) SELECT * FROM Dept where exists (select Deptid from subDept ) This a simple example where I know the second query will execute faster than the first. The qualifier "if both Deptid columns are unique" is a critical requirement in order for the JOIN to produce the same results as the IN. So: •JOIN will produce one row for each matching row in subDepts. •IN will produce only one row if there is one or more matching rows in subDepts •EXISTS will produce one row if there are any rows in subDepts regardless of whether they match or not

What is Null value ?

Nulls are neither good nor bad. NULL is simply the absence of data. How one deals with NULL is entirely dependent on the environment you are working within. The various forms of VB and xBase platforms seem to be  the worst at accommodating NULL within code. So, from that perspective, . However, from a database perspective (excluding MS-Access) a column having a NULL state is actually useful information (the absence of a value). I find NULL to be a useful data state, in that this clearly indicates that a value has either not been set, or specifically cleared. It's a pain to deal with fields that can either contain a value, blank, or null. It's just a bunch of extra coding to deal with the variations when writing code. It is different from dealing with results from a query where there is a left join that returns a null value.