Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

Last update on August 19 2022 21:50:45 (UTC/GMT +8 hours)

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

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

Syntax:

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Syntax diagram - FULL OUTER JOIN

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

Example: SQL Full Outer Join

Let’s combine the same two tables using a full join.

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

SQL Code:

SELECT * FROM table_A FULL OUTER JOIN table_B ON table_A.A=table_B.A;

Output:

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

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 -

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

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:

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

Here is the SQL statement which returns all rows from the 'foods' table and 'company' table using "FULL OUTER JOIN" clause.

SQL Code:

SELECT a.company_id AS "a.ComID", a.company_name AS "C_Name", b.company_id AS "b.ComID", b.item_name AS "I_Name" FROM company a FULL OUTER JOIN foods b ON a.company_id = b.company_id;

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 clause

We 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.

SELECT a.company_id AS "a.ComID", a.company_name AS "C_Name", b.company_id AS "b.ComID", b.item_name AS "I_Name" FROM company a FULL OUTER JOIN foods b ON a.company_id = b.company_id WHERE a.company_id IS NULL OR b.company_id IS NULL ORDER BY company_name;

Output:

a.ComID C_Name b.ComID I_Name ---------- ------------------------- ---------- --------------- 19 sip-n-Bite. Salt n Shake

Full Outer Join using Union clause

A UNION clause can be used as an alternate to get the same result as FULL OUTER JOIN

Here is the example:

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

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 clause

The following code is, the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN and combined by, using UNION clause

SELECT table_a.A,table_a.M,table_b.A,table_b.N FROM table_A LEFT OUTER JOIN table_B ON table_A.a=table_b.A UNION SELECT table_a.A,table_a.M,table_b.A,table_b.N FROM table_A RIGHT OUTER JOIN table_B ON table_A.a=table_b.A;
Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

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 -

Which join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join?

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

  • SQL Exercises, Practice, Solution
  • SQL Retrieve data from tables [33 Exercises]
  • SQL Boolean and Relational operators [12 Exercises]
  • SQL Wildcard and Special operators [22 Exercises]
  • SQL Aggregate Functions [25 Exercises]
  • SQL Formatting query output [10 Exercises]
  • SQL Quering on Multiple Tables [8 Exercises]
  • FILTERING and SORTING on HR Database [38 Exercises]
  • SQL JOINS
    • SQL JOINS [29 Exercises]
    • SQL JOINS on HR Database [27 Exercises]
  • SQL SUBQUERIES
    • SQL SUBQUERIES [39 Exercises]
    • SQL SUBQUERIES on HR Database [55 Exercises]
  • SQL Union[9 Exercises]
  • SQL View[16 Exercises]
  • SQL User Account Management [16 Exercise]
  • Movie Database
    • BASIC queries on movie Database [10 Exercises]
    • SUBQUERIES on movie Database [16 Exercises]
    • JOINS on movie Database [24 Exercises]
  • Soccer Database
    • Introduction
    • BASIC queries on soccer Database [29 Exercises]
    • SUBQUERIES on soccer Database [33 Exercises]
    • JOINS queries on soccer Database [61 Exercises]
  • Hospital Database
    • Introduction
    • BASIC, SUBQUERIES, and JOINS [39 Exercises]
  • Employee Database
    • BASIC queries on employee Database [115 Exercises]
    • SUBQUERIES on employee Database [77 Exercises]
  • More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL RIGHT JOIN
Next: Join a table to itself

Which join returns all the rows from the right table with the matching rows in the left table?

SQL cross join It generates all the rows from the left table which is then combined with all the rows from the right table. This type of join is also known as a Cartesian product(A*B).

Which join returns all the rows from the right table?

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1).

Which type of join returns all rows in which there is a match in either one of the tables?

FULL JOIN. Full Join or the Full Outer Join returns all those records which either have a match in the left(Table1) or the right(Table2) table.

Which join Return all rows from left table?

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).