Yes this can be a very useful feature of SQL, something I always thought was woefully lacking in Oracle.

I generally prefer to keep joins and filters separate, for sake of readability. So would recommend using a view, or joining on to a subquery, e.g.

[geshi lang=sql]
SELECT *
FROM table1 t1
LEFT JOIN
(
SELECT *
FROM table2
WHERE NOT col_b = 42
) t2
ON (t1.col_a = t2.col_a)
[/geshi]