how to avoid cartesian product in sql-CodePudding

I have two tables and have to join on Col1,col2 and Col3 and get all rows from T1.

T1
    Col1  Col2    Col3      Col4
    4     S       20220714  123
    4     S       20220714  111
    
T2    
    Col1  Col2    Col3      
    4     S       20220714  
    4     S       20220714

When I join T1 with T2 I get 4 rows instead of two.

select t1.* from Table1 t1
join Table2 t2
on t1.Col1=t2.Col1
and t1.Col2=t2.Col2
and t1.Col3=t2.Col3

Result I am getting is:

Col1  Col2    Col3      Col4
4     S       20220714  123
4     S       20220714  111
4     S       20220714  111
4     S       20220714  123

But I would like to result as

Col1  Col2    Col3      Col4
4     S       20220714  123
4     S       20220714  111

I even tried row number and its not helpful. I am missing a very small logic here. Can anyone help me?

CodePudding user response：

If you don't need any columns from table 2 just use exists

select t1.*
  from table1 t1
 where exists
         ( select 1
             from table2 t2
            where t1.col1 = t2.col1
              and t1.col2 = t2.col2
              and t1.col3 = t2.col3
         )