Left join in SQL and DBMS

Left join in SQL and DBMS

Left join shows all rows from the left table and shows only matching rows from the right table.

RegistrationNo Marks
T4Tutorials1 77
T4Tutorials2 32

Table: Exams

Inner join shows results from both tables where there is any match between columns in both tables.

RegistrationNo Fee
T4Tutorials1 1000
T4Tutorials2 2000
T4Tutorials3
3000

Table: Accounts

Now, we have two different tables. If we want to show the record of both tables in one single table then we can use a left join to join both two tables.

Query of left join

SELECT Exams.RegistrationNo, Exams.Marks, Accounts.Fee FROM Exams LEFT JOIN Accounts ON Exams.RegistrationNo =  Accounts.RegistrationNo

RegistrationNo Marks Fee
T4Tutorials1 77 1000
T4Tutorials2 32 2000

Table: Table generated after Left Join

Comparison of Left-join with other joins

Type Syntax Syntax What will do?
Left (Outer) Join SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table LEFT JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows from the left table and the matched rows from the right table.
Inner Join SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table INNER JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show only the rows where there is a match in both tables based on the specified condition.
Semi Join SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column = table2.column); SELECT * FROM Student_Table WHERE EXISTS (SELECT 1 FROM Teacher_Table WHERE Student_Table.ClassID = Teacher_Table.ClassID); Show only the rows from the left table where there is a match in the right table.
Cross Join SELECT * FROM table1 CROSS JOIN table2; SELECT * FROM Student_Table CROSS JOIN Teacher_Table; Show the Cartesian product of the two tables, resulting in all possible combinations of rows.
Full (Outer) Join SELECT * FROM table1 FULL JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table FULL JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows when there is a match in either the left or right table.
Right (Outer) Join SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; SELECT * FROM Student_Table RIGHT JOIN Teacher_Table ON Student_Table.ClassID = Teacher_Table.ClassID; Show all rows from the right table and the matched rows from the left table.
Self Join SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column = t2.column; SELECT * FROM Student_Table t1 INNER JOIN Student_Table t2 ON t1.ClassID = t2.ClassID; Joins a table with itself, treating it as if it were two separate tables.
Test Your Understandings

1. Left join shows some rows from left table ? YES / NO

Answer - Click Here:
No, its show all rows

2. Left join shows all rows from Right table? YES / NO

Answer - Click Here:
No, it can only show matching rows from right table.

3. SELECT Exams.Marks, Accounts.Fee FROM Exams LEFT JOIN Accounts?

In this query, exams are the right table and accounts is the left the table.

Yes/No

Answer - Click Here:
No, exams is left table

Topic Covered

Left join in SQL and DBMS.