Inner Join versus Outer Join?
-
OK, I'm stuck again. I'm not sure of the difference between an ON clause or WHERE clause or when to use one over the other.
Thank you!
I'm starting with:
dtTimeIn bStatus
---------------------------------------------------
1899-12-30 12:00:00 0
1899-12-30 12:00:01 0
1899-12-30 12:00:02 0
1899-12-30 12:00:03 1
1899-12-30 12:00:04 1
1899-12-30 12:00:05 0
1899-12-30 12:00:06 0
1899-12-30 12:00:07 1
I'm trying to get to:
dtTimeStart dtTimeFinish bStatus
---------------------------------------------------
1899-12-30 12:00:00 1899-12-30 12:00:02 0
1899-12-30 12:00:03 1899-12-30 12:00:04 1
1899-12-30 12:00:05 1899-12-30 12:00:06 0
1899-12-30 12:00:07 2005-09-09 22:00:00 1
-
There is no ON clause in JOINS you can add ON to the FROM clause but that is old you now use the where clause and use AND operator to add more conditions to the JOIN. You do INNER JOIN if the tables are equal and OUTER JOIN if they are not but OUTER JOIN defaults mathematically to a NULL so you have to be carefull to get expected results. But you are trying to do time which can get complex. The samples below are from the BOL (books online). Hope this helps.
-- Join in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
-- Join in FROM clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Any ideas on how I can group my data as indicated? I'm trying to group the times based on Status but maintain the sequence of Status.
-
For inner joins, there is no difference at all if you filter in the ON clause or the WHERE clause.
These two are exactly identical:
SELECT *
FROM foo f
JOIN bar b
ON f.id = b.id
AND b.foobar = 'bozo'
SELECT *
FROM foo f
JOIN bar b
ON f.id = b.id
WHERE b.foobar = 'bozo'
When you come to outer joins, the rules get more complex. You need to have an absolute understanding of what your query really is asking for when writing outer joins.
At all costs, NEVER use the old legacy *= syntax for outer joins - they just don't work correctly, and you risk getting unexpected, or even plain wrong results back. Always use ANSI syntax when writing outer joins.
So, if we take our two examples and do outer joins instead:
SELECT *
FROM foo f
LEFT JOIN bar b
ON f.id = b.id
AND b.foobar = 'bozo'
SELECT *
FROM foo f
LEFT JOIN bar b
ON f.id = b.id
WHERE b.foobar = 'bozo'
These two, even though they may look the same, are fundamentally different. This is where it becomes important if the filter is placed in the ON or WHERE clauses. In outer joins you always have an 'outer' table and an 'inner' table. Here, foo is 'outer' (the first table in the outer join) and bar is the 'inner' table.
Now, in outer joins, if you want to filter on the inner table - the filter goes into the ON clause. If you want to filter on the outer table, the filter goes into the WHERE clause.
It takes some practice to get the hang of the workings, but if you play around a little with some mock up data, it's pretty easy to just change between ON and WHERE and see how the results changes. The most important thing to remember when writing outer joins, is to test on known data. It's very easy to get results back, but can be more difficult to get the correct results back
/Kenneth
-
Thanks for the explanation Kenneth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply