0

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?
1

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.