What kind of JOIN syntax is this?

I am not often puzzled when looking at TSQL syntax, but this one time I could not figure out the syntax. Syntax looked similar to the example below.

 
SELECT  t.*
      , s.*
FROM    Teacher AS t
LEFT OUTER JOIN dbo.Student AS s
JOIN dbo.Grades AS gr
    ON s.ID = gr.StudentID
       AND  gr.GradeLetter = 'A'
    ON s.TeacherID = t.ID

My first gut reaction was that this code is broken and would not run. To my amazement code ran just fine. Now came the hard part, which was to figure out what the code was doing because I have never seen this syntax before. Since I did not understand what I was looking at I could not BING "weird join syntax" to get an answer. As a developer, I learned long time ago to break down code into smallest possible chunks to get the answer.

After I have figured out the relationship between tables, I was able to understand what query was doing. To be able to read query better it can be rewritten in the following way.

 
SELECT  t.*
      , s.*
FROM    Teacher AS t
LEFT OUTER JOIN
            (
                SELECT  s.*
                      , gr.*
                FROM    dbo.students AS s
                JOIN dbo.Grades AS gr
                    ON s.ID                = gr.StudentID
                       AND  gr.GradeLetter = 'A'
            ) AS s
    ON s.TeacherID = t.ID

This code is much easier to read and to understand. Students filtered by Grades and we are only interested in Students who have "A" grade. Therefore we get all records from Teacher table but only get Students who have good grades. If we would try to rewrite the query in the following matter, we would only get Teachers that have Students with perfect grades as we specified inner join; therefore, it takes precedence over the left outer join.

 
SELECT  t.*
      , s.*
FROM    Teacher AS t
LEFT OUTER JOIN dbo.Student AS s
    ON s.TeacherID         = t.ID
JOIN dbo.Grades AS gr
    ON s.ID                = gr.StudentID
       AND  gr.GradeLetter = 'A'

Now that I knew what code was doing I wanted to know performance because I do not care how the code looks as long as it does not perform fast it is useless. The execution plans of the two queries are almost identical with the only difference being that when I rewrote the original query as sub-query, I got additional operation added to the plan. Physical joins were identical and execution time was almost exact.

I could not believe I learned something new after writing T-SQL for many years. All of the sudden I got another tool in my toolbelt to write better code. Now if I ever need to filter tables that should not effect result I can write with this new syntax. To be honest, I have not taken this to the extreme to see how many JOIN operations I can nest before it becomes unreadable. However, I am sure it would not take many before someone else could not figure out what was going on and would have to break it down piece by piece to see it clear.

Just imagine having to support code that looks like example 1 below instead of example 2. Even though the code in the following case produces same execution plan, it becomes hard to read and therefore hard to maintain, and if you have junior developers who are not strong in SQL, they are more likely to make mistakes.

 
--Example 1:
SELECT  t.FullName AS TeacherName
      , s.FullName AS StudentName
      , gr.Class
FROM    dbo.Teacher AS t
JOIN dbo.Student AS s
JOIN dbo.Grades AS gr
JOIN dbo.Class AS c
JOIN dbo.Room AS r
    ON c.ID = gr.ClassID
    ON r.ClassID = c.ID
    ON s.ID = gr.StudentID
    ON s.TeacherID = t.ID

--Example 2:
SELECT  t.FullName AS TeacherName
      , s.FullName AS StudentName
      , gr.Class
FROM  dbo.Teacher AS t
JOIN dbo.Student AS s
    ON t.ID = s.TeacherID
JOIN dbo.Grades AS gr
    ON s.ID = gr.StudentID
JOIN dbo.Class AS c
    ON c.ID = r.ClassID
JOIN dbo.Room AS r
    ON c.ID = gr.ClassID

P.S. I still don't know if this JOIN syntax has special name, but at least I now know what it does.

SQL Joins - Basics Part 2

Having already covered the basics of SQL Join syntax, now it is time to jump into a little more advanced stuff. Early in my career, I remember being confused about the difference in results when the same clause is placed in JOIN instead of WHERE clause. This post is aimed to clarify those questions and few others around ON and WHERE clauses. For demonstration I will use table built and populated in SQL Joins - Basics Part 1

First, let us look at basic LEFT join from Part 1. All rows are returned from Teacher table and all matching rows from Student table are displayed.

 
SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
LEFT OUTER JOIN Student s
        ON s.TeacherID = t.ID

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Roy Chad	Milburn Brett
Dudley Goddard	Clinton Schuylerr
Dudley Goddard	Norbert Kemp
Raphael Philander	Meriwether Kennedy
Raphael Philander	Braith Cornelius
Sonnie Davin	NULL

