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!!

Vandalism

See this link - sqlbenjamin.wordpress.com/2017/12/23/… – Graniah

Answer 1

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

Businesswoman

@bluefeet but both execution plans are equals, aren't they? – Aleut

IMHO I think MySQL is smart enough to do that by itself – Darindaring

@ÁxelCostasPena smart enough to do what? – Businesswoman

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. – Darindaring

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 – Hinny

Answer 2

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

Scriber

@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. – Scriber

Great this inspired me to fix my case. – Godforsaken

Answer 3

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'

Impatient

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 – Pulsimeter

@Pulsimeter 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. – Impatient

Answer 4

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
 

Pushing