Skip to main content

Basic Joins

joins-inner joins-inner-3

All in common

joins-full join-full-3

All from both

joins-full-exclude join-full-exclude

None in common

joins-left joins-left-3

join-right-3

All from a table

joins-left-exclude join-left-exclude

join-right-exclude

None from a table

joins-cross joins-cross-3

Cartesian Product

joins-menuSet 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).

joins-innerAll 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:

joins-inner

joins-fullAll 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:

joins-full

 

joins-full-excludeNone 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:

joins-full-exclude

 

joins-leftAll 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:

joins-left

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:

joins-right

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.

joins-left-excludeNone 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:

joins-left-exclude

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:

joins-right-exclude

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.

joins-crossCartesian 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.

joins-cross

Leave a Reply

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close