Join 2 tables on matching columns

• Until now we've been querying a single table at a time
• One of the main strengths of a relational database is the ability to join tables together
• This enables us to piece data together from different places to answer a question

Think of a join as a left and right table:

• Left - The first table in the join (on the FROM line)
• Right - The second table in the join (on the INNER JOIN line)

SELECT      l.<field1>, l.<field2>, l.<field3>, r.<field1>, r.<field2>, r.<field3>
FROM        <schema_name>.<table_name> | <view_name> AS l
INNER JOIN  <schema_name>.<table_name> | <view_name> AS r
            ON l.<field1> = r.<field1> AND l.<field2> = r.<field2>;

Terms

• INNER JOIN = Combines rows that have matching values in two or more tables
• ON = The columns on which you want to join the tables
  • They must have compatable data and data types to get a meaningful result

Entity Relationship Diagram (ERD) Revisited

So how do we know what columns are joinable to which other table?

Let's look at the ERD:

• As an example, we can join Sales_Header to Customers.
  • This can be done by joining a column on each table called customer_id
  • Good naming conventions on tables often makes this job a lot easier

SELECT *
FROM "sequel-mart-schema"."Sales_Header" AS sh 
INNER JOIN "sequel-mart-schema"."Customers" AS cu
    ON sh.customer_id = cu.customer_id;

• This returns transactions from the Sales_Header and more detailed information about each customer from Customers
• Other possible joins include:
  • Sales_Header to Stores
  • Sales_Header to Dates
  • Sales_Header to Sales_Detail
  • Sales_Detail to Products

Notes:

• INNER JOIN is the longer form of this type of join.
• We can just use JOIN and the queries would run in the same way
• Beware that INNER JOIN only returns where records exist in both tables
  • If something is in one table and not the other it will not be returned