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