Parv The IT Geek

Have you ever wanted to search for a column for a range of values? For example if you were searching for Product ID which ranges from 1 to 300, or look for a range of names starting A to D.

Well I can show you how to do these quickly and easily by using the power of SQL.

The below query shows there are 504 rows in the Production.Product table

USE AdventureWorks2012 
GO 
SELECT * 
FROM [Production].[Product]

If you wanted to search for ProductID’s which were 1 to 4 you could do it using the below query.

USE AdventureWorks2012 
GO 
SELECT * 
FROM [Production].[Product] 
WHERE ProductID = 1 
OR ProductID = 2 
OR ProductID = 3 
OR ProductID = 4

Whilst the above query works it is a very ugly and inefficient query as you are writing way too much code when you could use search a range which takes less time to type and is more easily read and understood.

SELECT * 
FROM [Production].[Product] 
WHERE ProductID 
BETWEEN 1 AND 4

As you can see the above query is much more readable , easily understood and more importantly is shorter which saves you time.

I would recommend using the syntax on different values and tables to see how many different types of data you can retrieve.

Originally published at https://parvtheitgeek.com on January 24, 2014.

Have you ever been presented with a list of values which need to be found in a column or wondered how to search for a list of exact names, addresses, numbers without using wildcard searches or ranges.

Well SQL has the answer and it is the IN command.

USE AdventureWorks 
GO 
SELECT * 
FROM HumanResources.Department

The above query produces 16 rows of information when there are no clauses filtering the results.

If you wanted to know how many departments were in the Manufacturing group you would use the below query.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName ='Manufacturing'

If you wanted to know how many departments were in the Inventory Management group you would use the below query.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName ='Inventory Management'

You could combine both queries as show below using the OR command to filter for more than one value.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName ='Inventory Management' 
OR GroupName ='Manufacturing'

However, though it does achieve our desired result there are is a better method which I shall show you below now using the IN command.

SELECT * 
FROM HumanResources.Department 
WHERE GroupName IN ('Inventory Management','Manufacturing')

The above query produces the same results but is far superior in its readability and ease of use. We can easily see the filter and there are fewer commands which measns less to go wrong and less to type.

Experiment with numbers, dates and words on the database to familiarise your self with this new query.

Originally published at https://parvtheitgeek.com on January 23, 2014.

Well SQL has the solution and our saviour is the ‘IS NULL’ and ‘IS NOT NULL’ commands. Using them in a ‘WHERE’ clause will allow us to do the filtering out appropriately.

USE AdventureWorks 
GO 
SELECT COUNT(*) 
FROM [AdventureWorks].[Person].[Address]

The above query bring us a result of 19,614 rows contained within the AddressLine2 where no values have been filtered.

USE AdventureWorks 
GO 
SELECT COUNT(*) 
FROM [AdventureWorks].[Person].[Address] 
WHERE AddressLine2 IS NULL

Using the ‘IS NULL’ clause we find that there are 19,252 rows where AddressLine2 has a Null value.

USE AdventureWorks 
GO 
SELECT COUNT(*) 
FROM [AdventureWorks].[Person].[Address] 
WHERE AddressLine2 IS NOT NULL

Using the ‘IS NOT NULL’ clause we find that there are 362 rows where AddressLine2 has a value which does not contain a Null.

A quick check shows total count of ‘IS Null’ ( 19,252) + total count of ‘IS NOT NULL’(362) equal 19,614 which shows that we have returned the correct figures and have not been tripped up by any kind of data anomalies.

Originally published at https://parvtheitgeek.com on January 22, 2014.

A basic query of the HumanResources.Department table bring s the following results.

If you wanted to reverse the order of the DepartmentID then you would need to user the ‘ORDER BY’ clause.

The default sort is always ascending ( ASC) but it does not have to be explicitly written. If you want the order to be reversed then you would need to use DESC ( descending).

Sorting of results can also be done on numbers, dates, letters……

Originally published at https://parvtheitgeek.com on January 17, 2014.