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.