Tag Archives: T-SQL

Advanced Query Techniques

Thumbnail for 578

The T-SQL INSERT, UPDATE, and DELETE statements provide a lot of power and flexibility for maintaining data in a relational database. But they can also be unwieldy. Say that you have a table with rows that you need to use to update another table. Depending on the data in the source table, you might have to update or delete an existing row or, if no related row exists, insert it into the table. This can be unwieldy, requiring several statements to perform the various actions. And it can be a pain to get all the comparisons working just right.

The MERGE statement addresses this issue. It lets you insert, update, and delete table data with just a single statement, based on the similarities and differences in the data. It joins a data source table with a target table or view to perform the various actions. The operation it performs depends on the results of the join:

  • If data exists in both the source and the target, update the changed data in the target.
  • If data in the source doesn’t exist in the target, insert data into the target.
  • If data exists in the target but not in the source, delete the data from the target.

You have full control over how you define whether and how each of these actions will occur for a particular row in the source table. The MERGE statement is transactional, so you don’t need to explicitly define a transaction.
If any of the operations fails, the entire statement rolls back.

  • The MERGE statement uses five clauses:
  • The MERGE clause specifies the target data, such as a table, view, or other query.
  • The USING clause defines the source data.
  • The ON clause specifies how to join the tables, much like a regular JOIN clause.
  • One or more WHEN clauses define the actions to take based on the join matching.
  • The OUTPUT clause returns a row for each action taken.

The WHEN clause is where all the actions occur, and you’re likely to have more than one in a typical MERGE statement. The WHEN clause has three forms:

  •  WHEN MATCHED [AND condition]

Here is where you either update or delete an existing row in the target
data. You can have at most two of these clauses, and if you have two,
one must have a condition associated with it. You can define either an
UPDATE or DELETE statement in this clause. If you’re using an
UPDATE statement and more than one row in the target matches the
statement, you get an error, and you can’t update any row in the target
more than once. Similarly, you can’t update and delete the same row.

  •  WHEN NOT MATCHED [BY TARGET] [AND condition]

This is where you can use an INSERT statement, when a row in the source doesn’t match any row in the target. You can have only one of this WHEN form in any MERGE statement. The BY TARGET clause is optional, because it is the default.

  •  WHEN NOT MATCHED BY SOURCE [AND condition]

This form of the WHEN clause kicks in when rows in the target don’t match a row in the source; in this case you can either update or delete the row in the target. You can have at most two of these in a MERGE statement, and the second must have a condition associated with it. 

The optional condition on some of the versions of the WHEN clause can be  just about anything you can include in a WHERE clause. You have to include  at least one WHEN clause in the MERGE statement, although the order you include the different forms doesn’t matter at all. What does matter is that if you include the same form with and without a condition, the clause with the condition must be first.

Any triggers that exist on the affected target table will fire normally, but there is no guarantee of the order in which triggers fire if multiple operations are performed on the target table. So be careful of side effects. All normal permissions apply, so the user or security context must be able to perform the various actions.
ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Schemas and Naming in SQL Server

Whenever you execute a data selection query in SQL Server, you’ll be accessing one or more database objects, so it is important that you understand how things are named. In SQL Server, database object names use a convention that can contain four parts, any of which can be blank, except the object name:

  • The server_name specifies linked server name or remote server name. A blank implies the current server.
  • The database_name specifies the database name. A blank implies the current database context.
  • The schema_name specifies the name of the schema that contains the object. A blank implies the default schema for the current user or the dbo schema if no other default schema is assigned to the current user.
  • The object_name specifies the name of the object.

In most situations, it is not necessary to use all four parts. However, the recommendation is to use the schema name with the object name, as shown in the following two examples. The first example for the Northwind database will work with or without dbo, because the server uses dbo when no schema is explicitly defined and no default schema is explicitly assigned to the current user. The second query for the AdventureWorks2012 database will fail if the
schema name Sales is omitted, unless the user has Sales set as her default schema. In this case, the Store table was created in the Sales schema. This query will work only for a user with Sales as the default schema.

It might be tempting to deal with schemas by keeping all database objects assigned to dbo and avoid creating or assigning any other schemas. However, schemas can be a useful way of creating multiple namespaces in a database, just as namespaces make it easier for .NET programmers to keep track of classes. The AdventureWorks2012 database provides a good example of using schemas as namespaces. You can also assign permissions on a schema that grant the permission to all objects within it, which makes schemas a powerful security tool for protecting data access. 

NOTE

