PRESTO Left join using multiple operators

Question

I have 2 tables, one listing all our customers and then a 2nd listing every clients list of creditors/obligations
Firstly, I need to return all clients in t1 and then be able to determine if the client owns a vehicle or not by looking at t2
This query seems to do the trick and works as expected

    SELECT    t1.first_name,    t1.last_name,    t1.id_number,    CASE
          WHEN
             t2.uid IS NOT NULL 
          THEN
             1 
          ELSE
             0     END    AS vehicle_finance  FROM    db.client t1     LEFT JOIN
          db.obligation t2 
          ON t1.uid = t2.uid
          AND t2.creditor_name LIKE '%Vehicle Finance%'

But recently I discovered additional variants/keywords to include for the creditor name and this is the updated query I came up with

    SELECT
       t1.first_name,
       t1.last_name,
       t1.id_number,
       CASE
          WHEN
             t2.uid IS NOT NULL 
          THEN
             1 
          ELSE
             0 
       END
       AS vehicle_finance 
    FROM
       db.client t1 
       LEFT JOIN
          db.obligation t2 
          ON t1.uid = t2.uid
          AND lower(t2.creditor_name) LIKE '%vehicle finance%' 
          OR lower(t2.creditor_name) LIKE '%vehicle and asset finance%' 
          OR lower(t2.creditor_name) LIKE '%vehicle + asset finance%' 
          OR lower(t2.creditor_name) LIKE '%motor finance%'

Can I use the operators(AND & OR) like this in a LEFT JOIN after ON?
I am not worried about duplicate rows being returned as the client will have several obligations, I have another process that takes care of the duplicates
Is this query correct and most efficient way of achieving this?

Brendan McCaffrey · Answer 1 · 2022-02-28T18:47:36.227

Can I use the operators(AND & OR) like this in a LEFT JOIN after ON?

Yes, you can use AND and OR operators within your JOIN clause.

Is this query correct and most efficient way of achieving this? mysql.

The below conditions are going to cause you performance issues with your query.

lower(t2.creditor_name) LIKE '%vehicle finance%'
OR lower(t2.creditor_name) LIKE '%vehicle and asset finance%'

Anytime you wrap a function around a column, such as lower(t2.creditor_name), you are forcing the query engine to convert that column to lowercase for every row in the table, before it can compare it to something.
Putting a leading % in a LIKE condition is the equivalent of looking for '%oe, John' in the phonebook instead of 'Doe, Joh%'. Without knowing the first letter is D, someone would have to scan every page of the phonebook to find a record that could be a possible match.

Using these anti-patterns is generally a bad idea, and most often will make it very difficult for the query to utilize an index. You might not notice the impact initially, if your table only has a few hundred rows in it. However, as the data set grows, and you start getting into the 100s of thousands or millions of rows, it will most certainly matter.

score 0 · Accepted Answer · answered Feb 28 '22 at 21:54

There are several inefficiencies, and a few "errors". That is, I think the query would "run".

Filtering should go in a WHERE clause; ON controls how the tables are related.
Since it looks like you want to filter on creditor_name, use JOIN, not LEFT JOIN.
OR cannot be optimized
LIKE '%... cannot use an index
x AND y OR z is interpreted as (x AND y) OR z. Add parentheses to clarify what you want.
Since "true" is "1", the CASE clause simplifies to just t2.uid IS NOT NULL AS vehicle_finance.
Use a suitable collation instead of lower() or upper().
The query looks like it would "run".)

PRESTO Left join using multiple operators

2 Answers2