---

Now we introduce simple WHERE clause. By adding condition to WHERE clause we are now restricting entire results set to specific condition, any rows that do not satisfy that condition are excluded from results. Therefore we only end up with one row seen below.

 
SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
LEFT OUTER JOIN Student s
        ON s.TeacherID = t.ID
WHERE s.FullName = 'Jepson Balfour'

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour

---

So what happens when you move same clause to the ON clause? Ok, lets test it!

 
SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
LEFT OUTER JOIN Student s
        ON s.TeacherID = t.ID
  and s.FullName = 'Jepson Balfour'

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Dudley Goddard	NULL
Raphael Philander	NULL
Sonnie Davin	NULL

What happened? Result set looks nothing like first or second example. When I first did that I was thoroughly confused. To explain the result lets run another query.

 
SELECT s.FullName
FROM dbo.Student as s
WHERE s.FullName = 'Jepson Balfour'

StudentFullName
Jepson Balfour

---

Even though last two queries look different in reality they are placing exactly same restriction on Student table. On clause on Student table became where clause that restricts results to only rows that specific that critirea. Since Teacher table is not joined with INNER join it is not restricted by what happens to Student therefore we see all Teachers displyaed but only one of them actually showing a student.

---

Conclusion:

The ON clause is a powerfull way to change your result set exactly to what you need it to be, but if used without understanding of what happens to the data it can produce unpredicted result set. Each statement placed in ON clause will be evaulated prior to WHERE clause. This goes back to understanding order of operations in SQL Server. Below are just few of the operations listed in correct order. By knowing and understanding the order of operations in SQL Server it helps to understand why queries behaved the way that they did above. Each one was evaluated by SQL Server in correct order which produced correct output based on that structure.

1. FROM
2. ON
3. WHERE
4. SELECT

As always feel free to leave comments, questions, etc. For next post I will answer common question of TOP clause.

SQL Joins - Basics Part 1

This topic takes me back to my first SQL Server database class I had in college. Back then it was extremely difficult subject for me. Partly because I have worked very little with databases and secondary, because of the way it was presented. Taking all that into account let's try to attack it in most basic manner. SQL JOIN in its basic form is just a way to connect two tables together. There are several types of JOINs that can be defined: LEFT, RIGHT, FULL, INNER, CROSS. There are time and place for each one, but most of the time I can get away with using only LEFT and INNER.

Its is great to talk about JOINs, but I'm sure that there are other people who like me don't learn but hearing, they learn by seeing and trying it them self. To do that, we need to create two basic tables and populate it with some data.

First, let us create two tables to store data for our tests. Since the concept of student and teacher is most easily understood, I'm creating Teacher table and Student table with a key to link back to Teacher Table.

CREATE TABLE [dbo].[Student]
(
 [ID] [int] NULL
 ,[FullName] [varchar](50) NULL
 ,[TeacherID] [int] NULL
)

GO

CREATE TABLE [dbo].[Teacher]
(
 [ID] [int] NULL
 ,[FullName] [varchar](50) NULL
)

Now to load some data, I generated insert script with random names from the following website.

 
INSERT INTO Teacher (ID, FullName)
VALUES  (1, 'Roy Chad')
  ,(3, 'Dudley Goddard')
  ,(7, 'Raphael Philander')
  ,(5, 'Sonnie Davin')

INSERT INTO Student (ID, FullName,TeacherID)
VALUES  (1,'Jepson Balfour',1)
  ,(2,'Milburn Brett',1)
  ,(4,'Clinton Schuyler',3)
  ,(5,'Norbert Kemp',3)
  ,(7,'Meriwether Kennedy',7)
  ,(8,'Braith Cornelius',7)
  ,(10,'Dion Hayden',9)

---

Now that we got some data loaded into tables let the JOINing commence. First will look at 'INNER' JOIN

SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
INNER JOIN Student s
        ON s.TeacherID = t.ID

INNER JOIN throws out all records that do not match. In our case Teacher "Sonnie Davin" and Student "Dion Hayden" do not appear in result because there was no way to connect those records.

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Roy Chad	Milburn Brett
Dudley Goddard	Clinton Schuylerr
Dudley Goddard	Norbert Kemp
Raphael Philander	Meriwether Kennedy
Raphael Philander	Braith Cornelius

