Extra conditions in JOIN: ON ... AND ...

You already know how to join tables using JOIN. But in real life, just matching by keys isn’t always enough. A lot of times, you need to join data only if it matches some extra criteria — like only active records, only data for the current year, or only completed orders.

And that’s where extending the ON clause with AND comes into play.

Extra conditions in JOIN ... ON let you precisely control which rows get joined, even before SQL starts building the result. This makes your query:

• Faster (fewer rows go through the JOIN),
• More accurate (filtering happens at the join stage),
• More predictable when using LEFT JOIN (unlike filtering in WHERE).

Example: Only active course enrollments

Let’s say the enrollments table has a student’s participation status: active, dropped, pending.

students table:

Updated enrollments table:

courses table:

Now we want to get only those students who have active courses:

SELECT 
    students.name AS student_name,
    courses.name AS course_name
FROM students 
INNER JOIN enrollments 
    ON students.id = enrollments.student_id
    AND enrollments.status = 'active'
INNER JOIN courses 
    ON enrollments.course_id = courses.id;

Result:

Here we added AND enrollments.status = 'active' inside the ON so the join happens only for active records, not filtered after joining.

Why not WHERE?

You could write it like this:

...
WHERE enrollments.status = 'active'

But this behaves differently with LEFT JOIN. Filtering in WHERE removes rows where there’s no match (NULL), basically turning your LEFT JOIN into an INNER JOIN.

But the condition AND enrollments.status = 'active' inside ON limits the joined rows right away — it controls which rows even get into the join, not just filtering the result after.

This approach is super important if you want to keep rows from one table even if there’s no matching value in the other (which is common in reports and analytics).

More examples of ON ... AND ... usage

students table:

enrollments table:

courses table:

Example: only courses from the current year

SELECT
    students.name,
    courses.name,
    enrollments.enrolled_at
FROM students
JOIN enrollments
    ON students.id = enrollments.student_id
    AND EXTRACT(YEAR FROM enrollments.enrolled_at) = EXTRACT(YEAR FROM CURRENT_DATE)
JOIN courses
    ON enrollments.course_id = courses.id;

Here we’re joining only those records that are from the current year.

Example: excluding by value

JOIN enrollments
    ON students.id = enrollments.student_id
    AND enrollments.status != 'dropped'

We’re excluding students who dropped out at the join stage, not filtering after.

When the condition is inside ON, PostgreSQL can optimize the join plan and process fewer rows. This is especially important with big data sets. Filtering inside is more efficient than filtering after the JOIN.

JOIN ON — it’s not just about keys

A lot of people think ON is just id = id. Actually, you can put in:

• Logical operators: AND, OR, NOT
• Comparisons: >, <, <>, BETWEEN, IN
• Expressions: EXTRACT, DATE_TRUNC, COALESCE, NULLIF

Let’s combine it all

students table:

faculties table:

Note: the faculty with id = 30 has no name (NULL).

courses table:

enrollments table:

SELECT
    s.name AS student_name,
    c.name AS course_name,
    f.name AS faculty_name
FROM students s
JOIN enrollments e
    ON s.id = e.student_id
    AND e.status = 'active'
JOIN courses c
    ON e.course_id = c.id
    AND c.name != 'PE'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

Here we’re filtering by:

• Active records,
• Courses except "PE",
• Faculties that have a name.

Query result:

Hope you liked this lecture. You’ll be using multiple JOINs with filters in your queries all the time. Pretty much always :)