How to use like in a comparison of fields in different tables?

Question

How can I use the LIKE for a comparison of two different table fields? I need to compare the first 5 characters of each field. I tried with SUBSTRING and with LEFT, however, the performance gets very bad.

Here are two ways that I applied, with the commentary signaling: 1)

    SELECT(SELECT sum(sd3_sub1.D3_QUANT)
                FROM SD3010 AS sd3_sub1         
                WHERE sd3_sub1.D3_TM = '010'                    
                    AND YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO)  AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)
                    AND sd3_sub1.D3_LOCAL BETWEEN '01' AND '02'
                    AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                    /*AQUI UTILIZEI O LEFT*/   
                    AND LEFT(sd3_sub1.D3_CC, 5) = LEFT(sd3.D3_CC,5)
                    AND sd3_sub1.D_E_L_E_T_ <> '*') AS producao
    FROM SD3010 AS sd3

2)

    SELECT(SELECT sum(sd3_sub1.D3_QUANT)
                    FROM SD3010 AS sd3_sub1         
                    WHERE sd3_sub1.D3_TM = '010'                    
                        AND YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO)  AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)
                        AND sd3_sub1.D3_LOCAL BETWEEN '01' AND '02'
                        AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                        /*AQUI UTILIZEI O SUBSTRING*/   
                        AND SUBSTRING(sd3_sub1.D3_CC,1,5) = SUBSTRING(sd3.D3_CC,1,5)
                        AND sd3_sub1.D_E_L_E_T_ <> '*) AS producao
    FROM SD3010 AS sd3

Answer 1

In that case a JOIN is more interesting than a subquery. Follow the example for the case 2:

SELECT sum(sd3_sub1.D3_QUANT) AS producao
  FROM SD3010 AS sd3 WITH(NOLOCK)
       INNER JOIN SD3010 AS sd3_sub1 WITH(NOLOCK) ON sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
  WHERE DATEPART(YEAR, sd3_sub1.D3_EMISSAO) = DATEPART(YEAR, sd3.D3_EMISSAO)
    AND DATEPART(MONTH, sd3_sub1.D3_EMISSAO) = DATEPART(MONTH, sd3.D3_EMISSAO)
    AND SUBSTRING(sd3_sub1.D3_CC,1,5) = SUBSTRING(sd3.D3_CC,1,5)
    AND sd3_sub1.D3_TM = '010'
    AND sd3_sub1.D3_LOCAL BETWEEN '01' AND '02'
    AND sd3_sub1.D_E_L_E_T_ <> '*'
  GROUP BY sd3.D3_FILIAL

There were also fields without alias.

I also added the WITH(NOLOCK) also, which indicates for the SQL Server that it is not necessary to block the table while reading the data is performed.

Answer 2

Thiago, considering that the column D3_EMISSAO is probably declared as varchar(8), and with the value stored in the format yyyymmdd, it seems unnecessary, and perhaps inefficient, the use of the functions Year() and Month() for comparison between the external and internal consultation, and the comparison can be made directly in the environment string.

For example, instead of

... YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO)  
AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)

can be used

Left(sd3_sub1.D3_EMISSAO, 6) = Left(sd3.D3_EMISSAO, 6)

Both the use of Year/Month functions and the Left function, in this specific case, make the restriction non sargable. There is even how to make this construction sargable, when implementing something like

sd3_sub1.D3_EMISSAO between (Left(sd3.D3_EMISSAO, 6) + '01') 
                            and (Left(sd3.D3_EMISSAO, 6) + '31')

---

I don’t think that the cause of low performance is the use of the Left (or Substring) function in the comparison of the D3_CC column. The construction

LEFT(sd3_sub1.D3_CC, 5) = LEFT(sd3.D3_CC,5)

didn’t seem inefficient except, of course, for being a construction non sargable. But there are several other restrictions in the WHERE clause that also make it non sargable.

However, there are situations where LIKE can be more efficient than Left or Substring. But this depends on a conjunction of factors such as available indices, the list_de_columns, the construction containing the LIKE etc. In the case of your consultation, perhaps the construction

       and sd3_sub1.D3_CC like Left(sd3.D3_CC,5) + '%'

can be more efficient.

---

Here is suggestion for your code, where the constructions non sargable were replaced by other sargable:

-- código #1 v3
SELECT ...,
       (SELECT Sum(sd3_sub1.D3_QUANT)
          from SD3010 as sd3_sub1         
          where sd3_sub1.D3_TM = '010'                    
               and sd3_sub1.D3_LOCAL between '01' AND '02'
               and sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
               and sd3_sub1.D3_EMISSAO between (Left(sd3.D3_EMISSAO, 6) + '01') 
                                           and (Left(sd3.D3_EMISSAO, 6) + '31')
               and sd3_sub1.D3_CC like Left(sd3.D3_CC,5) + '%'
               and sd3_sub1.D_E_L_E_T_ <> '*') as producao
  from SD3010 AS sd3
  where sd3.D_E_L_E_T_ <> '*'
        and ...;