Joining tables
When you use the SQL builder to build SELECT statements, you can specify table joins in a statement.
When you drag multiple tables onto the table selection canvas, the SQL builder attempts to create a join between the table added and the one already on the canvas to its left. If foreign key metadata is available for the tables, the SQL builder uses it. The join is represented by a line joining the columns the SQL builder has decided to join on. After the SQL builder automatically inserts a join, you can amend it.
When you add a table to the canvas, SQL builder determines how to join the table with tables that are on the canvas. The process depends on whether the added table is positioned to the right or left of the tables on the canvas.
To construct a join between the added table and the tables to its left:
- SQL builder starts with the added table.
- Determine if there is a foreign key between the added table and
the subject table.
- If a foreign key is present, continue to Step 3.
- If a foreign key is not present, skip to Step 4.
- Choose between alternatives for joining the tables that is based
on the following precedence.
- Relations that apply to the key fields of the added tables
- Any other foreign key relation
Construct an INNER JOIN between the two tables with the chosen relationship dictating the join criteria.
- Take the subject as the next table to the left, and try again from step 2 until either a suitable join condition has been found or all tables, to the left, have been exhausted.
- If no join condition is found among the tables, construct a default
join.
If the SQL grammar does not support a CROSS JOIN, an INNER JOIN is used with no join condition. Because this produces an invalid statement, you must set a suitable condition, either through the Join Properties dialog box, or by dragging columns between tables.
An INNER JOIN is used with no join condition. Because this produces an invalid statement, you must set a suitable condition, either through the Join Properties dialog box, or by dragging columns between tables.
- SQL builder starts with the added table.
- Determine if foreign key information exists between the added
table and the subject table.
- If a foreign key is present, continue to Step 3.
- If a foreign key is not present, skip to Step 4.
- Choose between alternatives based on the following precedence:
- Relations that apply to the key fields of the added tables
- Any other joins
Construct an INNER JOIN between the two tables with the chosen relationship dictating the join criteria.
- Take the subject as the next table to the right and try again from step 2.
- If no join condition is found among the tables, construct a default
join.
If the SQL grammar does not support a CROSS JOIN, an INNER JOIN is used with no join condition. Because this produces an invalid statement, you must set a suitable condition, either through the Join Properties dialog box, or by dragging columns between tables.
An INNER JOIN is used with no join condition. Because this produces an invalid statement, you must set a suitable condition, either through the Join Properties dialog box, or by dragging columns between tables.