Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning. Show
In this lesson we'll cover:
Outer joinsOuter joins are joins that return matched values and unmatched values from either or both tables. There are a few types of outer joins:
Note: Outer joins vs. Inner joinWhen performing an inner join, rows from either table that are unmatched in the other table are not returned. In an outer join, unmatched rows in one or both tables can be returned. As you work through the following lessons about outer joins, it might be helpful to refer to this JOIN visualization by Patrik Spathon. The Crunchbase datasetThe data for the following lessons was pulled from Crunchbase, a crowdsourced index of startups, founders, investors, and the activities of all three. It was collected Feb. 5, 2014, and large
portions of both tables were randomly dropped for the sake of this lesson. The first table lists a large portion of companies in the database; one row per company. The
The second table lists acquisitions—one row per acquisition. You'll notice that there is a separate field called
The foreign key you use to join these two tables will depend entirely on whether you're looking to add information about the acquiring company or the company that was acquired. It's worth noting that
this sort of structure is common. For example, a table showing a list of emails sent might include a The INNER JOIN selects records that have matching values in both tables. (So in this case whatever data is present in Table1 is also present in table 2 for ex. "A" is present twice in tbl2 so in end result table its 2 times i.e for 1 "A" there is 2 values is been returned from tbl2) The LEFT JOIN returns all records from the left table (table1), and the matching records from the right table (table2).The result is 0 records from the right side, if there is no match. (So again "A" is having 2 values in tbl2 so for 1 "A" there is 2 values is been returned from tbl2) The RIGHT JOIN returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match. (So again in table 2 there is 2 "A" and for each 1 "A" 1 values from left table(tbl1) is returned). Data set is having similar values, that's why you are getting similar result. Try modifying the data set you will observe the difference What is Full Outer Join in SQL?In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Pictorial Presentation: SQL Full Outer Join Syntax: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name; Syntax diagram - FULL OUTER JOIN Example: SQL Full Outer Join Let’s combine the same two tables using a full join. SQL Code:
Output: Because this is a full join, all rows (both matching and nonmatching) from both tables are included in the output. There is only one match between table table_A and table table_B, so only one row of output displays values in all columns. All remaining rows of output contain only values from table table_A or table table_B, with the remaining columns set to missing values only one row of output displays values in all columns explain below - Example: SQL Full Outer Join between two tables Here is an example of full outer join in SQL between two tables. Sample table: foods Sample table: company As we know the FULL OUTER JOIN is the combination of the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN, so, here we are going to describe how FULL OUTER JOIN perform internally. Pictorial Presentation SQL Full Outer Join: Here is the SQL statement which returns all rows from the 'foods' table and 'company' table using "FULL OUTER JOIN" clause. SQL Code:
Output: a.ComID C_Name b.ComID I_Name ---------- ------------------------- ---------- ------------- 16 Akas Foods 16 Chex Mix 15 Jack Hill Ltd 15 Cheez-It 15 Jack Hill Ltd 15 BN Biscuit 17 Foodies. 17 Mighty Munch 15 Jack Hill Ltd 15 Pot Rice 18 Order All 18 Jaffa Cakes Salt n Shake 19 sip-n-Bite. FULL OUTER JOIN using WHERE clauseWe can include a WHERE clause with a FULL OUTER JOIN to get return only those rows where no matching data between the joining tables are exist. The following query returns only those company that have no matching food product in foods, as well as that food product in foods that are not matched to the listed company.
Output: a.ComID C_Name b.ComID I_Name ---------- ------------------------- ---------- --------------- 19 sip-n-Bite. Salt n Shake Full Outer Join using Union clauseA UNION clause can be used as an alternate to get the same result as FULL OUTER JOIN Here is the example: Here is the SQL statement: SELECT table_a.A,table_a.M,table_b.A,table_b.N FROM table_A FULL OUTER JOIN table_B ON table_A.a=table_b.A ORDER BY table_A.A; FULL OUTER JOIN using LEFT and RIGHT OUTER JOIN and UNION clauseThe following code is, the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN and combined by, using UNION clause Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition. Click on the following to get the slides presentation of all JOINS - Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews. Practice SQL Exercises
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus. Previous: SQL RIGHT
JOIN Which joins returns all records when there is a match in either left or right table?The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
What join keyword returns all records when there is a match in left or right table records left join?The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).
Which join returns rows when there is a match in both tables?Outer joins are joins that return matched values and unmatched values from either or both tables. There are a few types of outer joins: LEFT JOIN returns only unmatched rows from the left table, as well as matched rows in both tables.
Which join returns all the rows from the right table with the matching rows in the left table?It joins two or more tables, returns all records from the left table, and matching rows from the right-hand table. It is used to join two or more tables, returns all records from the right table, and matching rows from the left-hand table.
|