Filter Table Before Applying Left Join

Question

I have 2 tables, I want to filter the 1 table before the 2 tables are joined together.

Customer Table:

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

Entry Table:

   ╔══════════╦═══════╦══════════╗
   ║ Customer ║ Entry ║ Category ║
   ╠══════════╬═══════╬══════════╣
   ║ A        ║  5575 ║ D        ║
   ║ A        ║  6532 ║ C        ║
   ║ A        ║  3215 ║ D        ║
   ║ A        ║  5645 ║ M        ║
   ║ B        ║  3331 ║ A        ║
   ║ B        ║  4445 ║ D        ║
   ╚══════════╩═══════╩══════════╝

I want to Left Join so I get all records from the Customer table regardless of whether there are related records in the Entry table. However I want to filter on category D in the entry table before the join.

Desired Results:

   ╔══════════╦═══════╦═══════╗
   ║ Customer ║ State ║ Entry ║
   ╠══════════╬═══════╬═══════╣
   ║ A        ║ S     ║  5575 ║
   ║ A        ║ S     ║  3215 ║
   ║ B        ║ V     ║  4445 ║
   ║ C        ║ L     ║  NULL ║
   ╚══════════╩═══════╩═══════╝

If I was to do the following query:

   SELECT Customer.Customer, Customer.State, Entry.Entry
   FROM Customer
   LEFT JOIN Entry
   ON Customer.Customer=Entry.Customer
   WHERE Entry.Category='D'

This would filter out the last record.

So I want all rows from the left table and join it to the entry table filtered on category D.

Thanks to any help in advance!!

See this link - https://sqlbenjamin.wordpress.com/2017/12/23/sql-tip-left-joins-and-where-clauses-are-they-really-left-joins/ — MasterJoe, Sep 04 '20 at 05:41

score 156 · Accepted Answer · answered Feb 25 '13 at 21:45

156

You need to move the WHERE filter to the JOIN condition:

SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Entry e
   ON c.Customer=e.Customer
   AND e.Category='D'

See SQL Fiddle with Demo

answered Feb 25 '13 at 21:45

Taryn

242,637
56
362
405

3

@bluefeet but both execution plans are equals, aren't they? – Alex Zhukovskiy Feb 28 '17 at 09:34
IMHO I think MySQL is smart enough to do that by itself – Áxel Costas Pena Jul 04 '17 at 10:38
@ÁxelCostasPena smart enough to do what? – Taryn Jul 05 '17 at 02:11
1

To apply the filtering on a table on which the join depends before joining if the where conditions point to the first table. At least on some of my tests I saw that. I posted that comment in answer to the Alex's comment. – Áxel Costas Pena Jul 05 '17 at 06:52
2

In `Postgres 11`, this solution doesn't work as it considers the condition only for the join operation and then returns all records from the left column – Soheil Pourbafrani Feb 19 '21 at 00:42

score 36 · Answer 2 · answered Feb 25 '13 at 21:49

36

You could also do:

SELECT c.Customer, c.State, e.Entry
FROM Customer AS c
LEFT JOIN (SELECT * FROM Entry WHERE Category='D') AS e
ON c.Customer=e.Customer

SQL Fiddle here

answered Feb 25 '13 at 21:49

Jeff Rosenberg

3,522
1
18
38

4

@TomJenkin Sorry for the late response, I've been away from the computer this whole time. Out of curiousity, though, I ran both of these and checked the execution plans. The execution plans are nearly identical, but to the extent either is better, it appears it's Bluefeet's version. – Jeff Rosenberg Feb 26 '13 at 13:47
Great this inspired me to fix my case. – sivann May 30 '18 at 07:52

score 1 · Answer 3 · answered Dec 13 '18 at 16:45

1

Or...

SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Entry e
   ON c.Customer=e.Customer
WHERE e.Category IS NULL or e.Category='D'

answered Dec 13 '18 at 16:45

c z

7,726
3
46
59

1

In SQL JOIN condition is executed before WHERE. So in the suggested solution, entire table is joined and the result is filtered - which can be quite expensive when dealing with large tables. Intended filter condition should be part of the JOIN condition like suggested in other answers – Omley Nov 05 '20 at 06:15
3

@Omley I think you're confused about how SQL works. SQL is *declarative*, not *procedural*. The *order of the words* in the query has no effect on the *order of operations*. That is up to the engine. We can see from a simple `EXPLAIN` that MySql actually does very marginally better at optimising this query than the one in Taryn's answer, though this will certainly be more luck than management and the reverse may well hold true for another engine. – c z Nov 05 '20 at 13:16

score 0 · Answer 4 · answered Jun 30 '22 at 11:43

If you are using PostgreSQL i think that you can also use WITH clause to create Common Table Expression. This will especially be helpful if you are going to use this table in other Common Table Expressions in the same query.

Example:

 WITH 
      Filtered_Entries as (
     SELECT Entry,Customer
       FROM Entry_tbl
       WHERE Entry = 'D'
    )
SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Filtered_Entries e
   ON c.Customer=e.Customer

Filter Table Before Applying Left Join

4 Answers4

Linked

Related