Efficiently perform SQL left-joining, pre-filter table data

When processing multiple tables, it is usually necessary to filter one of the tables before joining them. This example involves two tables: Customer table and Entry table.

The goal is to filter the entry table based on the specific category 'D' before performing a left join between these two tables. The expected result is: all records in the customer table are retrieved regardless of whether there are relevant records in the entry table; and at the same time, records that do not meet the category criteria in the entry table are excluded.

The following SQL query demonstrates how to achieve this:

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

By moving the WHERE filter condition to the JOIN condition, we can apply category filters to the entry table before joining. This ensures that only entry records that meet the category criteria are included in the connection result.

Sample table

客户表 (Customer):

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

条目表 (Entry):

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

result

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

Abstract, by using the AND clause in the JOIN condition, we can filter the table before joining it, so that the data is retrieved more accurately based on the specified conditions.