How do you determine when to use an inner join versus an outer join?

Question

Anonymous · Accepted Answer

SQL has two types of joins

* INNER JOINs - for getting a subset from two tables which has the data based
   on the join key

* OUTER JOINs - used when you want to keep the rows of one side or both side
   intact ,e ven with empty columns, outer joins have the following three
   sub-sets
   
   * LEFT JOIN ,where the rows of the left hand side table is kept
   
   * RIGHT JOIN where the rows of right hand side table is kept
   
   * OUTER JOIN or FULL OUTER JOIN when both the tables rows are kept

Let's say table 1 has 500M rows with the following columns

product_id, order_date, sales_amount

and table 2 has 2M rows with the following columns

product_id, product_name,date_added_to_catalogue

Here's how inner joins and outer joins will look like

* INNER JOIN for two tables on product_id 2M rows ( incorrect) if you want the
   sales by dates

* OUTER JOIN types
   
   * LEFT JOIN with table 1 in left 500M rows
   
   * RIGHT JOIN with table 2 in right 2M rows ( doesn't make any sense)
   
   * FULL OUTER JOIN with table 1 and table 2 , could be anywhere from 1Billion
     rows to multiple buillions based on blanks in left and right hand side
     table (not recommended)

How do you determine when to use an inner join versus an outer join?

Practice this question with AI

Go Premium

Community Answers

Unlock Full Access

Practice More Questions