---

LEFT OUTER JOIN, returns all records from main table and attempts to match records from secondary table.

SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
LEFT OUTER JOIN Student s
        ON s.TeacherID = t.ID

As seen in this result list, Teacher "Sonnie Davin" appears in the list but does not have any student records associated. LEFT JOIN is most usefull when ever you not sure if you all records from main table are matched in secondary table. Almost every time I write query I use LEFT JOIN the first I'm JOINing tables together to know for sure that I'm not excluding any records that I wanted to include.

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Roy Chad	Milburn Brett
Dudley Goddard	Clinton Schuylerr
Dudley Goddard	Norbert Kemp
Raphael Philander	Meriwether Kennedy
Raphael Philander	Braith Cornelius
Sonnie Davin	NULL

---

RIGHT OUTER JOIN returns all records from RIGHT table and matches records from left table. The following two queries can be written as RIGHT or LEFT join with same results.

SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
RIGHT OUTER JOIN Student s
        ON s.TeacherID = t.ID

SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Student s
LEFT OUTER JOIN Teacher t
        ON s.TeacherID = t.ID

In this result set because we reversed the condition of LEFT JOIN above, we can see record from Student table "Dion Hayden" who is not matched with a teacher record.

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Roy Chad	Milburn Brett
Dudley Goddard	Clinton Schuylerr
Dudley Goddard	Norbert Kemp
Raphael Philander	Meriwether Kennedy
Raphael Philander	Braith Cornelius
NULL	Dion Hayden

---

FULL OUTER JOIN attempts to match records from both tables just like INNER JOIN and also returns all additional records that do not match from ether table.

SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
FULL OUTER JOIN Student s
        ON s.TeacherID = t.ID

In the this result set we have all records that we had from INNER JOIN, plus extra record from LEFT JOIN and one record from RIGHT JOIN. Teacher "Sonnie Davin" appears in result set without student and Student "Dion Hayden" also appears in result set without teach. This join is most usefull when need to get all records no matter if match exists or not.

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Roy Chad	Milburn Brett
Dudley Goddard	Clinton Schuylerr
Dudley Goddard	Norbert Kemp
Raphael Philander	Meriwether Kennedy
Raphael Philander	Braith Cornelius
Sonnie Davin	NULL
NULL	Dion Hayden

---

CROSS JOIN, returns all records from main table and matches them to every record from secondary table.

SELECT  t.FullName AS TeacherFullName
       ,s.FullName AS StudentFullName
FROM    Teacher t
CROSS JOIN Student s

What we get back is the following result set that has every record from both table. Since no condition has be specified every record appears in result set. Number of records in result set will always be equal to Number of records in TableA multiple by number of record in TableB. In our case we had 4 Teachers and 7 Students therefore we got 28 records in result. But what if we had 4 million teachers and 70 million students. This JOIN has worst performance and will require most memory to complete the operation.

TeacherFullName	StudentFullName
Roy Chad	Jepson Balfour
Roy Chad	Milburn Brett
Roy Chad	Clinton Schuylerr
Roy Chad	Norbert Kemp
Roy Chad	Meriwether Kennedy
Roy Chad	Braith Cornelius
Roy Chad	Dion Hayden
Dudley Goddard	Jepson Balfour
Dudley Goddard	Milburn Brett
Dudley Goddard	Clinton Schuylerr
Dudley Goddard	Norbert Kemp
Dudley Goddard	Meriwether Kennedy
Dudley Goddard	Braith Cornelius
Dudley Goddard	Dion Hayden
Raphael Philander	Jepson Balfour
Raphael Philander	Milburn Brett
Raphael Philander	Clinton Schuylerr
Raphael Philander	Norbert Kemp
Raphael Philander	Meriwether Kennedy
Raphael Philander	Braith Cornelius
Raphael Philander	Dion Hayden
Sonnie Davin	Jepson Balfour
Sonnie Davin	Milburn Brett
Sonnie Davin	Clinton Schuylerr
Sonnie Davin	Norbert Kemp
Sonnie Davin	Meriwether Kennedy
Sonnie Davin	Braith Cornelius
Sonnie Davin	Dion Hayden

---

This concludes the first look at logical JOIN operations. Just like I mentioned in beginning of the post, just about every time I write query it ends up being LEFT or INNER JOIN. Do I ever had to use others? Yes, but with specific business cases.

Please leave a comment with questions and/or feedback on post.