How to Filter a Table Before a Left Join Using the ON Clause?

Filter table before left join

To filter a table before performing a left join, apply the filter condition in the ON clause of the JOIN statement. In this example, the goal is to filter the Entry table by the Category column with a value of 'D' before joining the Entry table to the Customer table.

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

In this query, the ON clause filters the Entry table by the Category column, ensuring that only rows with Category = 'D' are included in the join operation. This allows you to retrieve all records in the Customer table regardless of whether they have related records in the Entry table, while filtering out any extraneous entries that do not meet the specified Category criteria.

The result of this query will be:

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

This matches the expected result, which contains all rows from the Customer table, plus matching entries from the Entry table filtered by Category = 'D'.

The above is the detailed content of How to Filter a Table Before a Left Join Using the ON Clause?. For more information, please follow other related articles on the PHP Chinese website!