proper JOIN syntax
-
I am writing a JOIN and I am trying to figure which of the 2 ways below is the proper way:
select pageDisplayName, COUNT(eventTargetID)
from pageList, eventlog
where moduleID = '1179652124'
and pageID = eventTargetID
group by pageDisplayName
order by pageDisplayName
OR
select PL.pageDisplayName, COUNT(EL.eventTargetID)
from pageList PL
LEFT JOIN eventlog EL ON EL.eventTargetID = PL.pageId
where EL.moduleID = '1179652124'
group by PL.pageDisplayName
order by PL.pageDisplayName
Any help or tips would be greatly appreciated!
Thanks!
-
I prefer the second method over the first as the second method is the ANSI standard JOIN. In looking at your second statement, your LEFT JOIN should be an INNER JOIN. Actually, SQL Server is going to treat it like an INNER JOIN anyhow. When using a LEFT JOIN, you are telling SQL Server that you want rows from the left table (pagelist) regardless of whether there are rows in the right table (eventlog); however, the EL.ModuleID = 'value' in the WHERE clause is telling SQL Server that the right table rows must exist in order to satisfy the query criteria. This has turned your LEFT JOIN to an INNER JOIN.
-
I agree with John. Your first statement is an inner join or sometimes called an equi join and that is all it will ever be without rewriting it.
The second satement can be easily converted to view the differences in the two tables by adding left, right or inner. JOIN gives you more flexibilty when doing analysis.
-
If the 2 queries create the same query plan and return the same results (which in this case they should), what does the system requirements have to do with it? The OP was asking about JOIN style preference, whether to use the old join style or the ANSI standard JOIN style.
-
While the plans should be the same for an inner join, that's not always the case with outer joins. That's one reason that it's best to standardize on one method or the other throughout a shop. In fact, due to differences between the two methods as to when filtering is applied in relation to when the join is applied, they can even return different results for outer joins.
I personally push the ANSI style in my shops, but whatever method you choose, standardize on it.
-
for the sake of arguing with you John, Magy did not explicitly specify that she was looking getting opinions on writing style, hence there are more than one solutions to the implicit question.
1. writing style
2. standards to be used as might be defined in the requirements document
3. and as you have mentioned
When using a LEFT JOIN, you are telling SQL Server that you want rows from the left table (pagelist) regardless of whether there are rows in the right table (eventlog); however, the EL.ModuleID = 'value' in the WHERE clause is telling SQL Server that the right table rows must exist in order to satisfy the query criteria. This has turned your LEFT JOIN to an INNER JOIN.
I could only think of these three, but there might be more reasons why I made reference to system requirements
-
Hey, no arguement here. I was just looking for an explanation on your position. 'Nuff said.
-
I would prefer the ANSI join syntax for two important reasons:
1. It clearly separates the join logic from the criteria logic, making the code easier to understand & maintain
2. SS 2005 does not support the old =* and *= outer join syntax, so in that case you MUST use the ANSI syntax for outer joins
-
Assuming the question is about which style of JOIN to use, you should be using the ANSI92 standard of {INNER | {LEFT | RIGHT | CROSS} OUTER } JOIN (Your 2nd example).
Your first example is from pre-ANSI92 days (when I learned SQL in the '80s). SQL Server 2000 BOL warns that style is deprecated and will not be supported in future versions. While your example will work in SQL Server 2005, the *= and =* operators to indicate Left and Right Joins are not supported in SQL Server 2005. So if your first query was to be a Left Join by having "and pageID *= eventTargetID", that query would fail in SQL 2005.
Hope this helps
Mark -
Thanks guys!
I am using SS 2000...but how could I write this query to be fully compliant in SS 2005?
Thanks!
-
select PL.pageDisplayName, COUNT(EL.eventTargetID)
from pageList PL
INNER JOIN eventlog EL ON EL.eventTargetID = PL.pageId
where EL.moduleID = '1179652124'
group by PL.pageDisplayName
order by PL.pageDisplayName
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply