Filter table before applying a LEFT JOIN

Question

Filter table before applying a LEFT JOIN

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

1

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

Client table:

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

Table Entry:

   ╔══════════╦═════════╦═══════════╗
   ║ 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 every Client regardless of whether there are related items in the Entry 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 do you get the desired result initially?

mysql sql join sql-select

asked by anonymous 19.02.2014 / 20:48

3 answers

Use NewRelic to monitor Java applications on Tomcat7 How to make a program run inside a pygame window?

score 2 · Answer 1

Not if it would be more performative but if you ran left join in the already filtered table as a subquery . I know subqueries are not very performative but as indexed it 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)

score 0 · Answer 2

Studying via original post where I found my question answered, and < a href="https://stackoverflow.com/questions/12400567/difference-between-on-and-on-where-in-sql-left-join"> another that discusses the difference between ON and WHERE in LEFT JOIN I came to the conclusion that the most appropriate answer is:

   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 situation with a Client table with 150K entries and an Entry table with 450K entries, my search does not finish nor after 30 minutes. To have a comparison point when I change the last AND by WHERE, the return is instantaneous. Should I expect this loss of performance (detail, all fields involved are indexed)? Any other research suggestions that are in theory faster?

score 0 · Answer 3

As a general rule, you leave in the join only what is necessary for the tables to be compared to each other, in this case only the c.Customer = e.Customer.

And you leave in the where only that is the filter, in the case WHERE e.Category = 'D'.

Modern systems engines will know whether to auto optimize regardless of the situation. Unfortunately I do not know the MySql engine to know if it is smart enough to adjust itself.

Since, in general, the WHERE is performed only after the JOIN, this loss of performance is expected. Let's see putting the filter condition together with the join for TODO record of the input table you will check if the category = D AND if it has a corresponding in client

In the second case it has already greatly reduced the search, as it will only check the category for entries that have a customer and possibly there is no index covering the category (and generally not worth creating indexes for flags)