WHILE Statements in SQL

Previously I've spoken about control of flow statements in SQL (see the following: IF...ELSE, GOTO).  Today I will talk about another control of flow statement: the WHILE statement.

A WHILE statement allows you to specify a statement to check and then SQL statement(s) to run while that expression is still true.

The general outline for a WHILE statement is as follows:

WHILE Boolean_expression
{ sql_statement }

• A boolean expression is one that is either true or false
• The SQL statement(s) are the statements to run while the WHILE statement is still true
• If your SQL statement is more than one statement, you will have to begin and end your WHILE statement with BEGIN and END, as seen below.

WHILE Boolean_expression

BEGIN

SQL Statement Number 1

SQL Statement Number 2

END

Following is an example of when a WHILE statement may be useful:

WHILE (SELECT count(*) FROM Customers Where Balance < 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance < 50

END

The above will update the balance of a customer to their balance times 2 until that balance is above $50.  For example, if we have a customer whose balance is $5, it will go through the WHILE statement 4 times until the balance is $80.

Keep in mind that when WHILE statements are used, it is possible to run into an infinite loop (a loop that never stops) if you aren't incrementing your values correctly.  Using the same query, if I change it a little bit, I can get an infinite loop:

WHILE (SELECT count(*) FROM Customers Where Balance > 50) > 0

BEGIN

UPDATE Customers

SET Balance = Balance * 2

Where Balance > 50

END

The above will never stop if I have one customer that has a balance over $50.  Because they are over $50, our boolean expression is and will always be true.

To read up more on WHILE statements, go here: WHILE

Creating a Child Form in C#

I spent some time this weekend working in C# on a personal project.  One of the subjects I came across was creating a child form.  A child form opens a new form within your current window.

During my research, I came across the following help document:

How to: Create MDI Child Forms

I have a program I created at work that has different tabs for specific tasks that need to be run.  I will be working on migrating from the tabbed format to the child form format, as this seems to be less confusing and the layout looks better.

A few key points for creating a child form:

1. Create a new windows form in your project for each new window.
2. Create an event handler for when you click on the button/menu item that will open the new form (Create Event Handler)
3. Paste the following in your event handler:

protected void MDIChildNew_Click(object sender, System.EventArgs e)
{

   Form2 newMDIChild = new Form2();
   // Set the Parent Form of the Child window.
   newMDIChild.MdiParent = this;
   // Display the new form.
   newMDIChild.Show();
}

Subqueries in SQL

A very helpful feature of SQL is the use of subqueries.  These subqueries allow you to nest one query inside of another query, thus giving you more flexibility with your data.  There are two areas in which a subquery can be used.  The first of these areas is in the SELECT statement.  For example, if we wanted to see how many purchases a customer has made, we can run a query like this:

SELECT CustomerNumber, CustomerName,
(SELECT SUM(Quantity)
FROM Orders
WHERE Orders.CustomerNumber = Customer.CustomerNumber) as Quantity
FROM Customers

In the above example, we used a subquery on the Orders table to get the total quantity (number of purchases) and then the Customers table to get our customer information.

The second instance where a subquery can be used is in the WHERE clause.  This gives us the ability to look for a particular set of data.  As another example, let's say that we want to look for customers that have an order greater than $50:

SELECT *
FROM Customers
WHERE CustomerNumber in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

The subquery returns all CustomerNumbers that have an order of $50 or greater.  We then tell SQL to look for all those (in) CustomerNumbers in our Customers table.  We could also tell SQL to give us CustomerNumbers that are not in the list by putting the word "not" in front of "in, like so:

SELECT *
FROM Customers
WHERE CustomerNumber not in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

This would return all customers that did not have an order of $50 or more.  The word "in" in this situation is called a "logical operator", which we will discuss in my next blog.

To read more on subqueries, go here: Subquery Fundamentals

SQL Joins Part 3

I was doing some research on the CROSS join since I haven't put this join into practice.  I'm still having a hard time imagining when this would be useful.

For purposes of completeness, I will explain how a CROSS join works.

Unlike other joins, a CROSS join does not have an "on" statement where you provide columns for comparisons.  You can insert comparison columns into the where clause, but when this occurs, you are basically running an inner join.

Following is an example of CROSS join, using two tables that are not related in any way.

SELECT *
FROM Customers
CROSS JOIN Vendors

If our Customers table has ten rows and our Vendors table has ten rows, this query will return 100 rows.  Each row from the first table will show with each row in the second table.

Following are two tables that do have comparison columns, with which we will run a CROSS join:

SELECT *
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerNumber = Orders.CustomerNumber

We've essentially just run an inner join.

To read more on Cross Joins, go here: Using Cross Joins

What situations do you think a CROSS join could be useful?

SQL Joins Part 2

Yesterday we spoke about OUTER joins.  Today we will talk about INNER joins.  INNER joins will return all rows from both tables, as long as the values in the columns you are joining on match.  This differs from OUTER joins in the fact that OUTER joins could in fact return NULL values for one table.  INNER joins only return those values that match within both tables.

Going back to our example of yesterday, we will use the Customers and Orders tables as examples.  First, lets look at an OUTER join:

SELECT *
FROM Customers
LEFT JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

The above would return all Customers and any matching orders in the Orders table, even if no orders exist.  Next, we will use the INNER join, as follows:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber

This will only return data where it exists in both tables.  If a customer doesn't have any orders, no data will be returned for that customer.

As part of your column matching, you can also use math operators ( >, <, <>).  For example, if we want only orders that are over a certain amount, you would run a query like so:

SELECT *
FROM Customers
INNER JOIN Orders on Customers.CustomerNumber = Orders.CustomerNumber and Orders.Amount > 10.00

This will return only those orders where the amount is greater than $10.

To review, OUTER joins will return data for both tables, even if data doesn't exist (it will return NULLs for non-existent data).  INNER joins, on the other hand, will only return data that exists, based upon your matching columns.

To read more on INNER joins, go here: Using Inner Joins

SQL Joins Part 1

In SQL, there are three different types of JOINs, which, if mastered, can be extremely helpful in querying your data.  The three different joins are:

1. OUTER JOIN
2. INNER JOIN
3. CROSS JOIN

First, with any JOIN statement, you need to specify the columns you will join on.  This tells SQL which columns to use as comparison.  After our second table, we put the word "on" and follow that with our columns comparisons.  As we jump into this post, we will see the "on" used in our FROM clause.

Today I will focus on the OUTER Joins.

Within the OUTER Joins there are the LEFT, RIGHT and FULL JOINs.  These joins are pretty easy to understand.  A query containing a LEFT or RIGHT JOIN will return all rows in the described table (left or right) and matched rows in the other table.  This is defined by which table is on the left (the first table in your FROM clause) or on the right (the second table).  Here is an example if we are using our Customers and Orders tables:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

In this example, Customers is on the left, while Orders is on the right.  This will return all rows in the Customers  table and any matching data in the Orders table.

We can then reverse it for another example:

SELECT *

FROM Customers 

RIGHT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

The above will return all rows in the Orders table and any matching rows in the Customers table.  You can also get the same results by running the following:

SELECT *

FROM Orders

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

Notice that Orders is the "left" table and Customers is the "right" table.  It may become more evident why you would use LEFT or RIGHT when we reference more than one table.  Let's take our example and add OrderDetails into the mix:

SELECT *

FROM Customers 

LEFT JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

LEFT JOIN OrderDetails on Orders.OrderNumber = OrderDetails.OrderNumber

This query gives us all Customers with matching rows in the Orders table and then all OrderDetails matching with the Orders table.  We could accomplish the same by running the following:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

RIGHT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

We could also mix it up:

SELECT *

FROM OrderDetails 

RIGHT JOIN Orders on Orders.OrderNumber = OrderDetails.OrderNumber

LEFT JOIN Customers on Customers.CustomberNumber = Orders.CustomerNumber

This would give all rows in the Orders table with matching rows in the OrderDetails and Customers table.

A FULL Join will return all rows from both tables.  Any row from one table that doesn't have a match in the other table will return all NULLS for the other table.

SELECT *

FROM Customers

FULL JOIN Orders on Customers.CustomberNumber = Orders.CustomerNumber

To read up more on SQL Join, go here: Using Outer Joins

GOTO in SQL

I've previously talked about control-of-flow language in SQL.  Another way to control the flow of your programs is using the GOTO.  GOTO allows you to automatically jump to a particular portion of your code, while skipping other portions.

Here is a good example of what GOTO would do:

DECLARE @Counter int;
SET @Counter = 4;
WHILE @Counter < 10
BEGIN 
    SELECT @Counter
    SET @Counter = @Counter + 1
    IF @Counter = 4 GOTO Branch_One
    IF @Counter = 5 GOTO Branch_Two
END
Branch_One:
    SELECT 'Jumping To Branch One.'
    GOTO Branch_Three;
Branch_Two:
    SELECT 'Jumping To Branch Two.'
    GOTO FINISH;
Branch_Three:
    SELECT 'Jumping To Branch Three.'
 
FINISH:
SELECT 'FINISH'

You declare your GOTO section by giving it a name and then following it with a ":".  After you specify your section, you can then put in what you want to happen in that section.

You can specify in your code that you want to go to that section by saying "GOTO" and then the section name.  The above query gives the following results:

4
Jumping To Branch Two.
FINISH

Keep in mind that the program will continue to run from the current GOTO onward if no other GOTO's are specified. If we remove the reference to "GOTO FINISH" in Branch_Two, it will then move to Branch_Three and then FINISH.  Also, you can go to sections before or after the current section.  For example, from Branch_Two, you can then jump back to Branch_One and from Branch_Three you can go to Branch_One or Branch_Two.  Be careful, because if you aren't paying attention, you could put yourself in an infinite loop.

To read more on GOTO, go here: GOTO