SQL+JQL is built on top of the popular H2 database engine. H2 is a high-performance database, however, SQL+JQL resolves all the queries against the Jira Java API, checking users' permissions, etc.

JQL vs SQL strings

Strings:

• single quotes in SQL
• double quotes in JQL

SELECT
 KEY(JQL.ISSUE)
FROM
  JQL
WHERE
  JQL.QUERY = ' status = "Open" '

Pay attention to the condition:

JQL.QUERY = ' status = "Open" '

In the query above:

"Open"

is a string in a JQL that will be parsed by Jira,  whereas: 

' status = "Open" '

is a string that will be parsed by the SQL+JQL engine.

Make Jira do as much work as possible

JQL performs much better than SQL+JQL. So, let Jira do as much work as possible → Move conditions into JQL as much as possible, instead of SQL.

Example:

SELECT *
FROM 
  issues i
WHERE 
  i.jql = ' reporter = currentUser() ' 
   AND
  i.assignee = 'John Smith'

The query above will get all the current user's issues. Then, the engine will check whether the assignee is John Smith for all the fetched issues. If the currentUser() reported N issues, checking the assignee by the SQL+JQL engine will occur N times.

However, the query below the SQL+JQL engine will rely on the Jira JQL engine to search for all the current user's issues assigned to John Smith which will return M records where M <= N.

SELECT *
FROM 
  issues i
WHERE 
  i.jql = ' reporter = currentUser() AND assignee = "John Smith" ' 

Joining SQL tables via dynamic JQL

I.e: how to get all the issues fixing any version of a project?

SELECT 
  key(jql.issue) as "Issue" 
FROM
 PROJECTVERSIONS pv 
    JOIN
 JQL ON query = 'fixVersion = ' || pv.id
WHERE
  pv.projectid = 10001

Note that the JQL query is built dynamically: 

'fixVersion = ' || pv.id

taken the project verion id as input.

The result of the above sentence is a text string that will be executed and resolved by the JQL table returning all the issues in that project version.

Next steps

Read about the Performance