Create Custom Joins

You can extend your database model with new joins without making any change to the database itself. Custom joins behave just like the built-in basic joins, except you can customize them as you wish.

For instance, let’s say you need to closer explore the relationship between customer names and actor names from a movie rentals database. Some actors  may be customers as well. They both have a first and a last name, so first thing to do is bring these tables in a new query, then drag customer’s first_name shape item and drop it over actor’s first_name item. You’ll be asked if you want indeed to create a new custom join. Confirm and you’ll immediately get a new connector between the shapes. Continue by dragging customer’s last_name and dropping it over actor’s last_name. Confirm you want to extend your custom join. Right click on the connector now and look at its Join Properties:

It’s interesting custom joins don’t have to always be equi-joins and you may use AND, OR, AND NOT and OR NOT concatenators.

Outer Custom Joins

What can you do now with your custom join that you cannot ordinarily do another way? Think about outer joins. Click on the right side of the connector, until you see the full bullet telling you you want ALL from actor (and only common records from customer). Right-click the connector and look at the Join Selection Type, to see this was indeed your choice. And, interesting enough, your query exposes quickly now that Jennifer Davis is an actor that happens to be also on of your customers:

Drill-Down on Custom Joins

Another advantage is whenever you use now one of these tables alone, you’ll see new related information when you drill-down. This query shows customers, but when the customer is also an actor, you get instant info to that record through drill-down:

ON vs USING

Both built-in and custom joins may use an alternative syntax when option Use USING in Joins, by default off, is turned on. When your database platform supports it, join syntax such as ON a.col1 = b.col1 AND a.col2 = b.col2 … is turned into USING (col1, col2, …). For this:

1. each join must be an equi-join,
2. each expression must be joined by AND,
3. each expression must have the same column names.