Return rows from MYSQL table A where table_A.col_1 = table_b.col_2 without using a left outer join?

Question

In a MySQL database with two tables table_A and table_B I want to return selected row columns from table_A based on comparison with values in table_B. The below erroneous line sums up the idea:

SELECT col_1, col_2, col_3 FROM table_A where table_A.col_1 = table_B.col_2;

I do not want any elements from table_B.

Why I can't use a left outer join: I've tried this with a left outer join as illustrated here(https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) however the database complains that the column names are ambiguous and changing table column names in the database isn't an option.

Answer 1

I you want rows in table_A whose col1 can be found in table_B(col_2), you can use exists:

select a.col_1, a.col_2, a.col_3 
from table_A a 
where exists (select 1 from table_B b where b.col_2 = a.col_1);

If you want rows that do not exist in table_B, then just change exists to not exists.

Note that I prefix the column names with the (alias of the) table they belong to. This is called qualifying the columns, and is how you avoid the ambiguous column name problem that you seemingly have met when trying to join.