Filter table before applying a LEFT JOIN

Question

I have two tables and I want all the elements of the first - regardless of the condition of JOIN (which characterizes a LEFT JOIN) - but before that I want to filter the second table. For example:

Table Client:

    ╔══════════╦═══════╗
    ║ Cliente  ║ Flag  ║
    ╠══════════╬═══════╣
    ║ A        ║ S     ║
    ║ B        ║ V     ║
    ║ C        ║ L     ║
    ╚══════════╩═══════╝

Entry table:

   ╔══════════╦═════════╦═══════════╗
   ║ Cliente  ║ Entrada ║ Categoria ║
   ╠══════════╬═════════╬═══════════╣
   ║ A        ║    5575 ║ D         ║
   ║ A        ║    6532 ║ C         ║
   ║ A        ║    3215 ║ D         ║
   ║ A        ║    5645 ║ M         ║
   ║ B        ║    3331 ║ A         ║
   ║ B        ║    4445 ║ D         ║
   ╚══════════╩═════════╩═══════════╝

OK. Running a LEFT JOIN I will have all Clients regardless of whether there are related items in the Input table, but before that I want to filter the latter by Category = D - before JOIN.

Desired result:

   ╔══════════╦═══════╦═════════╗
   ║ Cliente  ║ Flag  ║ Entrada ║
   ╠══════════╬═══════╬═════════╣
   ║ A        ║ S     ║  5575   ║
   ║ A        ║ S     ║  3215   ║
   ║ B        ║ A     ║  4445   ║
   ║ C        ║ L     ║  NULL   ║
   ╚══════════╩═══════╩═════════╝

If I use the search below I will lose the last item of the desired result:

   SELECT c.Cliente, c.Flag, e.Entrada
   FROM Cliente AS c
   LEFT JOIN Entrada AS e
   ON c.Cliente = e.Cliente
   WHERE e.Categoria='D'

How to do to get the desired result initially?

Answer 1

Not if it would be more performative but if it were executed the left join in the table already filtered as a subquery. I know that subqueries are not very performatic but as it is indexed can be faster.

SELECT 
    c.Cliente, c.Flag, e.Entrada
FROM 
    Cliente AS c
LEFT JOIN 
    (SELECT ent.Entrada FROM Entrada as ent WHERE ent.Categoria = 'D') AS e
        ON (c.Cliente = e.Cliente)

Answer 2

Studying via the original post where I found my question answered, and another that discusses the difference between ON and WHERE in LEFT JOIN I came to the conclusion that the most appropriate response is the following:

   SELECT c.Cliente, c.Flag, e.Entrada
   FROM Cliente AS c
   LEFT JOIN Entrada AS e
   ON c.Cliente = e.Cliente
   AND e.Categoria='D'

However, for a Customer table with 150K entries and an Entry table with 450K entries, my search does not end even after 30 minutes. To have a point of comparison when I change the last AND for WHERE the return is instantaneous. Should I expect this loss of performance (detail, all fields involved are indexed)? Any other research suggestion that is in faster theory?

Answer 3

As a general rule you only leave in Join what is necessary for the tables to be compared to each other, in your case only c.Client = and.Client.

And you leave in the Where only what is the filter, in this case the WHERE e.Category=’D'.

Modern systems engineers will know whether to auto optimize regardless of the situation. Unfortunately I don’t know the Mysql engine to know if it is smart enough to fit itself.

As in general, WHERE is performed only after JOIN this performance loss is expected. Let’s see putting the filter condition together with the Join for EVERY record of the entry table you will check whether the category = D E has a corresponding in client

In the second case it has already greatly decreased the search as it will only check the category for entries that has client and possibly there is no index covering the category (and usually not worth creating clues to flags)