sql table join:efficient syntax

I have an SQL like this:

select q1.*,q2* from (select * from t1,t2,t3 where t1.col1=t2.col1 and t2.col2=t3.col2) q1, (select * from p1,p2,p3 where p1.col1=p2.col1 and p2.col2=p3.col2) q2 where q1.col1 = q2.col1;

Assuming col1, c开发者_JAVA技巧ol2, col3 are all unique (i.e. no naming conflicts) and wildcards are replaced by proper col names...is this a proper way to write this query?

It is very complex to read.

How can it be made more efficient?

Also, when we do an outer join, are the syntaxes 'left join' and t1.col1 = t2col1(+) equivalent in all senses?

Please let me know if more info is required.

First, I would abandon the habit of creating Cross Joins via a comma-delimited list of tables (e.g. t1,t2,t3). Instead you should embrace the ANSI Join syntax. Second, you can combine both derived tables into a single query. Third, I suggest explicitly declaring the columns you want rather than using Select * (in the below case, I simply used an ellipsis to indicate that you need to fill in the column names you want). Lastly, I would not use any sort of + syntax to indicate Left Joins. Instead, use the Left Join syntax

Select ...
From T1
    Join T2
        On T2.Col1 = T1.Col1
    Join T3
        On T3.Col2 = T2.Col2
    Join P1
        On P1.Col1 = T1.Col1
    Join P2
        On P2.Col1 = P1.Col1
    Join P3
        On P3.Col2 = P2.Col2
    Left Join X1
        On X1.Col1 = T1.Col1

"Proper" is in the eye of the beholder to some extent. I might try creating views from the subqueries, and joining on the views.

Also, when we do an outer join, are the syntaxes 'left join' and t1.col1 = t2col1(+) equivalent in all senses?

I'm sure that was the intent. I know that didn't happen in the earliest Oracle releases that supported ANSI join syntax. I don't recall the details, though.

You have to use ANSI syntax everywhere but an Oracle shop, and you have to use it in a lot of Oracle shops, too.

I think there were differences in the order of evaluation that could lead to different results. That's basically because Oracle implemented their own outer join syntax before ANSI defined how outer joins were supposed to work.