I've two table (t1 and t2) with 3 identical integer columns: c1, c2 and c3. I want to count how many value in t1 are in t2.SELECT count(t1.value) FROM t1 INNER JOIN t2 ON  (    t1.c1 = t2.c1 OR t1.c1 = t2.c2 OR t1.c1 = t2.c3 OR    t1.c2 = t2.c1 OR t1.c2 = t2.c2 OR t1.c2 = t2.c3 OR    t1.c3 = t2.c1 OR t1.c3 = t2.c2 OR t1.c3 = t2.c3)It doesn't seems a good way to write it (I'll have to add some columns). Is there a better solution to write it without enumerated any possibilities?I'm using MySQL version 5.6.
Check for similar value with SQL
performance;sql;mysql
Your question is not very clear... but, the way I understand it is:Collect all the unique values in t1, and count how many of those unique values appear in t2.Interesting problem.... instead of a straight join with all the or conditions, which may lead to an internal cross-product (thousands of joins and results to run comparisons on), I would state the logic as a couple of subselects ... which represent the two sets of data... the unique values in t1, and the unique values in t2.Note, the 'union' operator does a distinct as part of the union....select count(*)from  (      select c1 as val from t1    union      select c2 as val from t1    union      select c3 as val from t1  ) as t1vals,  (      select c1 as val from t2    union      select c2 as val from t2    union      select c3 as val from t2  ) as t2valswhere t1vals.val = t2vals.valThe code looks nicer this way, but requires scanning each table three times (which I think will be better than the potentially thousands of times it may have to happen with your query......I have put together an sqlfiddle for this