my dog learned polymorphism*
The moose likes JDBC and the fly likes Old style SQL vs new style SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Old style SQL vs new style SQL" Watch "Old style SQL vs new style SQL" New topic
Author

Old style SQL vs new style SQL

Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9053
    
  12
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?


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1757
    
    3
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

Joined: Jul 22, 2000
Posts: 9053
    
  12
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
Ranch Hand

Joined: Mar 25, 2001
Posts: 1757
    
    3
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

Joined: Jul 22, 2000
Posts: 9053
    
  12
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

Joined: Jul 22, 2000
Posts: 9053
    
  12
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
Ranch Hand

Joined: Mar 25, 2001
Posts: 1757
    
    3
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

Joined: Jul 22, 2000
Posts: 9053
    
  12
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"??
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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 =.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Old style SQL vs new style SQL