A Visual Explanation of Different SQL JOINs

One of my standard interview questions to ask beginner- and intermediate-level developers who say that they know SQL is “Please explain a LEFT OUTER JOIN to me.” You’d be amazed how many people who “know SQL” trip over this. Understanding how to return proper sets of data from multiple tables is fundamental.

Jeff Atwood has a great post where he uses the concepts of data sets and Venn Diagrams to visually explain what each type of JOIN returns. Check it out:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Easy SQL “If Record Exists, Update It. If Not, Insert It.”

A very common scenario is the one where we want to update the information in a record if it already exists in the table, and if it doesn’t exist, we want to create a new record with the information. 

The most common solution to this problem is using IF EXISTS  (subquery).  This comes to mind first because it matches how we think about the problem (as you can see by the title of this article!).  We say “If the criterion exists in this initial subquery, then I’ll do this.  If not, I’ll do this other thing.”  This results in a three-step process:

1. Do the subquery (SELECT whatever FROM wherever WHERE something).
2. Evaluate the EXISTS statement (is it there or not?).
3. Execute either the UPDATE or INSERT statement.

Now, let’s try an “unnatural” shortcut.  I say unnatural because it doesn’t follow that “natural” logic occurring in our brain that I mentioned above.  Instead, let’s just do the update, and if it fails, then we’ll do the insert.  When the update fails, that just means that no rows were affected, not that an error was thrown.  Now we are down to a one-step (if the update succeeds) or two-step process (if we have to insert instead).  This is much more efficient!

Example:

This is not necessarily a practical example, but let’s say that we have a table called “Users” which has three fields:  “UserID”, “FirstName”, and “LastName”.  If a record already exists with the specified UserID, simply update it with the new @FirstName and @LastName values.  If it does not exist, create a new record with those values.

CREATE PROCEDURE dbo.spAddUserName 
     ( 
     @UserID AS int, 
     @FirstName AS varchar(50), 
     @LastName AS varchar(50) 
     ) 
AS 
     BEGIN 
          DECLARE @rc int    

          UPDATE [Users] 
             SET FirstName = @FirstName, LastName = @LastName 
           WHERE UserID = @UserID   

          /* how many rows were affected? */ 
          SELECT @rc = @@ROWCOUNT    

          IF @rc = 0 
               BEGIN 
                    INSERT INTO [Users] 
                                (FirstName, LastName) 
                         VALUES (@FirstName, LastName) 
               END         

     END