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

Question

A previous employee at my office constructed a php script that uses curly braces in the a SQL query. I understand that it would normally work for things like encapsulating array variables, but this query doesn't have any PHP variables in the string. Can someone clarify what the table being generated in the FROM section of the query is actually requiring the curly braces for, and what the OJ stands for?

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

Google uncovers [this link](http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj235.html).The google search was "OJ Syntax". — Chris Cunningham, Jul 20 '11 at 19:03

score 4 · Accepted Answer · edited Oct 04 '21 at 18:06

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

It can be problematic to have RDBMS-specific code if at some point you need switch to another RDBMS.
Googling around, it appears that MySQL doesn't support this syntax for more than 2 joins.

Aside:
Another non-ANSI-compliant issue with the code is the subsequent joins.

This is a quick stab at an ANSI-compliant 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.

Why is <> preferred? I personally use != due to its similarity in other languages. — dotancohen, Jan 21 '12 at 19:20
Thanks for the question. Maybe 'preferred' isn't the best word. The `<>` operator is [ANSI-compliant](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) SQL. Nevertheless many RDBMSs [allow both](http://stackoverflow.com/a/723426/42346). — mechanical_meat, Jan 21 '12 at 19:34

score 1 · Answer 2 · answered Jun 06 '17 at 18:10

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

escaped_table_reference:  
    table_reference  
  | { OJ table_reference }

The { OJ ... } syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

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 joins within { OJ ... }, such as INNER JOIN or RIGHT OUTER JOIN. This helps with compatibility with some third-party applications, but is not official ODBC syntax.

Ref

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

2 Answers2

Linked