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
2. Left join shows all rows from Right table? YES / NO
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
Topic Covered
Left join in SQL and DBMS.