How to create a subset of a table

Question

I have two tables (delta and aa) of flight data, and I am trying to create a new table that would be a subset of delta. This subset would only contain the rows in delta that share the same origin_airport_id and dest_airport_id as in aa.

aa has 89,940 rows and delta has 245,052. I used:

CREATE TABLE dl_share 
AS 
SELECT delta.* 
FROM delta,aa 
WHERE (aa.origin_airport_id = delta.origin_airport_id 
  AND aa.dest_airport_id = delta.dest_airport_id) 

which creates a table with 18,562,876 rows. Why is the size of the table bigger rather than smaller, and how can I do this correctly?

Answer 1

You should use WHERE EXISTS rather than JOIN:

SELECT *
FROM delta d
WHERE EXISTS (
    SELECT 1 
    FROM aa 
    WHERE aa.origin_airport_id = d.origin_airport_id 
    AND aa.dest_airport_id = d.dest_airport_id);