CODEWITHSUNDEEP
oracle-databaseselectouter-join
russds
russds

Reputation: 875

Difference between standard outer left join and join using select

Is there any difference between these two statements:

 -- Statement 1:
 SELECT *
 FROM Table1 t1
     LEFT OUTER JOIN TABLE2 t2 on t1.id = t2.id

and

  -- Statement 2:
  SELECT *
  FROM Table1 t1
     LEFT OUTER JOIN (SELECT id, a, b, c FROM Table2) t2 on t1.id = t2.id

I'm not an expert but statement 2 just looks like poorly written sql, and like it would take much longer. I'm attempting to optimize a code block and it has many joins like the second one. Are they technically the same and I can just replace with the standard join statement 1?

Thanks!

Ps. This is Oracle, and working with 100's of millions of rows.

PSS. I'm doing my own detective work to figure out if they are the same, and time differences, was hoping an expert could explain if there is a technical difference what it is.

Upvotes: 1

Views: 209

Answers (1)

sçuçu
sçuçu

Reputation: 3070

They are not same queries, with the lack of a criteria in the subquery that depends on whether the all columns and all column names of the TABLE2 is involved in the subquery. If the subquery involves all of the column names of the TABLE2 in the select list then they are the same query and the subquery is unnecessary. With subquery I refer to the part with a select statement after the join statement in the parens.

The first one uses the TABLE2 with its all columns, all those columns will be available in the result set where the criteria met.

However in the second one the table you make the JOIN is not the TABLE2 of yours but a table with just columns from TABLE2 specified in the subquery's SELECT list, namely id, a, b, and c. But it will have all the rows after this subquery since no criteria is enforced on it by a WHERE clause in the subquery.

You will have same number of rows with only selected columns participating from the TABLE2.

The second one is not necessarily the poorly written one. You could have a criteria to met before you JOIN to the TABLE2.

Upvotes: 1

Related Questions