Understanding the use of curly braces and "OJ" in an SQL query

Question

A previous employee in my office created a php script that uses curly braces in an SQL query. I understand that it usually works for things like encapsulating array variables, but this query does not contain any PHP variables in the string. Can anyone clarify that the table generated in the FROM section of the query really requires curly braces and what does OJ mean?

 SELECT DISTINCT ra.folder_id, pd.id, f.name, pd.descriptor_text FROM { OJ permission_descriptors pd LEFT JOIN permission_descriptor_users pdu ON pdu.descriptor_id = pd.id } role_allocations ra, folders f WHERE pdu.descriptor_id IS NULL AND pd.id = ra.permission_descriptor_id AND pd.id != 1 ra.folder_id = f.id ORDER BY ra.folder_id 

Answer 1

MySQL supports this alternative syntax for Outer Join. However, this does not mean that it should be used.

• It may be problematic to have RDBMS-specific code if at some point you need to switch to another DBMS.
• MySQL does not seem to support this syntax for more than two joins.

Besides:
Another non-ANSI problem with code is subsequent merging.

This is a quick hit on the ANSI compatible version (not tested):

 SELECT DISTINCT ra.folder_id, pd.id, f.name, pd.descriptor_text FROM permission_descriptors pd LEFT JOIN permission_descriptor_users pdu ON pdu.descriptor_id = pd.id LEFT JOIN role_allocations ra ON pd.id = ra.permission_descriptor_id LEFT JOIN folders f ON ra.folder_id = f.id WHERE pdu.descriptor_id IS NULL AND pd.id <> 1 ORDER BY ra.folder_id; 

Other notes:
For inequality != Will work, but <> is preferred.

Answer 2

You can remove "{ok" and "}" and SQL will work as before, not in ODBC.

 escaped_table_reference: table_reference | { OJ table_reference } 

The {OJ ...} syntax shown in the connection syntax description exists only for ODBC compatibility. Curly braces in the syntax should be written literally; they are not metasyntactic, as description is used elsewhere in the syntax.

SELECT left_tbl. * FROM {OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id} WHERE right_tbl.id IS NULL;

You can use other types of connections inside {OJ ...}, for example INNER JOIN or FORWARD. This helps with compatibility with some third-party applications, but is not an official ODBC syntax.

Ref