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

Question

Anonymous · Accepted Answer

Inner join only provides the data set that is common between both the tables
joined.
Left join brings all the data from the left table in the join and add the
information from right table where we have the data for the same set in both the
tables , leaving the other fields marked nulls for the data that appears only
the left table and not in right table.
Right join is reverse of left join where it brings all the dataset from the
right table in the join function and merge the left table data where its
available for the common data set leaving the fields null for those not existing
in left table

Anonymous · 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

Unlock Full Access

Practice More Questions