SQL Server Nested Select


SQL Server nested select is also known as a “sub query”. This is where instead of using a list, table, or view, you actually use another query. In this article, we will go over the basics on how to use a SQL Server nested select statement.

Basic Rules For A Nested Select
Here are a couple basic rules when using a nested select statement in SQL Server.

  • The nested select statement must be enclosed in parentheses.
  • When using an ORDER BY clause in your nested select, you must specify TOP command.
  • Many times, you will need to alias the nested select.

Sample Nested Select
Here is a sample where we return the basic animal information with their average weight.

		W. AverageWeight
FROM	Animal A
				  		AVG(AnimalWeight) AS AverageWeight
	          FROM		AnimalWeights
	          GROUP		BY AnimalID) W ON W.AnimalID = A.AnimalID

In the above example, we do our normal inner join, but we join to a nested select statement. This allows you to join on the results from the nested select. You can see that the nested select statement will get the average weight of the animals by AnimalID. That whole query is inside parenthesis. Then we alias that nested select results as “W”. We can then reference the 2 return values (AnimalID and AverageWeight) outside of the nested select statement by using the table alias W.

NOTE: Remember that you will need to alias your nested select statements most of the time. This can stop you up if you aren’t looking for it.

Cross Apply In SQL


CROSS APPLY is one of those helpful things in SQL Server that most people don’t think of or may not even know about. In this article I’d like to talk about what the APPLY operator is and how we can use it to simplify our sql statements.

APPLY Operator
The APPLY operator allows you to join a table to a table-valued function. A table-valued function is a function that will return a table with one or more columns. With the apply operator, you can pass values from the first table in to the table-valued function.

There are only 2 types of APPLY operators:
CROSS APPLY – Returns records when a value from both sides of the operator match. Like an INNER JOIN.
OUTER APPLY – Returns all rows from the other side of the operator and will return the value or NULL from the table-valued function. This is like an OUTER JOIN.

SELECT	A.AnimalName,
FROM	Animal A
CROSS	APPLY GetAnimalHabitat(A.AnimalTypeCode) H

In the example above, you can see that we join to the GetAnimalHabitat function using the CROSS APPLY. You can imagine that this function does a bunch of logic that is not visible in this query. If the GetAnimalHabitat function had 25 lines of code, you can see how this simplifies the above query dramatically.

Additional Thoughts
The APPLY operator can simplify the code, but could be accomplished by joining to a sub query as well. One difference is that the function in the APPLY operator is being executed for every row in the outer table. If you use the APPLY operator, make sure that you test the speed of your query to make sure that it did not degrade performance.

Case Sensitive Compare


Occasionally we need to do a string comparison in SQL Server as a case sensitive compare.  By default, case sensitive compare is not turned on for tables in SQL Server.  It’s actually pretty easy to change a string comparison to a case sensitive compare.  You can do this by using the COLLATE clause.

Read below to find out why this works and other tips on doing a case sensitive compare.

SELECT	A.AnimalID, A.AnimalName, AFT.FeedingTime
FROM	Animal A
INNER	JOIN AnimalFeedingTime AFT ON AFT.AnimalName = A.AnimalName COLLATE SQL_Latin1_General_CP1_CS_AS

What Is The COLLATE Clause

The COLLATE clause is something that you can add to the end of an expression to set the collation of the operation to a specific collation.  So in the example… the string equal comparison is being changed to a collation of SQL_Latin1_General_CP1_CS_AS.

A collation in SQL Server is something defines how data is compared.  This could be things like case sensitivity, acceptable characters, etc…  Most of the time (for English installations of SQL Server) it will be a collation type of SQL_Latin1_General_CP1_CI_AS.  This is because the Latin character set covers English and other Latin based languages.

The two most common collations that I deal with are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS.  You can see that they are very similar.  In fact… the only difference is the _CI_ and _CS_.  These actually stand for Case-Insensitive and Case-Sensitive.
Field Level Case Sensitivity

Even better… you don’t need to set the collation on every sql statement that you want to make case sensitive.  There is actually a setting that you can apply to a field in a table.

NOTE: that if you are joining on this table, both collations must be the same or you will need to specify the collation to use.

Create a new table with a case sensitive field

TABLE	Animal
(AnimalID		INT,
AnimalName	VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS)

Change a table’s column to be case sensitive

TABLE	Animals

Delete Using Join


The DELETE statement is one of the most fundamental statements on SQL Server.  Just about everyone knows how to use the delete statement.  But did you know that you can delete using a join?  It’s actually pretty easy.

Delete Using Join

To delete from a table using a join condition, you need to first format your statement like a regular select statement.  Instead of using a SELECT statement, you will use a DELETE statement.  After the delete command you will specify the table that you want to delete from in your join.

In the example below, we want to delete all orders for this employee.  I have aliased the Orders table with the letter O.  So my delete statement will say “DELETE O”… where O is the alias for the Orders table.  This will only delete data from the Orders table and not from the Employees table.

FROM	Orders O
INNER	JOIN Employees E ON E.EmployeeID = O.EmployeeID
WHERE	E.LastName = 'Tester'

One thing to note is that you cannot delete from multiple tables this way.

Reference: http://technet.microsoft.com/en-us/library/ms189835.aspx