Hidden features of SQL Server 2009 AF edition and mySQL9.1.4 - fuzzy logic

Question

It was reveled today that the new standard of SQL will include specification of some new database operations that will allow using databases for Fuzzy Logic operations. This new functionality is said to be implemented in SQL Server 2009 AF Edition and mySQL9.1.4

There were 2 new keywords introduced

The new keyword is called JOINT which can be used in the same way as a standard JOIN but will produce a fuzzy logic results the JOINT can be used with an optional HASH for even more fuzziness

For example the following 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 results for 'Joe Doe'

when 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 get the following result:

'Bob Marley'

using HASH JOINT in place of INNER JOINT will return even more results, like

'Cypress Hill'
'Bob Marley'

Warnings:

• the new keywords work only if you set your database locale to Amsterdam_CI any other locale setting will cause the following error:

Illegal operation. The the use of JOINT and HASH is not allowed.

• The extensive use of these keywords may cause a fatal error, system crash or produce no results at all.

• The results are nondeterministic

I did not experiment with the new functionality, but some say it may have a great potential.

My question:

• Do you know any examples of the creative use of this new functionality?
• What are the main cons and pros?
• any use that can cause errors or system crash?

Answer 1

You forgot to mention new MERGE JOINT algorithm in Oracle and a hint to force it, USE_MJ.

Answer 2

I think calling:

select * from users HASH JOINT cars

could result in system crash

Answer 3

Don't forget to pass your JOINT ByLeft