Which keyword I should use?

Question

I have two tables:

I1 (A, B, C)
I2 (A, C)

I2 has some tuple of data that I1 doesn't have.

Now I want to create a table I3 that only has the data (A, C) that both exists in I1 and I2.

• Which keyword I should use to create this table?
• Use intersect?

Answer 1

Assuming that the combination (a,c) is unique in both table, then this is a case of a simple join plus a create table as

create table i3
as
select i1.a, i1.c
from i1
  join i2 on i2.a = i1.a and i2.c = i1.c;

If (a,c) is not unique you will have duplicates in the resulting table. This can be avoided by using the intersect operator:

create table i3
as
select a, c
from i1
intersect
select a,c
from i2;

---

Note that create table ... as select is ANSI SQL but some DBMS (e.g. SQL Server) don't follow the standard and use a different syntax to create a table based on a select statement.