User Tools

Site Tools


concepts:join-condition
  • Bookmark "Join Condition" at del.icio.us
  • Bookmark "Join Condition" at Digg
  • Bookmark "Join Condition" at Furl
  • Bookmark "Join Condition" at Reddit
  • Bookmark "Join Condition" at Ask
  • Bookmark "Join Condition" at Google
  • Bookmark "Join Condition" at Netscape
  • Bookmark "Join Condition" at StumbleUpon
  • Bookmark "Join Condition" at Technorati
  • Bookmark "Join Condition" at Live Bookmarks
  • Bookmark "Join Condition" at Yahoo! Myweb
  • Bookmark "Join Condition" at Facebook
  • Bookmark "Join Condition" at Newsvine
  • Bookmark "Join Condition" at Yahoo! Bookmarks
  • Bookmark "Join Condition" at Twitter
  • Bookmark "Join Condition" at myAOL
  • Bookmark "Join Condition" at Slashdot
  • Bookmark "Join Condition" at Mister Wong

Join Condition

In SQL the act of merging two input tables into one generated output table is performed by processing a join operation. By doing this the Database Management System will display all valid combinations, consisting of values from two table entries each – one from every input table. In case of an outer join the output table also contains values from one input table, the corresponding second is a NULL value.
To ascertain which combinations are valid, it is obligatory to specify that in the so called join condition. It is part of every join – no matter if inner, left (outer), right (outer) and full (outer) join operation.
It is a segment of a SQL query in order to describe which columns are used to match against each other and which logical statement has to be true to qualify any combination as table entry of the output table. There are two possible ways to do so but in every query exactly one of them is applied: the two options are specifying join conditions with either the ON clause or the WHERE clause of a SQL SELECT.

To go the ON clause way you do not only set the column names which should be compared into your ON clause but also another condition to qualify only specific rows using the word AND.
For example:

SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
AND CUS.FirstName = 'John'

The last line of this SQL query exclusively includes the join condition.

The second opportunity for a join condition would be using the WHERE clause and is constructed as follows:

SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
WHERE CUS.FirstName = 'John'

The last line of this SQL query exclusively includes the join condition.
As we can see the ON clause is still obligatory to indicate the columns to be mapped and only the last line respectively one term set the two queries apart from each other. But the main impact is not obvious for an amateur: the performance differences since both SQL statement aren’t handled in the same way by the DBMS due to divergent processing logic.
In the first statement the join condition is applied for nominating only permitted table items of the input tables, then promptly displaying the results. The second statement records all possible table rows as given by the specific (inner/left/right/outer) join logic, then internally removes all non-fitting entries and finally presents them as output table which – of course – will cost more resources due to more transactions needed.

It’s also important to mention how to draft the query in case of joining more than two tables and / or applying more conditions. Supposing we would not only have our Customers and Orders table but an Stores table as well we would build that as follows:

In ON clause:

SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
AND CUS.FirstName = 'John' AND CUS.LastName = 'Doe'
INNER JOIN dbo.Stores AS STO
ON ORD.StoreID = STO.StoreID
AND STO.Country = 'United States' AND STO.stateCode = 'CA'


In WHERE clause:

SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
INNER JOIN dbo.Stores AS STO 
ON ORD.StoreID = STO.StoreID
WHERE CUS.FirstName = 'John' AND CUS.LastName = 'Doe' AND STO.Country = 'United States' AND STO.stateCode = 'CA'


Always make sure that the correct logical operator is used as needed in your context as well as keeping formats in mind.
Sources:
http://stackoverflow.com/questions/1018952/condition-within-join-or-where
https://msdn.microsoft.com/en-us/library/f4saeycb(v=vs.80).aspx

  • Bookmark "Join Condition" at del.icio.us
  • Bookmark "Join Condition" at Digg
  • Bookmark "Join Condition" at Furl
  • Bookmark "Join Condition" at Reddit
  • Bookmark "Join Condition" at Ask
  • Bookmark "Join Condition" at Google
  • Bookmark "Join Condition" at Netscape
  • Bookmark "Join Condition" at StumbleUpon
  • Bookmark "Join Condition" at Technorati
  • Bookmark "Join Condition" at Live Bookmarks
  • Bookmark "Join Condition" at Yahoo! Myweb
  • Bookmark "Join Condition" at Facebook
  • Bookmark "Join Condition" at Newsvine
  • Bookmark "Join Condition" at Yahoo! Bookmarks
  • Bookmark "Join Condition" at Twitter
  • Bookmark "Join Condition" at myAOL
  • Bookmark "Join Condition" at Slashdot
  • Bookmark "Join Condition" at Mister Wong
concepts/join-condition.txt · Last modified: 2016/05/19 23:58 by LJuraschek