Basic Joins

		All in common
		All from both
		None in common
		All from a table
		None from a table
		Cartesian Product

Set Join Type

To change a query join type:

• click on any connector edge.
• open connector’s Join Selection Type contextual menu.
• open connector’s Join Properties.

Join Patterns

Joins are links between two A and B tables/views/subqueries, represented as shapes. We recognize and represent:

• Symmetrical patterns: All in common, All from both, None in common.
• Asymmetrical patterns: All from a table, None from a table.
• Cartesian product of all paired rows, when there is actually no link between the tables.

In generated SQL queries, they translate into typical INNER JOIN, LEFT and RIGHT (OUTER) JOIN, and FULL (OUTER) JOIN (when supported).

• Inclusion patterns (All from …) appear as full black circles and translate into outer joins.
• Exclusion patterns (None from …) appear as empty circles and also translates into outer joins, but with transparent addition of one or more WHERE clauses (some keys must be NULL).

All in common

For this typical equality/equi-join (the default join in a query), both tables may have the same values on the keys defining the link, and this is the area represented as the intersection between the two circles. You may select only those rows, from each table, where there is such a match. In SQL, this translates into a INNER JOIN. This query will return only Artists with at least one Album, and Albums produced by known Artists:

All from both

Select both common and uncommon areas, from both tables. Cell values where there is no left or right match are filled with NULLs. Not all platforms support this form of FULL OUTER JOIN (or simply FULL JOIN), but it could be eventually simulated with a UNION between a LEFT and RIGHT JOIN. In addition to the previous query result, this data set will include as well Artists with no Album, and Albums produced by anonymous Artists:

None in common

This exclusion join removes the common area from the selection result. It’s the selection from “All from both”, from which you exclude the selection of “All in common”. In SQL, there is no separate join operator to represent this. But we can select “All from both” (with a FULL JOIN) and exclude, with a WHERE clause, the common portion, where the combination of key values from either column is NULL. The query will find only Artists with no Album, and Albums produced by anonymous Artists:

All from a table

Select all rows from one table and fill with NULLs if there is no match from the other table. This query will return all Artists (with or without Albums), and Albums produced by known Artists:

In SQL, this will translate into a typical LEFT or RIGHT OUTER JOIN, but you cannot enforce it as LEFT or RIGHT from the diagram. This is because A LEFT JOIN B is equivalent to B RIGHT JOIN A and the implementation detail is irrelevant for the end-user (read more about it in this blog post). You may select as well all from Album, in which case the query returns all Albums (with or without known Artists), and Artists with at least one Album:

As a general rule, if previous generated SQL query was A LEFT JOIN B, this one will be the asymmetrical A RIGHT JOIN B. Or, if it was A RIGHT JOIN B, this one will be A LEFT JOIN B.

None from a table

Select all rows from one table where there is no match with the other table. This query must exclude all Albums produced by known Artists. In other words, only Albums produced by anonymous Artists will be returned:

This translates into a LEFT or RIGHT JOIN, and an additional WHERE clause, to exclude rows where the combination of key values from the excluded table is NULL. You may exclude as well all Albums, in which case the query returns only those Artists without any Album:

As a general rule, if previous generated SQL query was A LEFT JOIN B, this one will be the asymmetrical A RIGHT JOIN B. Or, if it was A RIGHT JOIN B, this one will be A LEFT JOIN B. Also, the NULL keys from the WHERE clause come now from the other table to be excluded.

Cartesian Product

When there is no direct or indirect connection between two tables/views/subqueries in a query diagram, the cartesian product of their rows will be returned. Every row from one table will be matched against all rows from the other table.