Query Builder Specify From Table?

In the Query Builder, is there a way to specify which table is in the from clause when joining tables?

Hi Eric, Can you please clarify? When joining tables, all joined tables are in the FROM clause... An explicit example may help. If you select SQL Preview from the list to the right in the Query Builder toolbar, you see the generated SELECT statement. Best Regards, Hans

I'm sorry, I view the FROM with table reference as being a different construct from the JOIN language elements, but I suppose a FROM clause could be considered to contain the JOIN. Consider these 2 examples: select * FROM TABLEA a INNER JOIN TABLEB b on b.some_element = a.other_element versus select * FROM TABLEB b INNER JOIN TABLEA a on a.other_element = b.some_element If I drag TABLEA and TABLEB into the Query Builder and join by some_element and other_element, Query Builder can choose to place TABLEA or TABLEB as the table reference directly after the FROM. I'm wondering if I can have control over this. I just now realized that if place both tables in the Query Builder and drag the join from right to left, it puts the right table in the FROM. If I drag from left to right, it puts the left table in the FROM. If I add a 3rd table, it remains sticky to whatever it decided before, as far as I can tell. I don't know if execution plan would be affected or not, but mainly I'd like to see the main driving table in the first position, right after the FROM, for readability and understanding more quickly what a query is trying to do. It is more readable if generally the joins are essentially filters or add additional fields to the main reason for the query, which is right next to the FROM (in general).

I have a better example that just came up using the Query Builder: I have an application that has jobs and engravings that get performed on those jobs. Here is a simplified schema and steps to reproduce: CREATE TABLE "A_JOB" ( "ID" NUMBER(38) NOT NULL, CONSTRAINT "PK_A_JOBS" PRIMARY KEY ("ID") ); CREATE TABLE "A_ENGRAVING" ( "ID" NUMBER(38) NOT NULL, "A_JOB_ID" NUMBER(38), CONSTRAINT "PK_A_ENGRAVING" PRIMARY KEY ("ID") ); CREATE TABLE "A_TEXT_ENGRAVING" ( "ID" NUMBER(38) NOT NULL, "A_ENGRAVING_ID" NUMBER(38), CONSTRAINT "PK_A_TEXT_ENGRAVING" PRIMARY KEY ("ID") ); CREATE TABLE "A_IMAGE_ENGRAVING" ( "ID" NUMBER(38) NOT NULL, "A_ENGRAVING_ID" NUMBER(38), CONSTRAINT "PK_A_IMAGE_ENGRAVING" PRIMARY KEY ("ID") ); -- Notes: -- A_ENGRAVING.A_JOB_ID contains A_JOB.ID pointers -- A_TEXT_ENGRAVING.A_ENGRAVING_ID and A_IMAGE_ENGRAVING.A_ENGRAVING_ID point to -- A_ENGRAVING.ID Precondition: Query Builder does not have auto joins turned on. Steps: Drag into Query Builder in this order: A_JOB A_ENGRAVING A_TEXT_ENGRAVING A_IMAGE_ENGRAVING Now join, in this order: A_IMAGE_ENGRAVING.A_ENGRAVING_ID to A_ENGRAVING.ID A_TEXT_ENGRAVING.A_ENGRAVING_ID to A_ENGRAVING.ID A_ENGRAVING.A_JOB_ID to A_JOB.ID **** THE KEY PROBLEM HERE: **** View the query. A_IMAGE_ENGRAVING is in the table designation of the FROM clause. If I change them all to LEFT OUTER JOIN so that I can get all jobs, regardless of whether there are engravings, it won't work. I can only get jobs that have A_IMAGE_ENGRAVING rows. Here is the query I end up with: SELECT * FROM ASCHEMA.A_IMAGE_ENGRAVING LEFT OUTER JOIN ASCHEMA.A_ENGRAVING ON (ASCHEMA.A_IMAGE_ENGRAVING.A_ENGRAVING_ID = ASCHEMA.A_ENGRAVING.ID) RIGHT OUTER JOIN ASCHEMA.A_TEXT_ENGRAVING ON (ASCHEMA.A_ENGRAVING.ID = ASCHEMA.A_TEXT_ENGRAVING.A_ENGRAVING_ID) LEFT OUTER JOIN ASCHEMA.A_JOB ON (ASCHEMA.A_ENGRAVING.A_JOB_ID = ASCHEMA.A_JOB.ID) ; I'm also unclear as to why it insists on a RIGHT OUTER JOIN when I specified LEFT OUTER JOIN. I realize it might be trying to help me, but I can't override that. That seems like a problem too.

Hi Eric, Thanks for the detailed example. The order of the tables in the FROM/JOIN clauses is based on the order you add the joins, as you have already discovered. There is no way to rearrange the order in the Query Builder itself, but you can load the generated SQL into the SQL Commander editor and do it manually. If you don't like the JOIN clause syntax, you can also disable "Generate JOIN Clause in Query Builder" in the Properties tab for the connection, in the Query Builder category. It then generates a regular FROM clause with the join conditions in the WHERE clause, and the tables in the FROM clause order in the rode you add them. The fact that you get a RIGHT OUTER JOIN even though you select LEFT OUTER JOIN for all of them appears to be a bug. We will look into that, but as a work around you can set that join to a RIGHT OUTER JOIN instead: it will then generate the LEFT OUTER JOIN you want (i.e., the bug cause the JOIN to be reversed). I hope this helps. Best Regards, Hans

Hi Eric, I'm no longer sure the RIGHT vs LEFT OUTER JOIN behavior is a bug, as the code is doing this reversal intentionally. I don't remember the details for why it is this way, but it has to do with in which direction the join is created and that you have two joins with the same target column: A_IMAGE_ENGRAVING.A_ENGRAVING_ID to A_ENGRAVING.ID A_TEXT_ENGRAVING.A_ENGRAVING_ID to A_ENGRAVING.ID If you instead draw the second one as A_ENGRAVING.ID to A_TEXT_ENGRAVINGÅ_ENGRAVING_ID you get LEFT OUTER JOIN for both. Best Regards, Hans