Query runs out of Memory

Hi I am running the following query:

SELECT
route_list_sb032206."Route_ID",
route_list_sb032206."StartDate",
route_list_sb032206."EndDate",
route_list_sb032206."Carrier_ID",
route_list_sb032206."SB_Carrier_Name",
route_list_032206."Date",
route_list_032206."Route_ID",
route_list_032206."Carrier_ID",
route_list_032206."Carrier_Name",
route_list_032206."Occupant_Addr",
route_list_032206."Occupant_City",
route_list_032206."State",
route_list_032206."Zip"
FROM

public."route_list_sb032206", public."route_list_032206"

Where route_list_sb032206."Route_ID" != route_list_032206."Route_ID"

and getting the following error:

out of memory for query result

********** Error **********

I know I am running this wrong so let me look to those smarter than I.

What I am trying to accomplish is that the Route_ID columns on both tables contain the same data but one table has 4 more IDs (rows) than the other column and 50 that are different (not in both) when I run an looking for matches - Inner Join (that query runs fine) . I am trying to get a result set of what those different route ID's are.

Any help would be most appreciated.

Sean

Doing an inner join where something is not equal is a bad idea. This will return exponentially more matches than you're intending.

A better approach could be to do an inner join where they ARE equal, and then select a result set where the Route_ID is not in the inner join result set.

Example:

SELECT "Route_ID"
FROM public."route_list_sb032206"
WHERE "Route_ID" NOT IN
(SELECT route_list_sb032206."Route_ID",
FROM public."route_list_sb032206", public."route_list_032206"
Where route_list_sb032206."Route_ID" = route_list_032206."Route_ID") x1
UNION
SELECT "Route_ID"
FROM public."route_list_032206"
WHERE "Route_ID" NOT IN
(SELECT route_list_sb032206."Route_ID",
FROM public."route_list_sb032206", public."route_list_032206"
Where route_list_sb032206."Route_ID" = route_list_032206."Route_ID") x2;

This should return a set of Route_ID's that are only in one table or the other. All matches will be excluded due to the NOT IN.