Combining Join and Filter Criteria
I’m kinda shy when it comes to topics like this one. It borders on trolling for comments. The reason I have chosen this topic is because it has come up in recent conversation with developers moving to writing their own SQL, and wanting to understand the difference more fully. From that perspective I thought it best to not take the position that everyone has experienced different forms of SQL Join syntax, let alone combining it with filtering criteria.
In response to the previous editorial (http://bitonthewire.wpengine.com/editorials/readed.aspx?ID=2790) Adrian writes:
I think there are two different topics in there:
- The use of non-ANSI compliant syntax vs. ANSI compliant syntax.
In case of FULL JOINS the functional differences are minimal, however when intended to use LEFT/RIGHT joins the non-ANSI syntax (first query) proves to be quite inflexible sometimes. Please note that the non-ANSI syntax for outer joins has become deprecated in SQL Server 2008 (see http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx). Many Oracle developers still use and misuse the non-ANSI syntax. The general recommendation when writing code is to try to use ANSI syntax and functions. The ANSI syntax can make the code easier to read, minimize logical errors, can help while troubleshooting and minimize the effort when porting the code from one vendor to another.
- The use of filter conditions in the WHERE clause or directly in the JOINS.
If FULL JOINS are considered, as in your examples, actually it makes no difference where you put the conditions, the results should be consistent! If other types of JOINS are used, then the behavior is different from case to case, the filter being considered at the level you put it.
I agree that there are two concepts in the previous editorial. There are two join techniques…one performed in the WHERE clause (deprecated as Adrian points out) and the second in the JOIN clause of an SQL Statement. Both techniques are ANSI compliant as I recall. I know Joe Celko could tell you. Anyway, placing JOIN criteria in the JOIN clause is the newer ANSI method implemented in SQL Server in the mid 1990s.
I believe Adrian means an INNER JOIN in his statement #1 where he says, “In the case of FULL JOINS.” A FULL JOIN is the TSQL abbreviated syntax for a FULL OUTER JOIN, meaning that all the rows in table A are joined to all the rows in table B that match the join criteria, and data from table A or table B are null where no match is found. Still you always get all the rows from both tables.
In the case of INNER JOIN I would agree that the differences in the results are minimal. With outer joins, there is a lot of cool stuff that happens when you put the filtering criteria in the JOIN clause instead of in the WHERE Clause (Adrian’s point #2). This is where you can take advantage of the difference.
I’ll talk more to that tomorrow with some demonstrations for using the JOIN clause to produce interesting results.
If you have something you would like to add to this conversation, please feel free to leave a comment below, or drop a note to btaylor@sswug.org. Thanks Adrian for your valuable comments.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Written by Townsend Security
Simplify encryption and key management on … (read more)
Featured Script
List index information for all databases
List index information for all databases… (read more)