In versions before SQL Server 2005, a schema was created automatically for each database user. When a user created an object, the object was automatically created in that user’s
schema—unless the user was a database owner, in which case the object was created in the dbo schema. In more recent versions of SQL Server, each user does not automatically have a schema. Schemas are created independently of users; users must explicitly be assigned rights to a schema and can be assigned a default schema. Many users can have rights to use any schema and many users can have the same schema as their default.

 

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Select, Where and Clauses course written by expert Don Kiely. Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

 

Introduction to Stored Procedures

A stored procedure is a batch of Transact-SQL (T-SQL) code that is saved internally in SQL Server as a database object. You can use stored procedures to mask complex SQL tasks from clients, improve the performance of your application, enforce data and business rules, increase multi-user concurrency, and reduce locking and blocking conflicts. It is a module of T-SQL code that can perform just about any task in SQL Server, accessing data as well as
resources outside of SQL Server. A stored procedure is the workhorse of SQL coding, and in this chapter you’ll learn about how to use this important database feature.

Stored Procedure Features

Unlike scripts, which are saved outside your database as text files, stored procedures are saved as objects inside your database. You can execute a stored procedure from a client application, a batch or script, or from other stored procedures. Stored procedures are parsed and syntax-checked when you create them and are compiled on first execution. SQL Server caches the execution plan and shares it among all users of the database. Subsequent calls to the stored procedure can reuse the cached plan, even if different users call the stored procedure. This compilation, caching, and sharing of execution plans gives stored procedures a significant performance advantage over running ad hoc T-SQL statements. You can also lock down the security of your database by revoking permissions on database objects and only granting access to data and objects through a stored procedure.

Unlike views, which are also saved as database objects, stored procedures support the full capabilities of Transact-SQL. A single stored procedure can contain up to 250 megabytes of text, a limit you won’t encounter anytime soon (or, if you do, you should seriously consider refactoring your code!) Stored procedures can accept input parameters and can return more than one result set, and they can also return data in the form of output parameters and a return value. Stored procedures are as close as you can come to functions or methods that you’ll find in full-featured, object-oriented programming languages.

TIP: SQL Server uses stored procedures in most of its internal operations. System stored procedures have a schema name of sys and an sp_ prefix. You can read the Transact-SQL definitions of these stored procedures by running the sp_helptext system stored procedure and supplying the stored procedure name(for example, sp_helptext ‘sys.sp_tables’). You’ll find that most contain explanatory comments as well. Exploring system stored procedures is a great way to learn advanced T-SQL programming techniques once you’ve mastered
the basics.

The only tasks that you cannot complete with a stored procedure are the creation of triggers, defaults, rules, other stored procedures, and views. A stored procedure can do everything from executing a basic SELECT statement to enforcing complex business logic and explicit transactions, as well as modifying data and other database and system objects.

 

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Stored Procedures course written by expert Don Kiely.Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Overview of Transact-SQL (T-SQL)

Transact-SQL is the SQL Server implementation of SQL, a standard codified
by the American National Standards Institute (ANSI) and also adopted by the
International Organization for Standardization (ISO). No single vendor has
fully implemented every part of the ANSI/ISO standard, and each vendor has
added its own proprietary extensions to the language, so you’ll find plenty of
things in Transact-SQL (T-SQL) that you won’t find in other database
products. The standard is updated roughly every three or four years, and so is a
moving target that no SQL product ever fully implements.

The SQL language came about as a result of the work that Dr. E. F. Codd did
in the 1960s on his Relational Database Model. The first version of the
language was known as SEQUEL. It was then completely rewritten in the
seventies, and eventually became known as SQL for Structured Query
Language, because it turned out that the acronym SEQUEL had already been
trademarked. The original “sequel” pronunciation has stuck to this day–SQL
Server is still widely referred to as sequel server, although some purists insist
that the language name should be pronounced ess-que-ell. However you
pronounce it, the SQL standard has been relatively well received and is the
most widely supported standard today.

Transact-SQL (T-SQL)

Transact-SQL is Microsoft’s implementation of the SQL language in SQL
Server. The language implements a significant subset of the features of
standard SQL, as well as some very useful extensions to the ANSI standard
that add procedural capabilities, which makes Transact-SQL more like a
programming language. There are control-of-flow features, such as IF…ELSE
syntax and WHILE loops, as well as support for variables, parameters, and
user-defined functions. Like other programming languages, Transact-SQL
supports built-in functions for manipulating strings, numbers, and date/time
information, and for returning system information.

Although Transact-SQL has programming language features, you’d never want
to use Transact-SQL to replace a general purpose programming language. It

has no user interface and its programming constructs are very limited. The
main advantage to programming in Transact-SQL is that your routines execute
on the server and are highly efficient for set-based data operations. Transact-
SQL provides the building blocks for all your views, stored procedures, userdefined
functions, and triggers. When you perform as much processing as
possible in Transact-SQL, performance improves because less data has to
traverse the network for processing on the client.

T-SQL has two broad sets of statement types: data definition language (DDL)
and data manipulation language (DML) statements. DDL statements let you
create and update database and server objects, while DML statements let you
work with the data itself to create, retrieve, update, and delete rows of data.
These four operations are so common in relational databases that they are often
collectively referred to as CRUD operations.

Microsoft adds a number of new T-SQL features in every new version of SQL
Server, some of which are proprietary to that product, while others are
implementations of the features in the SQL standard. This chapter focuses on
the fundamentals of the T-SQL language.

NOTE

SQL Server hosts the .NET Common Language Runtime (CLR),
which allows you to write code in any supported .NET language,
such as C# or Visual Basic. You can use Visual Studio to write
programs that are then compiled into assemblies and loaded into
SQL Server. CLR procedures are designed for code that is
processor-intensive, such as complex mathematical or string
manipulation. The CLR is not designed to replace Transact-SQL,
which is always the best choice for data access and for set-based
operations. Any time a CLR procedure performs data access, it
uses Transact-SQL “under the covers.”

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Windows Logins via Transact-SQL

Transact-SQL provides full support for creating logins by using the CREATE LOGIN statement. The following example grants login privileges on SQL Server to the Windows user JaneAppDev on a machine named Willow (you’ll need to change the machine name if you want to try the code yourself). The brackets around the Windows login are required.

This statement adds the login but does not provide database access. You do that by using the CREATE USER statement. To add Jane to the AdventureWorks2012 database and make Production her default schema, add the following code:

 

You don’t have to name the database user the same as the server login. For example, you could instead name the user Jane in the database, assuming that it does not already contain a user Jane:

In this case, because the default schema wasn’t changed in the CREATE USER statement, the default would be dbo.

You can change a user account with the ALTER USER statement. For example, if you later wanted to make Jane’s default schema Production, you could use this code:

TIP: Only one user in any given database can be mapped to a single Windows login. A single login may, however, be mapped to users in many databases on the same SQL Server instance.

If you add one of the built-in Windows groups to SQL Server (Users, Power Users, etc.) use BUILTIN in the Windows login name instead of the machine or domain name. The following example grants login privileges to anyone in the Windows Users group. This means that anyone who can log in to Windows doesn’t have to log in a second time to get into SQL Server.

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server 2012: Using Snapshot Isolation

2.0-bigsqllogoAs discussed in our previous article, concurrency that is based solely on locking can cause blocking and deadlocking problems. However, reading data with READ UNCOMMITTED is not a solution for applications that require row-level accuracy when retrieving data; dirty data is generally not a good thing. To solve this problem, SQL Server includes a row versioning mechanism called snapshot isolation, which is intended to improve performance by avoiding the reader-writer blocking scenarios in the previous example.

Read operations do not request shared locks on the data, so transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level.

The term “snapshot” reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits transactions to execute without being blocked by a prior incomplete transaction.

NOTE: Two write operations do block each other even while running under row versioning-based isolation levels, because two write operations cannot modify the same data at the same time.

A unique transaction sequence number identifies each transaction. Whenever a transaction modifies a row, an image of the row before modification is copied into a page in tempdb. If multiple transactions modify a row, multiple versions of the row are linked in a version chain.

When a user or application retrieves data, it automatically gets the last saved version of each row.

Snapshot Isolation Caveats

Snapshot isolation is not recommended for every application. Here are several reasons why READ COMMITTED might be a better choice for your application:

  • READ COMMITTED consumes less tempdb space than snapshot isolation.
  • Snapshot isolation does not work with distributed transactions that span servers.
  • Snapshot isolation is vulnerable to update conflicts that do not apply to READ COMMITTED. When a transaction that’s running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back. This is not an issue with READ COMMITTED.

Enabling Snapshot Isolation

You enable snapshot isolation per database by turning on the ALLOW_SNAPSHOT_ISOLATION database option.

This allows snapshot isolation when it is explicitly invoked, but the default READ COMMITTED transaction isolation level remains in effect for implicit transactions that do not specify snapshot isolation.

To replace READ COMMITTED entirely, turn on the READ_COMMITTED_SNAPSHOT option in addition to the ALLOW_SNAPSHOT_ISOLATION option. Snapshot isolation will then be used for all transactions.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server 2012: Blocking and Deadlocks

