Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL Server where clause joins

  1. Misc
0
Votes
Undo
  1. Roland Smith
    Roland Smith
  2. PowerBuilder
  3. Tuesday, 11 June 2019 18:04 PM UTC

SQL Server dropped support for outer joins in the where clause in SQL Server 2012. According to our DBA, they are thinking of dropping equal joins from the where clause.

The database profile in PB/IM has the option to set outer join style. I think that Appeon should make this option apply to all types of joins and make the JOIN clause the default. Even better, eliminate the option altogether. In any case, they really should get ahead of the curve and do this before one of the db vendors pulls the plug on the where join.

 

 

How can I change a menu item visible property to f...
How to Turn Off UI Themes?
Responses (3)
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Wednesday, 12 June 2019 15:07 PM UTC
  2. PowerBuilder
  3. # 1

Even if Microsoft continues to support it, PowerBuilder should have the option that the SQL designer make all joins be in JOIN clauses.

Comment
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Wednesday, 12 June 2019 14:03 PM UTC
  2. PowerBuilder
  3. # 2

Coming from an Oracle background all our joins are in the where clause, makes me wonder why removing functionality is progress!?

Comment
Roland Smith
  1. Roland Smith
  2. Wednesday, 12 June 2019 15:05 PM UTC
I'm only going by what my DBA said. I wasn't able to find anything where Microsoft said anything about it.
  1. Helpful
David Peace (Powersoft)
  1. David Peace (Powersoft)
  2. Wednesday, 12 June 2019 15:21 PM UTC
I know they stopped supporting * (star) joins in SQL Server 2008 unless you were runnign in compatability mode and eventually stop support totally.



I cannot saythat I have hit any problems with SQl Server 2014, but that doe snot mean that it's not ture... more that the applications are not using that sort of syntax.



I guess we will cross that bridge when we get to it. As Michael said, where the join requires some OR or between logic then you still need the where clause.
  1. Helpful
There are no comments made yet.
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 11 June 2019 19:19 PM UTC
  2. PowerBuilder
  3. # 3

Hi Roland,

Personally I prefer joins in the FROM clause because it is easier to distinguish data navigation (joins) from other restrictions (rest of WHERE clause). So I certainly favor the JOIN syntax as the target for DW painter generation.

However, I can't really see how you can evolve SQL syntax in a positive way by removing equi-joins.

I can't help but think: What about non-equi joins - like:

SELECT ...
FROM ...
WHERE ...
AND Task.DueDate BETWEEN PlanPeriod.BeginDate AND PlanPeriod.EndDate

Basically that is a JOIN restriction.

Just my 2 euro-cents. /Michael

Sidebar: I really liked SQL Anywhere's KEY JOIN syntax to eliminate the hassle of writing multi-column equi-joins in full - only to go fix every SELECT statement because someone replace a multi-column join with a one-column identity PKEY

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.