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

Question of the Day 2

I've had my second Question of the Day presented on SQLServerCentral.com.  To visit the question of the day, go here: SQL CHOOSE

To re-read my post on SQL CHOOSE, go here: SQL CHOOSE Post

You can also join the discussion on the question by going here: SQL CHOOSE Discussion

I'm certainly learning a lot about what needs to be considered when putting together these questions.  With this question of the day, I've found out that people outside of the U.S. are answering.  Because of this, they are using a different language for SQL Server than me, which apparently affects how different data is presented.  In my case, dates are presented differently depending on the language you use and this caused more people to get unexpected errors.  This is a good thing to consider in case you are working with people overseas.

Using APIs

I've been spending much of my time recently using APIs (application programming interface) at work, so I haven't had much time to research SQL topics.  For any of you who don't know, APIs are used as a way into an application without providing the code/table diagrams underneath.

For example, if I, as a programmer, want to give people access into my database, but not allow them in directly, I would give them access to an API.  APIs are used for inserting data into the database or retrieving information without giving away what's in your database.

Anyway, I've been spending time recently trying to use some of the APIs from our billing vendor to see if I can increase my work performance.  I'll keep you informed of anything I think worthy of note.

Thanks.

SQL Views

I've briefly spoken about SQL Views in prior posts (see here), so I wanted to take this opportunity to talk a little more about SQL Views.

First, what is a SQL view?

A view is just exactly what it sounds like: it allows you to "view" information.  Views can and are usually created to present data in a certain way.  It is what we may call a virtual table, meaning that the table doesn't actually reside in the database the way it is presented in the view.

A view allows you to take a T-SQL query, spanning across multiple tables if needed, and present it as a view.  Then, someone can write a query using the view and get all the data they need.  This helps to avoid writing the same query multiple times.  The query is already written as a view.

An example might help us better understand the concept.  Let's say that we have three different tables: Customer, Orders and OrderDetails.  We use the following query to get information for an order:

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

It can sometimes be difficult to remember the relationship between each table, so instead of creating the same query over and over again, we create a view.  We create a view using the following syntax:

CREATE VIEW [dbo].[vOrderDetail]

AS

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

Now, when you want to get the information for a customer order, your query can look like this:

SELECT *
FROM vOrderDetail

To avoid confusion, it is a good practice to name your views differently from your tables.  In my example, I put a lowercase "v" at the beginning of the view name so I know I am using a view.

Once you've created your view, now you can give people access to views.  These are used for just viewing data, users can't update or delete these records.  The deletion or updating of records has to be done on the tables the view refers to.

To read more on views, go here: SQL Views

Querying NULL Values in SQL

When querying a SQL database, you need to be aware of the presence of NULLs.  In SQL, a NULL represents an unknown, non-existent or empty value.  Sometimes the NULL value is put in explicitly or no values was specified for the column, so a NULL was entered.  The reason we need to be careful is we cannot query NULLs the way we do for data types.

For example, let's say that you wanted to query a customer's balance where it is zero (either the customer has paid his balance or he hasn't made any purchases).  Since this would be a money or other number type, you could run a query, like so, to get all your customers:

SELECT *
From CustomerTable
where Balance = 0

But, depending on how you set up your table, new customers may not have a value in the Balance field.  Therefore, this value could be a NULL.  The above query would not return a NULL value, so you aren't getting all the results you require.

There are two ways you can update your query.  Here is the first one:

SELECT *
From CustomerTable
where Balance = 0 or Balance IS NULL

To query on NULL values, you need to specify IS NULL or IS NOT NULL.  This is the only way to query NULLs directly.

The other way to query NULLs is the following.  Keep in mind that this will work, while also making your query NonSARGable:

SELECT *
From CustomerTable
where ISNULL(Balance, 0) = 0

ISNULL() is a function that allows you to convert NULLs in your field to a specified value.  ISNULL() is used like this:

ISNULL( column, value to replace NULL)

We replaced our NULLs with the value of 0.  We can then query all 0s using one simple clause.

To read more on NULL values, go here: NULL Values

Index Design Tips

I've written previously about SQL Indexes.  I've been reviewing tips on Index Design and came across the following Microsoft site:

General Index Design Guidelines

A few things I thought were interesting in the article:

• A large number of indexes on one table can affect your Update, Insert and Delete statements.
• Keep the number of indexes or index columns small on these tables.
• It may be a good idea to create a view and create indexes on the view rather than on the table that will receive Update, Insert of Delete.  This would allow you to modify your original table, but view the data while using a cluster.
• Indexing small tables is not always optimal, since it may take longer to search through the indexes than to find the records.
• Create nonclustered indexes on columns that are referenced frequently in your queries.
• Order the columns in your index based upon how often those columns are used.  For example, if column c is used more than b and b is used more than a, then the column order in the index should be: c, b, and a.
• Columns that are referenced in query using exact matches could be a good candidate for clustered or nonclustered indexes.

These are all good pointers and should get any person inexperienced in creating indexes started.  From there, you can usually play around with different indexes until you find the right ones.

What types have you found while creating indexes on your database?

IF....ELSE in SQL

Every programming language has flow of control ability.  What I mean by this is the language allows you to check your data and make a decision based on what is returned.  One of the most popular ways to control the flow of your program is an IF/ELSE statement.  An IF/ELSE statement allows you to check a value and IF it matches a certain criteria, do something, ELSE do something else.

The basic look of an IF/ELSE statement looks like this:

IF expression
    do this
ELSE
    do this

Let's say that we are building a table of customer data.  Before we continue, we want to check and see if we have any customers in our table.  If we do, print one message or else we print another message:

IF (select count(*) from CustomerTable) > 0
    PRINT 'We have customers'
ELSE
    PRINT 'We dont have customers'

If your block statement after the IF or ELSE does more than one thing, you will need to insert a BEGIN and END at the beginning of each block.  Below is an example:

IF (select count(*) from CustomerTable) > 0
BEGIN
    PRINT 'We have customers'
    PRINT 'That is great'
END
ELSE
    PRINT 'We dont have customers'
    PRINT 'Isnt that sad?'
END

These are very simple examples.  You can do much more than print messages to the screen.  You can also update data, delete data, select data, and much more.  Below is one example:

IF (select count(*) from CustomerTable ) > 0
BEGIN
    delete from CustomerTable
    PRINT 'We just deleted our customers'
END
ELSE
BEGIN
    PRINT 'We dont have customers'
    PRINT 'Isnt that sad?'

END

To read more on IF/ELSE statements, go here: SQL IF/ELSE

Using CONCAT in SQL

In my line of work, we regularly put different fields together for representation to our customers.  There are two ways we can approach this in SQL:

• Concatenate the fields together using the "+" sign
• Using the CONCAT() function

Both of these approaches give the same results in most cases, so why should you use one solution over another?  I would first say because of how it looks.  For example, if we want to concatenate the first name and last name together, here is what our two solutions would look like:

Using "+" sign:

SELECT FirstName + ' ' + LastName from Customers

Using CONCAT() function:

SELECT CONCAT(FirstName, ' ', LastName) from Customers

This is a pretty simplistic example.  If we use something with more fields, it may become more evident.  For example, let's say that we have three different fields for a customer's phone number, we could put the three fields together like so:

Using "+" sign:

SELECT '(' + AreaCode + ')' + CityCode + '-' + Suffix from Customers

Or

Using CONCAT() function:

SELECT CONCAT( '(', AreaCode, ')', CityCode, '-', Suffix) from Customers

Another reason to use the CONCAT() function is that integer values are automatically converted to string.  If, for example, you put numbers together when using your "+" sign, SQL will add the two values together instead of concatenating them:

This returns the value of 3:

SELECT 1 + 2

This returns the value of 12:

SELECT CONCAT(1, 2)

In the first example, you will need to make sure you are converting integer values to a string, which could be quite cumbersome.

To read more on the CONCAT() function, go here: SQL CONCAT

Rounding Up in SQL

Working in the telecommunications industry, you run into situations not always found in the real world.  For example, when billing a call or applying taxes to an account, you always round up.  We can't charge a customer anything less than 1¢ (since they can't pay less than 1¢), so you have to round up.

SQL has a function that allows you to perform rounding on your values.  This function is called ROUND(), but only does true rounding (below 5 = round down, above 5 = round up).  So using this function, how do we always round up?

First, we have to figure out how many decimal places we will be billing our customer.  Let's say, for example, that our customer's calls are rounded to the nearest two decimals.  Once we know how many decimals we need to round, we can apply our logic.  The logic is then to add 49 one digit past the decimal place you are trying to round.  In this case, since we want to round to the nearest two decimal places, we will add $0.0049 to our value and then round.  If we wanted to go three decimal places, we would add $0.00049, $0.000049 to four decimal places, etc.

The following will round to $0.01:

select ROUND(0.013, 2)

But if we want to always round up, we need to add $0.0049:

select ROUND(0.013+0.0049, 2)

The above rounds to $0.02.

Now, you might be asking yourself: why do we add $0.0049 instead of $0.005?  Well, adding $0.005 might inadvertently makes us round higher than we want to go.  Look below for an example, which will round up to $0.03:

select ROUND(0.020+0.005, 2)

When we actually want it to stay at $0.02:

select ROUND(0.020+0.0049, 2)

I've found it is more productive to add $0.0049 to the number and then round, as opposed to trying to figure out a way for SQL to round up only those values that need it.

To read up more on rounding, go here: SQL ROUNDING