It returns all rows from right table and matched rows from left table. If no matching rows from left table, then it will return NULL values
For example EMPLOYEE Table has
EMP_ID | EMP_NAME | HIRE_DATE | SALARY | DEPT_ID |
---|---|---|---|---|
1 | Roger Fed | 2001-05-01 | 5000 | 4 |
2 | Djoker Nova | 2002-07-15 | 6500 | 1 |
3 | Stef Graph | 2005-10-18 | 8000 | 5 |
4 | Mart Hingis | 2007-01-03 | 7200 | 3 |
5 | Andre Duplesi | 2008-06-24 | 5600 | null |
DEPARTMENT Table has
DEPT_ID | DEPT_NAME |
---|---|
1 | French Open |
2 | Wimbledon |
3 | US Open |
4 | Aus Open |
5 | Olympics Games |
Now lets write RIGHT JOIN query:
SELECT D.DEPT_ID, D.DEPT_NAME, E.EMP_NAME, E.SALARY
FROM EMPLOYEE E, DEPARTMENT D
RIGHT JOIN DEPARTMENTS
ON E.DEPT_ID =D.DEPT_ID;
The Output will be like this:
DEPT_ID | DEPT_NAME | EMP_NAME | SALARY |
---|---|---|---|
1 | French Open | Roger Fed | 5000 |
1 | French Open | Djoker Nova | 6500 |
3 | US Open | Mart Hingis | 7200 |
4 | Aus Open | Roger Fed | 5000 |
4 | Aus Open | Roger Fed | 5000 |
4 | Aus Open | Roger Fed | 5000 |
4 | Aus Open | Roger Fed | 5000 |
4 | Aus Open | Roger Fed | 5000 |
5 | Olympics Games | Stef Graph | 8000 |
Learn more : SQL Tutorial , SQL Interview Questions