JoinS giving wierd result???

Question

JoinS giving wierd result???

Maverick100877

SSCertifiable

Points: 5240
More actions
June 5, 2006 at 6:22 am

#113307

Hi
Could anyone please let me know the difference between these two queries
use pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
   ON s.title_id = t.title_id
   AND s.qty > 50
ORDER BY s.stor_id ASC

use pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
   ON s.title_id = t.title_id
Where s.qty > 50
ORDER BY s.stor_id ASC
When I use the And it gives me a different resultset and when I use where it gives me a different resultset..Why so???Whats the difference??

Viewing 4 posts - 1 through 3 (of 3 total)

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

asura SSC Rookie Points: 34 More actions · Answer 1

Hello,

The difference lies in whether the filter Where s.qty > 50 should be applied on the Results of the join or in the source of the join

In query 1 the Filter is applied on Sales and then the result is given for a join.

However in query 2, the values are joined and then filtered

Ranjit

David McFarland SSChampion Points: 11815 More actions · Answer 2

The WHERE clause gets handled after the join, so the second query will only show those rows where the s.qty is greater then 50. The first query should return all rows from Sales, but only the quantities from titles that have a quantity greater than 50.

Edit: Swap Sales with Titles above, as I see you have a RIGHT OUTER instead of LEFT OUTER. Also, I see Asura beat me to the punch.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

Arun,

The first reply to your message has the correct answer however I'd like to elaborate on the answer and try and re-word it another way that I think is easier for many necomers to SQL to understand.

In your first query the FROM clause is taking data from 2 tables; SALES & TITLES. The second query is doing the same but with one exception, the second query is not placing any conditions on what records from each table can be included in the join. In the first query you are joining all records from TITLES table and joining those rows from the SALES table in which the QTY value for each row in the SALES table is greater then 50. If a row has a value in SALES.qty that is less then 50 then it is not included in the join. Once that join is complete you have a new virtual table and the WHERE clause in the query is then applied to that new table which holds the combined records from SALES & TITLES. Because there are no records in this new table from the SALES table that have a qty value less then 50 there is no need to add that condition to the WHERE clause. The second query you wrote has no condition on what records from the SALES table are to be joined with records from the TITLES table and so after records from the 2 tables are combined you will need to add to the WHERE caluse the condition SALES.qty > 50 .

So the difference between the 2 is when the records from the SALES table that have a qty value less then 50 are filtered from the final result set. In the first query you get rid of those rows before you join data from the SALES table to data from the TITLE table. And in the second query you filter out those records after you have joined data from both tables.

Now there is a big difference in how you do this, which of the 2 queries you use, because they are not always going to return the same end result. If these same 2 queries used a Left Outer Join instead of the RIGHT Outer Join then the first query could return more data then the second. This is because the first query is applying the condition of qty>50 to only the data in the SALEs table. The second query is applying that condition to the data from both tables combined. SO if you have a record in the TITLES table that is joined to a record in the SALES table in which the SALES.qty value is less then 50 then the first query will still show that record from the TITLES table and display a NULL for all the columns from the SALES table shown in the query where as the second query will not show that record from the TITLES table.

I hope that better explains the difference.

One additional note. You can use a Right Outer Join but it's consider better form to use a Left Outer Join and use a Right Outer only when a Left Outer can not be used. SO instead of SALES Right Outer Join TITLES it would be better form to write TITLES Left Outer Join SALES.

Ed Carden

Kindest Regards,

Just say No to Facebook!