Dom Dom - 1 month ago 9
SQL Question

Resolve MySQL ambiguous column without prefixing table name and without using subquery

I've got a query which is failing with an ambiguous column error. Unfortunately, because of the framework I'm using (Magento 1), I can't just resolve this problem by prefixing the column name with the correct table name in the 'where' clause.

Given the two example tables

T1 = colA | colB | colC
T2 = colD | colB | colE


I want a query that will join the tables on
colA
and
colD
, select only
colC
and
colE
, but have a where clause on T1's
colB
. The straight forward SQL would look like:

SELECT colC, colE
FROM T1 LEFT JOIN T2 ON colA=colD
WHERE T1.colB=1


The problem is that the 'where' clause is already set by core code as
WHERE colB=1
and I can't easily change it to add the
T1
prefix.

I've tried rewriting it as a sub query like this:

SELECT colC,colE
FROM T1 LEFT JOIN (SELECT colD,colE FROM T2) ON colA=colD
WHERE colB=1


This works and gives me what I'd expect, but its unacceptable slow.

Are there any clever SQL tricks that can be used to either optimise the subquery, or rewrite the join so I can use the where clause without the table prefix.

Answer

How does this perform?

SELECT colC, colE 
FROM T1 LEFT JOIN
     T2
     ON colA = colD AND T1.colB = 1
WHERE T1.colB = 1;

Another alternative is a subquery that looks like this:

SELECT colC,
       (SELECT colE 
        FROM T2
        WHERE T1.colA = T2.colD
       ) as colE
FROM T1
WHERE colB = 1;

This should perform as fast as the original query (which is presumably taking advantage of indexes.

Comments