When you modify data in SQL Server, it is important to understand how isolation levels and locking can affect performance and concurrency. The term concurrency refers to the ability of multiple users or applications to access the same data. When you update data, concurrency conflicts occur when multiple users select data and then try to update the data simultaneously. SQL Server places various kinds of locks on database objects and table rows in order to ensure data integrity.

One problem is that locks on the data can delay the updates. Another problem is that users may be able to update data that has changed since they last inspected the data. Applications often prevent this by adding a WHERE clause to the update to ensure that the update will succeed only if the data in the database still has the values that the user originally retrieved. The following article discusses Blocking and Deadlocks.

Blocking and Deadlocks

SQL Server is very efficient at applying and releasing locks. If you are modifying data using the default READ COMMITTED isolation level, locks will be applied and quickly released on each row of data. But anytime a resource is locked in SQL Server, it may cause other processes to be unable to access data necessary for the other processes to perform its actions. Two situations you have to be aware of and prepared for are blocking and deadlocks.

Blocking

Blocking occurs when locks are held for too long. When a transaction is blocked because another transaction has locks on data, it just sits there and waits. Over a network, blocked transactions will either hang or eventually time out with an error message.

You can reduce the chance of common blocking problems by avoiding user interaction in the middle of transactions, keeping data modifications short, and avoiding recursive operations and nested triggers.

Deadlocks

A deadlock occurs when two separate processes are each holding a resource that the other needs. Each process is waiting to release the resource it is holding until the other resource becomes available. Unless one of the processes is forced to yield, they will stay deadlocked forever. Deadlocks are difficult to simulate in a development environment; they seem to appear only when your database is running and many users attempt to complete the same operation at the same time.

The Figure below displays how a deadlock works. In this case, Transaction 1 has a lock on Resource 1 and needs a lock on Resource 2 to perform its work. Transaction 2 has a lock on Resource 2 and needs a lock on Resource 1 to perform its work. Transaction 1 can’t get a lock on Resource 2, and Transaction 2 can’t get a lock on Resource 1. So the transactions sit, deadlocked, waiting for the other transaction to let go of its locks.

Two transactions in a deadlockSQL Server uses an interval to determine which processes are running and which are blocked. If this interval passes twice and a process is still blocked, SQL Server chooses a deadlock victim. The victim’s transaction is rolled back, and error code 1205 is returned to the client application of the losing transaction. Your error handling routine in the client application can test for error 1205 and resubmit or cancel the query. If SQL Server did not select a deadlock victim, then eventually your server would run out of available processes and crash.

To avoid deadlocks, access objects in the same order every time, so that if a process is blocked it goes into a wait queue until the process that is holding the lock is complete.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Updating Large Value Types with UPDATE .WRITE

SQL Server provides an UPDATE .WRITE Transact-SQL statement to perform partial updates on columns that are defined as varchar(max), nvarchar(max), or varbinary(max). Here is the abbreviated syntax:

The following example uses the Production.Document table in the AdventureWorks database to demonstrate substitution of the word “critical” for the word “important” in one row of data with a DocumentNode value of 0x5B40.

First, select the row to view the existing data:

The first sentence in the results should look like the Figure below (you’ll need to expand the width of the column to view the entire text of the column).

Updating Large Value Types with UPDATE .WRITE

To change the word “important” to the word “critical” you need to specify the offset (the number of characters from the start of the string to the value you want to replace) and the length of the string to replace.

If you execute the SELECT statement again to view the current value, you’ll see the results as shown in the Figure below.

Updating Large Value Types with UPDATE .WRITE

Execute the following statement to restore the original value:

You can also use the CHARINDEX and PATINDEX functions to calculate the offset location of the substring you want to change. The LEN function returns the number of characters in a string, so the following statement performs the same replacement of ‘critical’ for ‘important’. (In the sample code file, these statements are wrapped in a transaction that is rolled back, so you don’t have to worry about executing another UPDATE statement to undo the change.)

WARNING! In past versions of SQL Server, WRITETEXT was used to replace sections of long text values. SQL Server 2012 still supports this statement, but it has been deprecated and will be removed in a future version of SQL Server. Therefore, you should always use the new .WRITE clause of an UPDATE statement rather than using WRITETEXT.

 

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SQL Server 2012: Creating a New Table with SELECT INTO in T-SQL

2.0-bigsqllogo

The SQL standard draws a distinction between Data Manipulation Language (DML) and Data Definition Language (DDL). DML statements manipulate and maintain data, while DDL statements change the structure and properties of database objects. SELECT statements are considered to be part of DML, even though you might not think of selecting data as a form of “manipulation.”

