SQL FULL JOIN Vs LEFT JOIN Clause
The distinction between a FULL JOIN and an LEFT JOIN will be discussed in this session.
SQL FULL JOIN Clause
FULL JOIN can be thought of as a combination of LEFT JOIN and RIGHT JOIN.
This query will return a single row for each unique entry in either the right or left tables.
Example: Full join clause
SELECT Product.name, Product.no,
FROM Product
FULL OUTER JOIN Customer on Product.no = Customer.no SQL LEFT JOIN Clause
This will involve adding left table entries regardless of whether or not a comparable record exists in the right table.
Example: The Customer and order table is the left table in the query below.
| CID | Name | Gender | City |
|---|---|---|---|
| C-1 | Keshavan | Male | Madurai |
| C-2 | Harish Karthik | Female | Hyderbhad |
| OrderNo | CID | Amount | OrderDate |
|---|---|---|---|
| 1 | C-1 | 4000 | 2004/08/20 18:56:46 |
| 2 | C-2 | 8000 | 1998/05/25 20:34:47 |
SELECT Author.AuthorName,Author.AID ,Book.BookName,Book.AID
FROM Author
LEFT JOIN Book
ON Author.AID = Book.AID; Output As a result, the output will be,
| authorname | ab_author.aid | bookname | ab_book.aid |
|---|---|---|---|
| Hanumanthan | A-1 | Learing Sql Server T-Sql | A-1 |
| Hanumanthan | A-1 | SQL Visual Quickstart | A-1 |
| Hanumanthan | A-1 | The Database Language SQL | A-1 |
| Ranjani Mai | A-2 | Programming with PL/SQL for Beginners | A-2 |
SQL Differece Between LEFT JOIN AND FULL JOIN Clause
There are 3 main contrast in left join & full join clause that are in below table:-
| LEFT JOIN | FULL JOIN |
|---|---|
| Gets all the rows from the left-hand table. | Retrieves all rows from both tables. |
| Inner Join +all the rows from the left table that aren't matched. | Inner Join +all the rows from the left table that aren't matched +all the rows from the right table that aren't matched |
| The right table's unmatched data is lost | Neither information is lost. |