Sql Left Join Clause
- Left Join brings the left table all records and the matching records of right table.
- We can use right join, if we change the tables position in query.
- Left join and left outer join are same clauses. You may use you want.
Sql Left Join Syntax
Select columnNames from tableName1 left Join TableName2 on tableName1.relationColumn = tableName2.relationColumnNote: The TableName1 is the table,that we want to bring all records.
Sql Left Join Examples on Library Database
TO DOWNLOAD THE SAMPLE LİBRARY DATABASE CLICK
Example 1:List all student’s name,surname and the borrow’s taken date. Students who do not read books are also listed.
1 2 |
Select name,surname,takenDate from students left join borrows on students.studentId = borrows.studentId |
If we change table’s position
1 2 |
Select name,surname,takenDate from borrows right join students on students.studentId = borrows.studentId |
Example 2: List all the datas of students who do not read book.
1 2 3 |
Select students.* from students left join borrows on students.studentId = borrows.studentId where borrowId is null |
Example 3: List, All of the student’s name, surname and count of the read books. Students who do not read books are also listed with zero data.
1 2 3 |
Select name,surname,count(borrowId) as Counts from students left join borrows on students.studentId = borrows.studentId group by name,surname,students.studentId |
Note: Star character could be written inside count function. If we do, It had been wrong. Because The number of books read, would be one.