When you retrieve data with a SELECT statement, SQL returns a result set. However, any actions you take on that data (besides just looking at it) involve data manipulation queries that use one of the following three T-SQL statements: INSERT, UPDATE, or DELETE. SQL queries that use these statements are sometimes known as action queries.

SQL Server was designed to handle data modification extremely efficiently via T-SQL DML statements. This article takes a look at the SELECT INTO statement for creating new tables using T-SQL.

SELECT INTO

The SELECT INTO statement creates a new table with the columns necessary to contain all the values that the SELECT statement returns. The following query creates a new table called Produce, which contains all of the rows that match the produce category in the Products table:

If you run the statement again, you’ll receive an error that the Produce table already exists.

NOTE: When you create a new table with SELECT INTO, the new table schema will match the original schema, including identity columns. The Produce table will have a ProductID that is an identity column. The nullability of columns is also copied from the source, but indexes, constraints, and triggers are not copied.

Temporary Tables

Although the SELECT INTO syntax allows you to create tables in a database, you probably won’t want to use this technique very often in applications. When you allow users to create tables at will, problems can occur with backups, security, and database clutter. But sometimes you’ll want to create temporary, or temp, tables as a way to store data that’s necessary for processing but is not required afterward.

You can create temp tables so that only the connection that creates them can access them. Once that connection ends, SQL Server automatically destroys the tables, which eliminates all of the problems with persistent tables. Temp tables are stored in the tempdb database, and are identifiable by the first character of their name: the pound symbol (#).

NOTE: Temp tables created in stored procedures are destroyed when the stored procedure terminates.

The following SELECT INTO query creates a temporary table named #Produce:

Namespace conflicts won’t occur if multiple users create a #Produce table, since every connection can have its own #Produce in tempdb. Each #Produce table in tempdb has a suffix with an underscore and the connection information to keep them separate. To select data from the temp table, use the following query:

Another way to create temp tables is to use the standard CREATE TABLE syntax, as shown in the following DDL statement:

The following query inserts data into the temp table. Note that the data types you explicitly define in the temp table must match the data types in the table you are selecting data from:

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

T-SQL Fundamentals: Outer Joins

One of the fundamental concepts of relational databases is the normalization of data into tables, or sets, of similar data. Different data elements are grouped into separate tables. Data about employees is in the dbo.Employees table, data about orders is in the dbo.Orders table, and so forth. The process of organizing the various elements of data into tables is called normalization. The key measure of successful normalization is the ability to join tables effectively.

As the SQL standard has evolved, so has the join notation, both inner and outer. The earliest join notation uses the WHERE clause to enforce the joining criteria. This article takes a deeper dive into outer joins.

Outer Joins

An outer join includes all of the rows from either or both of the tables in the join, even if there is a row in either table that doesn’t have a match in the other table. Three types of outer joins are possible: left, right, and full.

  • A left outer join includes all rows of the first table, even if there is no matching row in the second table.
  • A right outer join includes all rows of the second table, even if there is no matching row in the first table.
  • A full outer join includes both tables so that all rows of both tables are part of the result set even if there isn’t a match in the other table.

The “left” and “right” directions simply reference which table you list first and second in the join clause, respectively.

The challenge of using outer joins is to know when they are appropriate, and which one to use. The following query uses an inner join to generate a list of all customers and the date of each customer’s first order. We’ll use this query to show how the different outer joins work for this relationship between the Customers and Orders table. A second query returns the total number of Customers.

The query joins the Customers table to the Orders table and groups the results by company name. The MIN aggregate function returns the lowest order date for each customer. The first few rows of the result set shown in the Figure below display each customer and the date of their first order. The query returns 89 rows. You can see in the results for the second query that there are 91 customers in the table.

Using an inner join to return the first order of each customer

WARNING! Be careful with the number of rows reported by the query results window when executing multiple queries. When you first run those two SELECT queries, it reports 90 rows. But that is the total of all rows returned by the two queries, including the SELECT COUNT(*) query. To show the row count of 89 in the Figure above, we selected the grid for the first query’s results, so it displays the row count for just that one query.

Why is there a discrepancy between the number of customers in the table and the list of first orders? The list, generated using an INNER JOIN, doesn’t include every customer. The behavior of the inner join excludes any customers who have not placed any orders, which means that there isn’t a matching order record for two customers. If you need to see a list of all customers regardless of whether they’ve placed an order, as well as the date of the first order for customers who have, you need to use an outer join.


Thumbnail for 566This post is an excerpt from the online courseware for our
SQL Server 2012: T-SQL Select, Where, and Clauses course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.