Join Notation

Cross Joins

We transparently link any isolated selectable shape with one other shape from the query diagram, using a cross join. We use by default the ANSI-92 CROSS JOIN notation between the two tables. Except for Microsoft Access and Sybase/SAP ASE, which still use only the old ANSI-89 comma (,) notation. Turn Use Comma for CROSS JOIN setting on (by default off) to always use a comma for any cross join.

Standard Join Syntax

The query below lists all employees working on projects, with their project id and the department name, as well as manager’s name (if any). Look at the SQL syntax with both Use NATURAL JOIN and Use USING switches off (by default both are off). You should see an INNER JOIN (for the connector), and two LEFT JOIN (one for each lookup field). The standard join syntax uses INNER/LEFT/RIGHT JOIN, always with an ON clause.

Natural Join Syntax

When Use NATURAL JOIN option (by default off) is turned on, we try to express any detected natural join with the NATURAL [LEFT/RIGHT] JOIN syntax (if supported by your database platform), which does not require the ON clause. Natural joins are linked on all columns with the same name from each joined table. Our features work on the built-in, custom and chain joins represented by connectors in the query diagrams, as well as the invisible joins we automatically add for lookups.

Look at the previous query after turning Use NATURAL JOIN on. You should see now the INNER JOIN replaced with a NATURAL JOIN (for the connector), and one of the lookups expressed as well with a NATURAL LEFT JOIN. The only join with different column names is the one for employee’s manager. This is also a self join, and we never look for natural joins in self joins.

NATURAL JOIN syntax is supported by Oracle, MySQL, MariaDB, Amazon Aurora, Amazon Redshift, PostgreSQL, SQLite, Firebird and SQL Anywhere. It is not supported by Microsoft SQL Server, SQL CE, SQL Azure, Access, Sybase/SAP ASE, Ingres and IBM DB2.

Using Join Syntax

When Use USING option (by default off) is turned on, we try to express any detected join with the USING syntax (if supported by your database platform), which replaces the ON clause when all joined columns have the same name. Unlike natural joins, these joins may leave some columns with the same name out of the join clause. For instance, if two tables have two matching columns id and name found in each of them, a natural join will automatically link on all these columns, while USING may be used as well when the link is only on the id columns, or the name columns.

Look at the previous query after turning Use USING on and Use NATURAL JOIN off (NATURAL JOIN checks come always before USING). Upon query’s SQL refresh, you should see the USING clause used in the INNER JOIN (for the connector) and in the LEFT JOIN (for the department name lookup).

USING syntax is supported by Oracle, MySQL, MariaDB, Amazon Aurora, Amazon Redshift, PostgreSQL, SQLite, Firebird and Ingres. It is not supported by Microsoft SQL Server, SQL CE, SQL Azure, Access, Sybase/SAP ASE, SQL Anywhere and IBM DB2.

All previous queries should always return the same result. Whenever natural joins are not supported by the platform, but one of these switches is turned off and we detect some, we show an warning.