If you've been reading these Database Fundamentals posts, you've already seen the WHERE clause because of your use of it when manipulating data with DELETE and UPDATE statements. It's also been used several times earlier in this series to limit the values returned from a SELECT statement. The primary places where people run into trouble with T-SQL is in the JOIN criteria and the WHERE clause criteria. This occurs because they don’t understand well enough what the filters and operators they’re using will do. They end up returning too much data because they didn’t us the WHERE clause or misapplied it. They also filter too much data out. Just remember, there are even more functions than we go over here in this series. While these basic operators answer most needs,…
While the CROSS JOIN is not used much, and, depending on the size of your data it can be dangerous, there are some uses for it. For example, you may want to write a query that will summarize all the sales for the companies and people in your system. You can do this using what is called an aggregate operation or a GROUP BY: SELECT c.CompanyName, p.LastName, SUM(ft.TransactionAmount) AS 'TransactionTotals' FROM Management.Company AS c JOIN Finance.FinancialTransaction AS ft ON c.CompanyID = ft.CompanyID JOIN Personnel.Person AS p ON p.PersonID = ft.PersonID GROUP BY c.CompanyName, p.LastName; This will add all the values up in the SUM operation for each company and each person that has values so that your data will look like this: The only problem with this is, you can’t…
The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you have to include whether or not you’re dealing with a RIGHT or a LEFT JOIN. The OUTER word, just like the INNER key word, is not required. OUTER JOIN Imagine a situation where you have a list of people. Some of those people have financial transactions, but some do not. If you want a query that lists all people in the system, including those with financial transactions, the query might look like this: SELECT p.LastName, ft.TransactionAmount, ft.TransactionDate, ft.TransactionTime FROM Personnel.Person AS p LEFT JOIN Finance.FinancialTransaction AS ft ON p.PersonID = ft.PersonID; Except for the addition of the LEFT key word, this…
It is entirely possible to try to JOIN two tables on almost any field, as long as the two data types can, in some way, be made to reconcile to each other, you can try to join the tables. But, most database designs assume a much more directly relationship and provide a column or columns in one table that match the identifying column or columns in the other table. INNER JOIN The INNER JOIN will return the parts of both data sets that match. Frequently, what you'll see when joining two tables is the same column name in each table. With that, you have to be sure to identify the owner of each column. I've introduced what is called an alias to make it so I don't have to type…
The last Database Fundamentals post introduced the SELECT and FROM commands. We're going to start using JOIN operations shortly, but first, let's explore the idea behind joins. The very concept of relational storage that is the foundation of SQL Server requires you to related one table to another. You do this through a operation called JOIN. There three basic types of JOINS, INNER, OUTER, and CROSS. Think of them like this. It’s all about relationships. The relationships are only ever between two sets of data. Yes, you can combine lots of tables together through a query, but each JOIN relationship will be between two sets of data. Types of Joins If you take two sets of data and represent them as two circles, they might look like this. An INNER…
In my previous Database Fundamentals post, I showed you how to use the Query Designer to build a query. That was a SELECT statement. The basic construct of all your SELECT statements will be the same. You’re going to define a list of columns, the table or tables you’re interested in, and some sort of filter criteria. That’s the bare bones basics of how it works. But, as we all know, the devil is in the details. There are lots and lots of details. This section will introduce the T-SQL SELECT statement and start explaining some of those details. Column List You’ve been introduced to the basic concepts of the column list in the SELECT statement. It represents the information that is going to be available for display by whatever…
While SQL Server Management Studio (SSMS) provides a robust graphical user interface (GUI), the commands you're going to use the most to control databases and the data within them in SQL Server are going to be done through T-SQL. Therefore, you really need to spend time learning how to write, read, and edit T-SQL. Previous posts in the Fundamentals series have showed how to INSERT, UPDATE and DELETE data using T-SQL. Next, we're going to learn SELECT. However, I want to show you a crutch you can use as you get started learning how to write T-SQL, the Query Designer window. The instruction on this topic is only meant to provide a mechanism to focus on the more important topic, learning T-SQL. However, this may be an easier path for…
Deleting data from a table using T-SQL works quite a lot like the UPDATE statement. How it Works In the same way you supply the statement, DELETE, and then the table name. You’re not going to specify columns in any way because deleting data is all about removing a row. If you just wanted to remove the values in a column, you would use the UPDATE statement. Because of this, the only other thing you need for a DELETE statement is the WHERE clause. Just like with the UPDATE statement, if you don’t supply a WHERE clause, then the DELETE statement will remove all data in the table. Be very careful about using this statement. Make sure you’ve always got a WHERE clause. This example would delete all the rows…
The preferred method for modifying your data within a database is T-SQL. While the last Fundamentals post showed how to use the GUI to get that done, it's not a very efficient mechanism. T-SQL is efficient. UPDATE The command for updating information in your tables is UPDATE. This command doesn’t work the same way as the INSERT statement. Instead of listing all the columns that are required, meaning columns that don’t allow for NULL values, you can pick and choose the individual columns that you want to update. The operation over-writes the information that was stored in the column with new information. In addition to defining the table and columns you want to update, you have to tell SQL Server which rows you’re interested in updating. This introduces the WHERE…
I've said it before, but I feel I should repeat myself. Using the SSMS GUI for data entry and data manipulation is not the preferred mechanism. T-SQL is the right way to manipulate the data in your database. For purposes of completion though, I will show the GUI methods in this blog series. Information doesn’t go into the database and stay there, unchanged, forever. Data is modified. This occurs because information changes, such as when a person marries and changes their name, or information was incorrectly entered, in which case you need to fix it, or just about anything else. You have to have a mechanism for modifying existing information. Modifying Data You start modifying data in the tables the same way you did the insert, by taking advantage of…