Some Common Mistakes When Querying SQL Database

When I started learning SQL, I found out several issues, which I thought to be interesting to share. For our needs, let us use AdventureWorks2012 database, which can be obtained here.

Introduction

When I started learning SQL, I found out several issues, which I thought would be interesting to share. For our needs, let us use AdventureWorks2012 database, which can be obtained here.

Don't Forget about NULL

Let's have a look at the following query,

  1. select Count(*) from Sales.SalesOrderDetail  

The result will be,

Some Common Mistakes When Querying SQL Database 

Filtering by CarrierTrackingNumber,

  1. select Count(*)  
  2. from Sales.SalesOrderDetail  
  3. Where CarrierTrackingNumber = '4911-403C-98'  

will give us,

Some Common Mistakes When Querying SQL Database 

It is natural to assume that the result of the following query,

  1. select Count(*)  
  2. from Sales.SalesOrderDetail  
  3. Where CarrierTrackingNumber <> '4911-403C-98'  

would be 121317-12=121205. However, in fact, it is,

Some Common Mistakes When Querying SQL Database 

So where are all the other rows? Those rows have NULL in CarrierTrackingNumber column.

Some Common Mistakes When Querying SQL Database 

The fact is that comparison between NULL and the value ('4911-403C-98' in our case) will return UNKNOWN, while WHERE clause returns only statements that are true. Comparison between value and NULL will also give UNKNOWN, which is shown on a screenshot below.

Some Common Mistakes When Querying SQL Database 

So if you need to compare your value and NULL, you should use operator IS NULL. For example,

  1. select Count(*)  
  2. from Sales.SalesOrderDetail  
  3. Where CarrierTrackingNumber <> '4911-403C-98' or CarrierTrackingNumber  IS  NUll  

will give us expected,

Some Common Mistakes When Querying SQL Database 

INNER JOIN is Not a Golden Hammer

Let us select all job candidates and id of their addresses (it is more natural to select addresses but let us not complicate the query for our learning needs). As this information is situated in 2 different tables linked by the column BusinessEntityId, a common practice for the beginners is to use INNER JOIN on this column.

  1. select C.JobCandidateID, A.AddressID  
  2. from HumanResources.JobCandidate C inner join Person.BusinessEntityAddress A   
  3. on c.BusinessEntityID = A.BusinessEntityID  

Two candidates are selected,

Some Common Mistakes When Querying SQL Database 

Everything looks fine until we realize that there are 13 job candidates in the first table.

Some Common Mistakes When Querying SQL Database 

Again, the problem is NULL. If we examine JobCandidate table closely, we'll see that the column BusinessEntityId can take NULL as its values.

Some Common Mistakes When Querying SQL Database 

Therefore, if we want to select all job candidates, we should use LEFT JOIN instead of INNER JOIN, which will just select all rows from the left table.

  1. select C.JobCandidateID, A.AddressID  
  2. from HumanResources.JobCandidate C left join Person.BusinessEntityAddress A   
  3. on c.BusinessEntityID = A.BusinessEntityID  

And voila!

Some Common Mistakes When Querying SQL Database 

If Syntax Construct Exists, Why Don’t You Use It?

Let us have a look at the two queries, which do exactly the same: select items, which cost more than 100 and were bought more than 1000 times.

  1. Select t.ProductID, count(*) AS TransactionCount From Sales.SalesOrderDetail t  
  2. WHERE (Select count(*) From Sales.SalesOrderDetail t1   
  3.     Where t1.ProductId = t.ProductId AND t1.UnitPrice > 100) > 1000  
  4. Group by t.ProductId  
  5.   
  6. Select ProductID, count(*)  
  7. From Sales.SalesOrderDetail  
  8. Where UnitPrice > 100  
  9. Group by ProductID  
  10. Having count(*) > 1000  

Which one to use? Here are the statistics,

Some Common Mistakes When Querying SQL Database 

As you can see, the first one is imperformant. Therefore, if subquery or join can be replaced by a more simple approach, you should better to do so.

Conclusion

This is by no means an exhaustive list of common pitfalls but still, I hope the article was useful to you.