RIGHT JOIN and FULL JOIN - SQL Query
Consider the following tables Table1 and Table2.
Table1: Clients
| C_ID | Client_Name | Client_Phone |
|---|
| 1 | Alex | 9022222222 |
| 2 | Mark | 9033333333 |
| 3 | SAM | 9044444444 |
| 4 | John | 9055555555 |
Table2: Payment
| C_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 355 |
| 2 | 102 | 255 |
| 3 | 103 | 300 |
RIGHT JOIN or RIGHT OUTER JOIN
Q. Write a query to perform right join on table 'Clients' to display the column 'Clients_Name' from Table1 and 'Amount' from table2.
Answer:
Consider Table1 as Right and Table2 as left. The RIGHT JOIN returns the all possible rows from right table and shows null in left table.
SELECT Clients.Client_Name, Payment.Amount
FROM Payment
RIGHT JOIN Clients
ON Payment.C_ID = Clients.C_ID
| Client_Name | Amount |
|---|
| Alex | 355 |
| Mark | 255 |
| SAM | 300 |
| John | |
FULL JOIN or FULL OUTER JOIN
Q. Write a query to perform full outer join and display columns such as C_ID, Client_Name, Transaction_ID and Amount.
Answer:
SELECT Clients.C_ID, Clients.Client_Name, Payment.Transaction_ID, Payment.Amount
FROM Clients
FULL JOIN Payment
ON Payment.C_ID = Clients.C_ID;
In the above query the FULL JOIN (OUTER JOIN) is performed on Table1. This type of join Combines the result the all rows from left table and all rows from right table.
Output:
| C_ID | Client_Name | Transaction_ID | Amount |
|---|
| 1 | Alex | 101 | 355 |
| 2 | Mark | 102 | 255 |
| 3 | SAM | 103 | 300 |
4 | John | | |
