Sql Right Join Clause
- Right Join brings the right table’s all records and the matching records of left table.
- We can use left join, if we change the position of tables in query.
- Right join and right outer join are same clauses. You may use you want.
Sql Right Join Syntax
Select columnNames from tableName1 right Join TableName2 on tableName1.relationColumn = tableName2.relationColumnNote: The TableName2 is the table,that we want to bring all records.
Sql Right 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 borrows right join students on students.studentId = borrows.studentId |
If we change the position of the tables
|
1 2 |
Select name,surname,takenDate from students left join borrows on students.studentId = borrows.studentId |
Example 2: List all the datas of students who do not read book.
|
1 2 3 |
Select students.* from borrows right join students 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 borrows right join students 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.
Example 4:List all student’s name,surname,book name and the borrow’s taken date. Students who do not read books are also listed.
|
1 2 3 |
Select students.name,surname,books.name,takenDate from borrows join books on books.bookId = borrows.bookId right join students on students.studentId = borrows.studentId |
or with left join
|
1 2 3 |
Select students.name,surname,books.name,takenDate from students left join borrows on students.studentId = borrows.studentId left join books on books.bookId= borrows.bookId |
Note: If we are joining two tables, It is not important using left join, or right join. But if we are joining three tables, then right join is more simple. At seems the example above,if we use left join, we must use it two times.
