Outer Join Suggestions

Toad for Oracle
1

Hi Folks,

I am a Oracle PL/SQL developer and use TOAD everyday at work.
I have a question regarding a TOAD feature which might or might not be there.
Does TOAD have any feature which may suggest Outer Join automatically while joining the two tables which do not have all the matching key values for join columns ?
so that developers can have good control over the result set rather than discovering later that some of the records are missing or there is a mismatch.
I think this should save some time and gives surety towards the expected result set prediction for developers.
Sometimes it becomes difficult debugging the possible reason of data not being returned from a query with multiple tables in spite of almost being sure that the query should return rows and later finding out that one of the tables do not have the matching values for the join columns and there is Inner join on that.

–Unmesh

2

When writing your SQL in Editor you can use Code Insight (CTRL+T) to suggest join conditions. If there are explicit relationships between two tables using foreign key constraints then they will be suggested. If there are likely relationships between two tables then they will also be suggested. A relationship is determined to be likely if the field names and datatypes are the same in columns of two tables.

Example query...

SELECT *
  FROM user_tables ut LEFT OUTER JOIN 
       some_other_table ot ON

When Code Insight is invoked in the JOIN ON clause then you will see suggested conditions in addition to the columns of all tables involved.

image
image.png748×308 19.9 KB

Michael

3

Michael,

Thanks for the reply. I was looking for a auto-suggest on the JOIN itself, here in this case the [left outer join]

e.g.

Two tables A and B. I as a developer do not know whether all the values from A for a joined column exist in the table B. Hence I am unable to decide whether Inner join or Outer Join should work here.
If, based on the joining columns , i write following

  1. ( select A.columns,B.column,C.column from A Inner Join B using (ID) Inner Join C using (ID) ) – assuming A and C has all the values of ID in both the tables but B is a subset of A and C.

and as i am writing it, the join suggestion pops-up something like,

  1. ( select A.columns,B.column,C.column from A Inner Join [Left/Right Outer Join] B using (ID) Inner Join C using(ID) ) ,

it gives me a fair idea that not all the values from table A for the columns ‘ID’ exist in table B and in that case I choose to go for [Left Outer Join] instead of a Inner join that i was writing before, and that saves me the trouble of debugging the query and looking for Non existent values in ‘B’ if the above query (1) does not return me a result set, especially if it’s a big query with multiple tables and joins.

I have faced these scenarios at work and thought there might be such feature that i am not aware of. Although there is an option using the Outer join all the way in such a case.

Thanks,
Unmesh

4

I’m not sure I follow you 100%. Are you asking for Toad to detect that you might want an OUTER JOIN because table B may be missing some data from A? Toad won’t have any way of knowing if you should check your data with an OUTER JOIN without actually running the query and looking. That would be far too involved IMO for code insight assistance. Toad will help you write your own joins though so you can run your safety checks periodically and look for missing data.

5

Are you asking for Toad to detect that you might want an OUTER JOIN because table B may be missing some data from A?

-- Yes.

Toad won’t have any way of knowing if you should check your data with an OUTER JOIN without actually running the query and looking. That would be far too involved IMO for code insight assistance.

-- Now that i think about it, yes. So , using Outer join all across is the only solution in such scenario ?

Thanks,
Unmesh

6

However you want to query your tables to see the missing data you're looking for would be appropriate. I'm not a SQL expert so perhaps someone else here with more knowledge could assist you, but you can probably create your tables such that constraints can prevent some data error in the first place.