presence/absence

Greetings,

I'm trying to compare two views, both with the same three fields,
where the rows of one view are a subset of the rows of the other. I
want to produce a new view that shows each row in the larger of the
two original views, and has a new column that has a value of 1 if the
combination of values on that row occurs in the smaller of the
original views, and a 0 if it doesn't.

I'm certainly a beginner SQL user, and the solution that occurred to
me -- namely a CASE conditional statement to produce the fourth column
in the new view-- has not worked, because mentioning the columns of
the smaller source view in the CASE statement induces the same
behavior as though all the columns in both the smaller and larger
source views were mentioned in the SELECT list -- that is, all
combinations of the values in the six fields in the larger and smaller
source tables are tested by the CASE statement (which is pretty
useless). I only want the particular rows in the larger view to be
tested pairwise against those in the smaller.

This is what I tried, where the larger source view mentioned above is
called "SWWA_list", and the smaller view (a subset of the rows of the
larger) is "SWWA_t1". Each of the source views has the same three
columns, called point_id, region, and foray.

CREATE VIEW bird_data."SWWA_pres" AS (
SELECT
DISTINCT l.point_id, l.region, CASE WHEN (l.point_id,
l.region, l.foray) = (t1.point_id, t1.region, t1.foray) THEN 1 ELSE 0
END
FROM "SWWA_list" l, "SWWA_t1" t1
ORDER BY l.point_id, l.region
);

What happens, when I try the above, is that rows that do appear in
both source views produce 2 rows in the newly created view, identical
except that one row has a 1 in the CASE column, and the other a 0.
Rows that don't appear in both source show up just once in the new
view, and have a 0 in the CASE column. Diagnosis of this (e.g., by
removing the DISTINCT keyword) has proved to me that the CASE
statement is considering all possible combinations of the values in
the six columns from the two original views.

I tried specifying a ROW constructor, to force PostgreSQL to stop
essentially permuting my column orders, but to no avail -- it seemed
to change nothing:

CREATE VIEW bird_data."SWWA_pres" AS (
SELECT
DISTINCT l.point_id, l.region CASE WHEN ROW(l.point_id,
l.region, l.foray) = ROW(t1.point_id, t1.region, t1.foray) THEN 1 ELSE
0 END
FROM "SWWA_list" l, "SWWA_t1" t1
ORDER BY l.point_id, l.region
);

So, my hunch is that there should be a very simple way to get what I'm
after, but I've not been able to find it. Any advice would be most
appreciated. If my description of the problem is too vague, I can
send more specifics. Thanks in advance.

Bryan Nuse