0

I have two tables, table1 and table2. I need to search occurrence of table1.col1 in col1.table2. How can I achieve this ?

I did the following statement, but I'm not experienced in mySQL. I'm not sure if this the right method to query from two different unrelated tables? col1 and col2 are strings.

select table1.col1, table2.col1 from table1, table2 where 
STRCMP(table1.col1, table2.col1)=0;

Is this correct statement for my purpose? Can it be optimized ?

Jury A
  • 19,192
  • 24
  • 69
  • 93

2 Answers2

2

The "best way" is to join both tables.

SELECT  a.*, b.*
FROM    table1 a 
            INNER JOIN table2 b
                ON a.col1 = b.col1
-- WHERE   --other condition here

SubQuery Method (but I'll prefer on joining tables)

SELECT  *
FROM    table1
WHERE   col1 IN
   (
     SELECT col1 
     FROM   table2
     -- WHERE -- condition here  
   )
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • another way is by using subquery, although it is slower than join. I'll update my answer. – John Woo Aug 20 '12 at 06:06
  • http://stackoverflow.com/questions/7357516/subquery-or-leftjoin-with-group-by-which-one-is-faster – rahularyansharma Aug 20 '12 at 06:09
  • @John Woo: What do yoou mean by WHER--condition here?? What I want to do is search for table1.col1 occurrence in table2.col1 .. Do I need any conditions ?? Or IN is enough to do the purpose ? – Jury A Aug 20 '12 at 07:34
  • i mean if you have any "extra" filtering condition :) but if not, you can just omit it. – John Woo Aug 20 '12 at 07:36
0

As John said Joins would be better :), but you could always put another SELECT statement in the WHERE clause

Jared Drake
  • 982
  • 4
  • 12