what type of join is in this query

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

General SQL Server Forums

New to SQL Server Programming

what type of join is in this query

Author

Topic

Tamaradenise
Starting Member

7 Posts

Posted - 2014-12-24 : 13:22:06

Select * from patient, orders
Where patient.patient_id = orders.patient_id (+)
And orders.order_type = 705

Tamara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-24 : 13:44:36

It's a terrible join query. It isn't using the ANSI SQL-92 standard. I would run far away.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Tamaradenise
Starting Member

7 Posts

Posted - 2014-12-24 : 14:05:55

I know this was a mess trying to figure out what type of join query it was

Tamara

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-24 : 14:43:47

it could be an inner join but, at least in SQL Server, the "(+)" is invalid syntax at the end of your second line.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-24 : 15:13:39

I believe it's an outer join in Oracle.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-24 : 20:59:19

Wow, just got a flashback (of the bad kind). It is at least 15 years ago, I last saw queries like this.
Directly translated from oracle to mssql, your query would be:

select *
  from patient
       left outer join orders
                    on orders.patient_id=patient.patient_id
 where orders.order_type=705

which makes this join an inner join.
Could be rewritten to (and still have the same functionality):


select *
  from patient
       inner join orders
               on orders.patient_id=patient.patient_id
              and orders.order_type=705

If it was a (true) outer join, (+) should have been applied to all columns of the orders table.

A fine description is found here (see "outer join" section): [url]http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm[/url]

Tamaradenise
Starting Member

7 Posts

Posted - 2014-12-24 : 23:04:54

Thank You .... yall Rock

Tamara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources