Usually the first step of building a SQL query would be to determine how to join two or more tables. We've added a new feature to help in this process. This is the Analyze Joins utility. This is available in the ER Diagramer as well as the Query Builder.
Wherever you have a join, whether it's one that's defined in the database by foreign key, like this one here, or whether you have made a manual join by dragging two columns together, you will be able to see a new icon. And this is the Analyze Join icon. And when you press on this icon, you will see a Venn diagram. Now this diagram shows you the relationship of the data in the two tables.
Here we can see that there is 1,070 rows that are only available in the Address table. And that there are 247 values in Address, that are also in Contact. And then we can see that there's 1,000 rows that are in Contact that are also Address. Now to see the exact values you can click on each of these areas. So if you click here, we'll see the 1,070 rows that are in Address, but not in Contact.
Here we can click on the 247, and we can see them grouped by their relationship, as well as the same in the Contact. For full viewing of the Venn diagram, you can click on this right corner, and it will go full size for you. Now this diagram can help you determine if you need an inner, outer, or full join. You can then send it to the Query Builder, or you can pick columns and build your query. But the Venn diagram is still available here. So you can click on that and get the same utility.
Now there's going to be times when there are no rows common to both tables. And in this event your join can produce a Cartesian join. You really want to avoid this type of query, as it's very resource intensive and usually doesn't have any value.
In this case, the analyze join is going to add this red area, which will tell you the number of extra rows that would be generated if you were to execute this query. So when you see this red circle, hold off. Rethink that join. In all, the analyze feature is very simple, but it can be very useful when building your SQL.
02:54