• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Old style SQL vs new style SQL

 
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In an effort to simplify the sql referenced here, we've come up with this:

We've avoided all the RIGHT OUTER JOIN stuff.

However, I still want to do a LEFT OUTER JOIN to one of the tables. I'm under the impression that by using a WHERE this way, all the joins are inner joins.

1) Is this true?
2) How would I add the LEFT OUTER JOIN to this?
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
MdQ: I'm under the impression that by using a WHERE this way, all the joins are inner joins.

1) Is this true?
2) How would I add the LEFT OUTER JOIN to this?


1. Yes. (As you've written it, yes, unless you introduce some vendor-specific syntax to indicate an outer join.)
2. The syntax would be specific to your database vendor. For example, Oracle uses strategically placed "(+)"s to indicate outer joins (both on join conditions and non-join conditions, if necessary). You're working in SQL Server, right? I've never worked with SQL Server. I'm not certain they support "old style" (specified in the WHERE clause instead of the FROM clause) outer joins. (In databases that don't support outer joins, you can simulate them with queries that use UNIONs and NULLs, but I haven't done this for awhile and am fairly rusty and it can get nightmarish if the query is at all complicated, and you tend to have some complicated queries.)

We've avoided all the RIGHT OUTER JOIN stuff.

For some reason, RIGHT OUTER JOINs just make my head hurt. Especially if combined in the same query with LEFTs. In general, RIGHT and LEFT joins are interchangeable -- just switch order of the tables when switching from RIGHT to LEFT or vice versa. When using newish join-in-the-from-clause syntax and making outer joins, I tend to cast everything as a cascade of LEFT OUTER JOINs. The only drawback to this approach is that you sometimes have a "driving" table somewhere in the middle of the cascade of LEFT OUTER JOINs. I actually prefer this than the approach that has the driving table in the "middle" with LEFT and RIGHT OUTER JOINS spilling off to either side.

Anyhow, did you get the count(*) issue straightened out?
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, this particular query is in MSSQL Server 2000.

I tried just switching all the RIGHTs to LEFTs, but it didn't work. There's a right join in the middle that doesn't have and "ON" ... which is why we are re-writing it.

Using the combination of RIGHT and LEFT, it worked (with the driving table in the middle), but it was very hard to add stuff. Hopefully this way will be easier.

Now I'm in the process of changing the WHERE to JOINs in the FROM. That will make it easier to add the LEFT OUTER JOINs I think.

They've (at least temporarily) dropped the requirement for the count().
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
MdQ: I tried just switching all the RIGHTs to LEFTs, but it didn't work.

To clarify, simply reversing the tables and switching LEFT <-> RIGHT works when you have a total of two tables. When you have more, you have to reorder the whole cascade of tables. In other words:

from A left join B on ...
left join C on ...

becomes

from C right join B on ...
right join A on ...

NOT

from B right join A on ...
right join C on ...
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
true.
When I took this

(notice that there is no "ON" after SCADMIN.assignmentm1 AS asm1)
and tried to work my way backwards from probsummarym1 to contactsm1,

it wouldn't run.
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
After thinking about this some more --



Note that there are two "ON"s at the end. I wonder if there might be some imaginary parens around part of this statement. Is that possible in SQL?
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
MdQ: Note that there are two "ON"s at the end. I wonder if there might be some imaginary parens around part of this statement. Is that possible in SQL?

You can write

from A left join B on ...
left join C on ...

as

from A left join B
left join C
on ... (stuff joining A and B)
on ... (stuff joining C to combination of A and B)

I think that's what you have going on here. The second ON at the end is just the condition that's joining asm1 to the rest. It could've been written as

<...>
RIGHT OUTER JOIN
SCADMIN.assignmentm1 AS asm1 ON assnmtOper.name = asm1.wdManagerName
<...>

If you missed that join condition when you rewrote as LEFTs, that may have been the problem causing it not to run.
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So just to be sure that I'm understanding this correctly,
I should be able to rewrite

as


??

Does it make any difference which field goes on the left side of the equals sign and which one goes on the right side? Would "psm1.number = relation.source" be equivalent to "relation.source = psm1.number"??
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Marilyn de Queiroz:
Does it make any difference which field goes on the left side of the equals sign and which one goes on the right side? Would "psm1.number = relation.source" be equivalent to "relation.source = psm1.number"??


Those are equivalent. It doesn't matter which field goes on the right/left side of the =.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic