Some Transact SQL Basics
So now let’s take a moment to examine the SQL query and understand what it is doing. You will notice that SQL has very similar syntax to the WQL queries in SMS that are automatically generated when you create an SMS query. Each query contains keywords that Query Analyzer recognizes and highlights in blue. There are numerous reserved keywords in SQL, but for the purpose of this article we are only examining a few as they are the most common ones that you will run into. The ones we will be learning are SELECT, FROM, AS, INNER JOIN, ON, and WHERE.
In order to explain these keywords, we will start with a basic overview of the entire SQL statement and then go back and explain each keyword in detail. This may seem confusing at first, but hang in there and it will make more sense in a minute. Let’s start with the entire SQL statement again; for readability, I will be highlighting the SQL keywords in blue:
SELECT ALL SMS_R_System.ItemKey, SMS_R_System.Name0, SMS_R_System.User_Name0, __System_ADD_REMOVE_PROGRAMS0.DisplayName00
FROM System_DISC AS SMS_R_System
INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey
WHERE __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = "Windows Internet Explorer 7"
Now we’ll go into each line in further detail. SELECT is the first SQL keyword we will look at as it is the first one listed in the query, along with its secondary keyword ALL. Here’s the first line of the SQL query:
SELECT ALL SMS_R_System.ItemKey, SMS_R_System.Name0, SMS_R_System.User_Name0, __System_ADD_REMOVE_PROGRAMS0.DisplayName00
The SELECT ALL keywords are followed by references in the form of Table Name or Alias.Column Name, each of which is separated by a comma. The ALL keyword is self-explanatory, but I want to point out that you can also use SELECT * to return the same results.
In our query, SMS_R_System is an alias for the table name System_DISC which is defined in the next SQL statement using the FROM and AS keywords in the next line. Additionally, we are displaying the DisplayName00 column from a table aliased as __System_ADD_REMOVE_PROGRAMS0. This alias is defined in the INNER JOIN statement which we will explain shortly. For now, we’ll look at the FROM statement next and then tie these two lines together. The FROM line is as follows:
FROM System_DISC AS SMS_R_System
This one’s easy; we’re selecting our columns from a table named System_DISC but from now on we are referring to it using the alias SMS_R_System. Let’s take a look at what we have so far in terms of the aliases, the red arrows show where each alias is used in the SELECT statement:
Sorry about the mess; this obviously isn’t an art class, but hopefully you get the idea. I really need to stop using Microsoft Crayon and learn how to use Microsoft Paint instead!
Next we will look at INNER JOIN. There are several different types of joins in SQL, but for simplicity we will keep it limited to just this one. Basically, what an INNER JOIN does is to join two or more tables or views and display data from both of them. The ON keyword specifies which two columns that the join is linked on. Let’s examine what our INNER JOIN statement is doing in our query:
INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey
First we are taking the table named Add_Remove_Programs_DATA and aliasing it as __System_ADD_REMOVE_PROGRAMS0. The ON keyword is then linking the MachineID column from the table aliased as __System_ADD_REMOVE_PROGRAMS0 to the ItemKey column in the table aliased as SMS_R_System.
The last line with the WHERE keyword simply states that the only rows in the database that should be returned are the ones in the __System_ADD_REMOVE_PROGRAMS0 table where the data in the DisplayName00 column is equal to Windows Internet Explorer 7. This line looks like this:
WHERE __System_ADD_REMOVE_PROGRAMS0.DisplayName00 = “Windows Internet Explorer 7”
In summary, what our SQL query is saying is to display the columns named ItemKey, Name0, and UserName0 from a table called System_DISC which is aliased as SMS_R_System. We are also displaying another column named DisplayName00 from a table aliased as __System_ADD_REMOVE_PROGRAMS0, and this table is joined to the other table. This query will only return the rows where the __System_ADD_REMOVE_PROGRAMS0 table has data in the DisplayName00 column that is equal to Windows Internet Explorer 7.
So hopefully you now have at least a basic knowledge of what the SQL statement is doing, and you’re ready to start learning about SQL views which we will cover in Part 4. But before I close this article out, I have one more helpful tip: If you ever run across another keyword or want to look up more information on one of the ones that we’ve covered, Query Analyzer has context-sensitive help so you can highlight a particular keyword in a query then hit SHIFT+F1 to display help on that particular keyword. SQL Books Online has to be installed in order for this to work properly, which can be done by re-running the setup utility.