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

Question

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.

Do you have other restrictions, e.g. can the code where colB =1 be placed anywhere or does it has to end with it? Do you have a 1:1 relationship between your tables or can colA=colD get you several rows in T2 for a row in T1? Do you by any chance use mysql 5.7? — Solarflare
@Solarflare Magento builds the final query from objects, so I can't control the location of the 'where' clause and it will appear at the end of the query. For one use, there is a one to one relationship, but in the second use, there is a one to many relationship. I'm using MySQL version 5.5 — Dom

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-10-30T12:44:55

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.

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

1 Answers