SQL Differences - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Differences

Author

Topic

ketema
Starting Member

4 Posts

Posted - 2004-08-11 : 09:50:15

just have a quick question regarding SQL Formats...What is the difference between the two queries below? Are there any situations where one will return different results than the other?

SELECT
table1.column1 ,table1.column2, table1.column3
FROM
table1 LEFT OUTER JOIN table2 ON table1.column2 = table2.column1
WHERE
table1.column1 = 123456

SELECT
table1.column1 ,table1.column2, table1.column3
FROM
table1,table2
WHERE
table1.column2 = table2.column1 AND table1.column1 = 123456

Kristen
Test

22859 Posts

Posted - 2004-08-11 : 09:54:09

If table1.column2 IS NULL then you will get no data from the second one, but you may get data [depending on the other criteria] from the first

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-11 : 09:54:09

The last one would be the same as an INNER JOIN hence give less results should table2 not have any values for table1.column2

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 09:54:35

of course it will.
second query can be also written like:
SELECT
table1.column1 ,table1.column2, table1.column3
FROM
table1 inner join table2 on table1.column2 = table2.column1
WHERE table1.column1 = 123456

in other words left join wil return all data from table1, and null values in table2 where there is no match
inner join will return only connected data.

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 09:55:38

ok that was really tight squeeze :)

Welcome back, Kristen. how was your vacation? :)

Go with the flow & have fun! Else fight the flow :)

ketema
Starting Member

4 Posts

Posted - 2004-08-11 : 10:45:35

Thank you. That makes sense. Next Question. How would you write the OUTER JOIN query without the outer join? I am converting a bunch of SQL server queries to non SQL Server databases, and it doesn't like the OUTER JOIN syntax. I wrote the bottom query, and got back the same data, but I want to make sure I get ALL ROWS from table1, regardless of what table2 has in it. (hence the OUTER JOIN for SQL server, but I need it without the outer join)

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 10:51:54

well... you could do
select * from table1, table2 where table1.column2 = table2.column1 AND table1.column1 = 123456
union all
select * from table1, table2 where table1.column2 <> table2.column1 AND table1.column1 = 123456

play with it to get correct results. i don't think there is a beter non join way...

Go with the flow & have fun! Else fight the flow :)

ketema
Starting Member

4 Posts

Posted - 2004-08-11 : 10:59:39

I want the data grouped in rows, so that information pertaining to 123456 is together, that way will give me multiple rows. I may just have to look at the documentation for this server. I believe in Oracle you use a + or something similar to get the OuterJoin functionality, so I'm sure there is something similar for this AS400 thing. Ther also must be an ANSI standard way of obtaining an outer join, so I will look for that as well.

Thanks.

ketema
Starting Member

4 Posts

Posted - 2004-08-11 : 11:05:48

I got it figured out. This server is not an AS400 at all it is an oracle 8 server, which of course needs the (+) to specify the optional table. I was told it was an AS400 and I just found the manual and it says it supports the INNER,OUTER JOIN syntax, so I knew something was up. Thanks for your help guys, but once again it boils down to having the right information.

Thanks

Kristen
Test

22859 Posts

Posted - 2004-08-11 : 13:03:30

"Welcome back, Kristen. how was your vacation?"

I went on a sniping course! Sniping by 0 seconds is something to savour, that's for sure! - Sorry Lumbago ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-11 : 13:18:53

ok... so what is sniping??

Go with the flow & have fun! Else fight the flow :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-08-12 : 06:37:56

sniping...when two people post answers at the same time....have at look back at the posting times earlier...."Posted - 08/11/2004 : 09:54:09 "

it's an amazing thing that 2 people in disparate locations around the world can be typing and posting at the exact same time.........just shows the power of SQLTeam.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources