It had been reveled today the new standard of SQL includes specs of newer and more effective database procedures that will permit using databases for Fuzzy Logic procedures. This new functionality is stated to become implemented in SQL Server 2009 AF Edition and mySQL9.1.4

There have been 2 new key phrases introduced

The brand new keyword is known as JOINT which may be used in the same manner like a standard JOIN and can create a fuzzy logic results the JOINT may be used by having an optional HASH for more fuzziness

For instance the next query

select top 10
    m.artistName 
from 
    users u
    inner join userFavMusic ufm on u.userID = ufm.userID
    inner join music m on ufm.musicID = m.musicID
where
    u.fullName = 'Joe Doe'

returns no recent results for 'Joe Doe'

whenever we change join to joint operation

select 
    u.fullName, m.artistName 
from 
    users u
    INNER JOINT userFavMusic ufm on u.userID = ufm.userID
    INNER JOINT music m on ufm.musicID = m.musicID
where
    u.fullName = 'Joe Doe'

we obtain the next result:

'Bob Marley'

using HASH JOINT instead of INNER JOINT will return much more results, like

'Cypress Hill'
'Bob Marley'

Alerts:

  • the brand new key phrases work only when you place your database locale to Amsterdam_CI every other locale setting may cause the next error:

Illegal operation. The using JOINT and HASH isn't permitted.

  • The extensive utilization of these key phrases could cause a fatal error, system crash or produce no results whatsoever.

  • The outcomes are nondeterministic

I didn't test out the brand new functionality, however, many say it could have a great potential.

My question:

  • Are you aware any good examples from the creative utilization of this new functionality?
  • Do you know the primary cons and pros?
  • any use that induce errors or system crash?

You didn't remember to say new MERGE JOINT formula in Oracle along with a hint to pressure it, USE_MJ.

I believe calling:

select * from users HASH JOINT cars

could cause system crash

Be sure to pass your JOINT ByLeft