• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54755
  • Last Modified:

LEFT JOIN subquery

Hi,

The following is a simplified version of a complex query i wrote.

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
                FROM table3
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x


and it works... but it is slower since the subquery fetches whole table again and again.
Is there any way to make this faster by adding a condition inside subquery...
I want to do something like this.

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
                FROM table3
                 WHERE  table3.x = table1.x    <<<<<<------------- does not work // Unknown table 'table1' in where clause
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x

Any alternative query is welcome.

Thanks
0
venkateshwarr
Asked:
venkateshwarr
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
I dont find any erorrs there, check whether 'Table1' already exists on your database
0
 
HuyBDCommented:
Hi
0
 
HuyBDCommented:
Hi venkateshwarr!
To avoid use sub query, try this:

SELECT table3.x, max(table3.y)
from table3
left join table1 on table3.x=table1.x
GROUP BY table3.x
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
venkateshwarrAuthor Commented:
Hi aneesh,

I am not sure about other databases, but it does not work in mysql.

HuyBD,

As I said before, this is a pretty complex query and I cant do what u said.
0
 
LowsanCommented:
Yes use of table1 in subquery is suported for example in oracle but in mysql won't work

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
               FROM table3
                 inner join table1 using (x)
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x

wouldn't this speedup yur query if table 3 is really big and use the same where clouse in sub query and outside it?
0
 
todd_farmerCommented:
Have you tried this:

SELECT (SELECT table3.x, max(table3.y)
               FROM table3, table1
                 WHERE  table3.x = table1.x
                 AND table1.x=table2.x
                 GROUP BY table3.x)
FROM table1;

0
 
LowsanCommented:
Same result will produce:

SELECT table3.x, max(table3.y)
               FROM table3
                 inner join table1 using (x)
                 GROUP BY table3.x

but meabye you were looking for something like this:

SELECT table2.x, table2.y
FROM table1
LEFT JOIN (SELECT table3.x, max(table3.y)
               FROM table3
                 WHERE table3.x IN (SELECT table1.x FROM table1)
                 GROUP BY table3.x) AS table2
         ON table1.x = table2.x

or to not call queries on table1 twice you could create temporary table with records from table1 you want to use

create temporary temp_table1
      SELECT table1.x FROM table1
       WHERE..........

then just:

SELECT table2.x, table2.y
FROM temp_table1
LEFT JOIN (SELECT table3.x, max(table3.y)
               FROM table3
                 WHERE table3.x IN (SELECT temp_table1.x FROM temp_table1)
                 GROUP BY table3.x) AS table2
         ON temp_table1.x = table2.x

Will this be quicker enough?
0
 
venkateshwarrAuthor Commented:
Thanks for all ur suggestions.. I am increasing to 500 pts.
0
 
venkateshwarrAuthor Commented:
LEFT JOIN with subquery is slow...
I am accepting HuyBD as it is faster compared to other solutions.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now