Select com like

declare @COLUNAI  table (
CODIGO int, 
DESCRICAO varchar(20))

insert into @COLUNAI (CODIGO, DESCRICAO) values (1,'automovel')
insert into @COLUNAI (CODIGO, DESCRICAO) values (3,'outro')
insert into @COLUNAI (CODIGO, DESCRICAO) values (2,'automato')

declare @COLUNAII table (
CODIGO int, 
DESCRICAO varchar(20))
insert into @COLUNAII (CODIGO, DESCRICAO) values (1,'automovel')
insert into @COLUNAII (CODIGO, DESCRICAO) values (2,'zunga')
insert into @COLUNAII (CODIGO, DESCRICAO) values (3,'filipeta')
insert into @COLUNAII (CODIGO, DESCRICAO) values (4,'automato')

SELECT	ii.codigo,
		ii.descricao
FROM @COLUNAII ii
WHERE  substring(ii.DESCRICAO ,1,3) in  (select substring(i.DESCRICAO ,1,3) 
										from @COLUNAI i )

codigo      descricao
----------- --------------------
1           automovel
4           automato