Join Help

Question

Join Help

Perry Elliott

Valued Member

Points: 59
More actions
November 3, 2007 at 1:24 pm

#62489

I have the following two queries which I thought would give me identical results:
select count(distinct po_id)
from order_po, order_sales, list_bases
where order_po.requisition = 1
and order_po.base_id *= list_bases.base_id
and order_po.Po_id *= order_sales.Req_no
and order_sales.Active = 1
------------------------------------------------
SELECT count(distinct po_id)
FROM ORDER_PO p LEFT OUTER JOIN ORDER_SALES os
ON p.Po_id = os.Req_no
LEFT OUTER JOIN LIST_BASES lb
ON p.Base_id = lb.Base_id
WHERE p.Requisition = 1
AND os.Active = 1
However, only the first query returns the expected results - I'm pretty much still a newbie developer and am maintaining and adding features to this existing application. After reading the article on sqlservercentral about the join mystery (as well as guru's guide to t-sql), I have decided to use the new join syntax and haven't ran into any problems with it until now. Also note when I display the estimated execution plan, they are identical except the second query uses a left semi join whereas the first query uses a left outer join. What, that should be plainly obvious, am I missing? Also note I ran the queries against both sql server 2005 and sql server 2000.
thanks for your time

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Perry

I think the problem is that the last line of your ANSI-compliant query effectively turns the join with order_sales into an inner join. You haven't posted any table structure, sample data or expected results, so it's not easy to see what you're trying to achieve. Try putting that last AND clause into the join predicate and see if that gives you what you're looking for. If it doesn't, please post the information I mentioned above and we'll see if we can help you out.

John

Perry Elliott Valued Member Points: 59 More actions · Answer 2

yep, when I placed the active = 1 into the join, it returned the expected results - thanks for your help. Any simple explanation or online resources available that explain why it turned it into an inner join?

thanks again for your help

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

The only difference between a LEFT JOIN and an INNER JOIN is that there are some NULLs in the right-hand table where there are no matches with the values in the left-hand table. If you impose an equality condition on a column in the right-hand table then that automatically eliminates all NULLs from that side, thus turning your join into an inner join.

John

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 4

*= is severely deprecated and may produce unexpected results. Don't use it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) It is a common mistake to think that join clause conditionals and where clause conditionals will equate. But there is a vast difference in where the restriction is taken when you have other than inner joins.

2) *= is only deprecated in 2005, isn't it Jeff? And it throws an error as opposed to giving undefined results.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 6

SELECTCOUNT(DISTINCT lb.po_id)

FROMORDER_PO AS p

LEFT JOINORDER_SALES AS os ON os.Req_no = p.Po_id

AND os.Active = 1

LEFT JOINLIST_BASES AS lb ON lb.Base_id = p.Base_id

WHEREp.Requisition = 1

N 56°04'39.16"
E 12°55'05.25"

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

TheSQLGuru (11/5/2007)
1) It is a common mistake to think that join clause conditionals and where clause conditionals will equate. But there is a vast difference in where the restriction is taken when you have other than inner joins.
2) *= is only deprecated in 2005, isn't it Jeff? And it throws an error as opposed to giving undefined results.

AFAIK, I believe it's been "marked" as deprecated since 7.0. Nobody pulled the trigger and ACTUALLY deprecated it until 2005.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 8

TheSQLGuru (11/5/2007)
1) It is a common mistake to think that join clause conditionals and where clause conditionals will equate. But there is a vast difference in where the restriction is taken when you have other than inner joins.
2) *= is only deprecated in 2005, isn't it Jeff? And it throws an error as opposed to giving undefined results.

I guess it depends on your definition of "deprecated"... Here's from SQL Server 2000 BOL and I've highlighted a key point...

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

I believe a similar warning was present in SQL Server 7 BOL. According to WikiPedia.com, the definition of "deprecated" is...

In computer software standards and documentation, the term deprecation is used to indicate discouragement of usage of a particular software feature, usually because it has been superseded by a newer/better version. The deprecated feature still works in the current version of the software, but it may raise error messages or warnings recommending an alternate practice.

I humbly appologize for not having 2k5 available to test on, yet... but I'm thinking that if it no longer works in 2k5, it's not deprecated... it's gone 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Looks like my definition (is broken now) is too restrictive - this from dictionary.com:

Said of a program or feature that is considered obsolescent and in the process of being phased out, usually in favour of a specified replacement. Deprecated features can, unfortunately, linger on for many years. This term appears with distressing frequency in standards documents when the committees writing the documents realise that large amounts of extant (and presumably happily working) code depend on the feature(s) that have passed out of favour.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Of course - I could understand why some are surprised that it's gone....Might I draw your attention to this section of the BOL for SQL Server 7.0?

In earlier versions of SQL Server, you use the *= and =* operators to specify the left and right outer join conditions in the WHERE clause. In some cases, this syntax results in an ambiguous query that might not return the expected results. For example, consider the two SELECT statements in Listing 5 and their output in Figure 1 on page 32. Logically, both statements are identical, but they result in different data sets. The ambiguity exists under the old syntax because the optimizer does not always distinguish the JOIN condition from the selection criteria. SQL Server 6.5 supports SQL-92-compliant outer joins using the LEFT OUTER JOIN and RIGHT OUTER JOIN extensions in the FROM clause, thus removing this ambiguity. For backward compatibility, SQL Server 7.0 continues to support the old-style syntax. However, Microsoft has stated that future releases of SQL Server will support only the SQL-92 syntax. Therefore, we strongly recommend that you convert any remaining queries that use the old-style syntax to a SQL-92-compliant form.

...Sounds familiar...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 11

Uh huh... sure does. Thanks, Matt 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)