How to Perform a Left Join with a Pre-Filtered Table in SQL?

Efficiently execute SQL left join and pre-filter table data

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

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

The following SQL query demonstrates how to achieve this:

<code class="language-sql">SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Entry e
   ON c.Customer=e.Customer
   AND e.Category='D'</code>

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

Example table

<code>客户表 (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        ║
╚══════════╩═══════╩══════════╝</code>

Results

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

In summary, by using the AND clause in the JOIN condition, we can filter the tables before joining them, thereby retrieving data more precisely based on the specified conditions.

The above is the detailed content of How to Perform a Left Join with a Pre-Filtered Table in SQL?. For more information, please follow other related articles on the PHP Chinese website!