SQL statement to combine fact table and dimension table?

Question

If I have a fact table and a dimension table, for example, as below:

fact_table

fact_key

dim_key

revenue

cost

dimension_table

dim_key

advertiser

product

Fact table has 4 rows and dimension table has 3 rows. How do I combine two tables?

Answer 1

Since there is a relationship between fact_table and dimension_table, you might want to do something like this:

alter table fact_table
    add constraint fact_table_fk
        foreign key (dim_key) references dimension_table(dim_key);

For the query you're asking for when selecting the rows, use INNER JOIN or any other JOIN statements

SELECT * FROM fact_table f
INNER JOIN dimension_table d
ON f.dim_key = d.dim_key